手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表Tag:database

Mysql时间函数

说实话,在我的应用当中,很少会用到这些MYSQL自带的时间函数,除非是在使用PHPMYADMIN的时候,于是,经常在有人问我,时间上加一天怎么办,减一天怎么办,当前月的最后一天怎么处理,上个月的今天到这个月的今天怎么搞,我都是临时翻手册来加以解释。毕竟大多数的情况下,我们都是会把这些处理交给PHP计算好再使用SQL查询,而真的很少利用自带函数。

在看到Taobao的DBA们把它们整理出来时,不禁暗暗 的YING笑了一番,以后方便了,不用找手册了。。。。。

原文:http://rdc.taobao.com/blog/dba/html/234_mysql_date_func.html

作者:丁原 

内容:

通常我们会有一些时间的转换需求,比如要统计某个时间段的收入,比如要截取某个时间的年份,比如要根据某个日期推算出是星期几等个,这些都可以通过Mysql自带的时间函数很容易实现。因为我对Mysql的函数还不熟,而通常又会调用这些时间函数,这边稍加总结以便查询。
–返回当前时间

mysql> select now(),date(now()),sysdate();
+---------------------+-------------+---------------------+
| now()               | date(now()) | sysdate()           |
+---------------------+-------------+---------------------+
| 2008-12-02 10:11:36 | 2008-12-02  | 2008-12-02 10:11:36 |
+---------------------+-------------+---------------------+
1 row in set (0.00 sec)
mysql> select curdate(),curdate()+0,curtime(),curtime()+0;       
+---------------------+-------------+---------------------+
| curdate()  | curdate()+0 | curtime() | curtime()+0   |
+---------------------+-------------+---------------------+
| 2008-12-02 | 20081202    | 10:00:33  | 100033.000000 |
+---------------------+-------------+---------------------+

–返回日期当月最后一天

mysql> select last_day('2008-12-02');
+------------------------+
| last_day('2008-12-02') |
+------------------------+
| 2008-12-31             |
+------------------------+
1 row in set (0.00 sec)

–返回日期的星期几

mysql> select dayname('2008-12-02'),dayofweek('2008-12-02');
+-----------------------+-------------------------+
| dayname('2008-12-02') | dayofweek('2008-12-02') |
+-----------------------+-------------------------+
| tuesday               |                       3 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

–返回日期的年,月,日

mysql> select month('2008-12-02'),year('2008-12-02'),day('2008-12-02');
+---------------------+--------------------+-------------------+
| month('2008-12-02') | year('2008-12-02') | day('2008-12-02') |
+---------------------+--------------------+-------------------+
|                  12 |               2008 |                 2 |
+---------------------+--------------------+-------------------+
1 row in set (0.00 sec)

–返回日期的小时,分,秒

mysql> select hour('10:05:03'),minute('10:05:03'),second('10:05:03');       
+------------------+--------------------+--------------------+
| hour('10:05:03') | minute('10:05:03') | second('10:05:03') |
+------------------+--------------------+--------------------+
|               10 |                  5 |                  3 |
+------------------+--------------------+--------------------+
1 row in set (0.00 sec)

1.subdate(d,t):起始时间加上一段时间(year,month,day…)

mysql> select date_add('1998-01-02', interval 31 day),adddate('1998-01-02', 31);     
+-----------------------------------------+---------------------------+
| date_add('1998-01-02', interval 31 day) | adddate('1998-01-02', 31) |
+-----------------------------------------+---------------------------+
| 1998-02-02                              | 1998-02-02                |
+-----------------------------------------+---------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1998-01-02',interval 2 year);
+-----------------------------------------------------+
| date_add('1998-01-02', interval 2 year)
+-----------------------------------------------------+
| 2000-01-02                                 
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1998-01-02', interval 2 hour);
+-----------------------------------------------------+
| date_add('1998-01-02', interval 2 hour)
+-----------------------------------------------------+
| 1998-01-02 02:00:00                   
+-----------------------------------------------------+
1 row in set (0.00 sec)

2.subdate(d,t):起始时间减去一段时间

mysql> select subdate('1998-01-02', interval 31 day),subdate('1998-01-02', 31);
+----------------------------------------+---------------------------+
| subdate('1998-01-02', interval 31 day) | subdate('1998-01-02', 31) |
+----------------------------------------+---------------------------+
| 1997-12-02                             | 1997-12-02                |
+----------------------------------------+---------------------------+
1 row in set (0.00 sec)

3.addtime(d,t):起始时间d加入时间t

mysql> select addtime('1997-12-31 23:59:50','00:00:05'), addtime('23:59:50','00:00:05') ;
+-------------------------------------------+--------------------------------+
| addtime('1997-12-31 23:59:50','00:00:05') | addtime('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:55                       | 23:59:55                       |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)

4.subtime(d,t):起始时间d减去时间t

mysql> select subtime('1997-12-31 23:59:50','00:00:05'), subtime('23:59:50','00:00:05');     
+-------------------------------------------+--------------------------------+
| subtime('1997-12-31 23:59:50','00:00:05') | subtime('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:45                       | 23:59:45                       |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)

5.datediff(d1,d2):返回起始时间d1和结束时间d2之间的天数

mysql> select datediff('1997-12-31 23:59:59','1997-12-30');
+----------------------------------------------+
| datediff('1997-12-31 23:59:59','1997-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

6.date_format(date,format):根据format字符串显示date值的格式

mysql> select date_format('2008-12-02 22:23:00', '%y %m %m %h:%i:%s');
+---------------------------------------------------------+
| date_format('2008-12-02 22:23:00', '%y %m %m %h:%i:%s') |
+---------------------------------------------------------+
| 2008 12 12 22:23:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

7.str_to_date(str,format) 字符串转化为时间

mysql> select str_to_date('04/31/2004', '%m/%d/%y %h:%i:s');
+-----------------------------------------------+
| str_to_date('04/31/2004', '%m/%d/%y %h:%i:s') |
+-----------------------------------------------+
| 2004-04-31 00:00:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

8.timestamp(expr) , timestamp(expr,expr2) :
对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2添加到日期或日期时间表达式 expr 中,将theresult作为日期时间值返回

mysql> select timestamp('2003-12-31'), timestamp('2003-12-31 12:00:00','12:00:00');
+-------------------------+---------------------------------------------+
| timestamp('2003-12-31') | timestamp('2003-12-31 12:00:00','12:00:00') |
+-------------------------+---------------------------------------------+
| 2003-12-31 00:00:00     | 2004-01-01 00:00:00                         |
+-------------------------+---------------------------------------------+
1 row in set (0.00 sec)

9.取当天0点0分,下一天0点0分

mysql> select timestamp(date(sysdate())),timestamp(adddate(date(sysdate()),1));     
+----------------------------+---------------------------------------+
| timestamp(date(sysdate())) | timestamp(adddate(date(sysdate()),1)) |
+----------------------------+---------------------------------------+
| 2008-12-02 00:00:00        | 2008-12-03 00:00:00                   |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)

--EOF--

Tags: mysql, database, 时间函数

PDF下载:《High Performance MySQL》

回忆未来的张宴推荐了这本书,并且在他的博客上提供了下载的链接,链接是新浪的爱问:点此下载《High Performance MySQL Second Edition》PDF电子版,并且博客上还有详细介绍:

  XML/HTML代码

  1. High Performance MySQL Second Edition  
  2. 作者: Baron Schwartz / Peter Zaitsev / Vadim Tkachenko / Jeremy Zawodny / Arjen Lentz / Derek Balling  
  3.   
  4. 副标题: Optimization, Backups, Replication, and More  
  5. ISBN: 9780596101718  
  6. 页数: 708  
  7. 定价: USD 49.99  
  8. 出版社: O'Reilly Media, Inc.  
  9. 装帧: Paperback  
  10. 出版年: 2008-06-18  
  11.   
  12. High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity.  
  13.   
  14. High Performance MySQL teaches you advanced techniques in depth so you can bring out MySQL's full power. Learn how to design schemas, indexes, queries and advanced MySQL features for maximum performance, and get detailed guidance for tuning your MySQL server, operating system, and hardware to their fullest potential. You'll also learn practical, safe, high-performance ways to scale your applications with replication, load balancing, high availability, and failover.  
  15.   
  16. This second edition is completely revised and greatly expanded, with deeper coverage in all areas. Major additions include:  
  17. * Emphasis throughout on both performance and reliability  
  18. * Thorough coverage of storage engines, including in-depth tuning and optimizations for the InnoDB storage engine  
  19. * Effects of new features in MySQL 5.0 and 5.1, including stored procedures, partitioned databases, triggers, and views  
  20. * A detailed discussion on how to build very large, highly scalable systems with MySQL  
  21. * New options for backups and replication  
  22. * Optimization of advanced querying features, such as full-text searches  
  23. * Four new appendices  
  24. The book also includes chapters on benchmarking, profiling, backups, security, and tools and techniques to help you measure, monitor, and manage your MySQL installations.  

为此,张宴还提供了一张图片:

 

大小: 5.18 K
尺寸: 106 x 139
浏览: 1530 次
点击打开新窗口浏览全图

 

整篇博客我作了一下整理和排序,想看原文的,请到:http://blog.s135.com/read.php?381,因为在这个页面上张宴采用了划词翻译,如果有看不懂英文的话,选中一下就会自动翻译了。。我没有提供。也不想再插入一些代码。呵呵

为了方便大家,我也上传了这个文件,大家可以点击下载:high.performance.mysql_second.edition.zip


Tags: mysql, database, pdf, download

MySQL之表结构修改[转]

Author:丹臣 posted on Taobao.com

mysql数据库里,对一个已创建的表进行DDL操作,比如说添加一个字段。在做测试时,发现ddl操作的时间特别的长。oracle里,通常情况下只是 修改数据字典就可以了,操作时间非常的短,阻塞DML的时间也比较短。mysql数据库对表进行ddl操作跟oracle数据库有很大的不同,它先要把原 表拷贝一份到临时表,这期间不阻塞select,阻塞所有的更改操作(update,delete,insert),对临时表ddl操作完成,删除原表, 重命名临时表。
如果一张比较大的表进行ddl变更,比如说40G,那拷贝的时间让人无法忍受,并且阻塞所有的DML操作,让业务无法继续。

以下是测试过程:

mysql> desc t1;
+-------------------+------------------+------+-----+---------+-------+
| Field                  | Type               | Null   | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| id                      | int(11)            | YES  | MUL | NULL    |       |
| nick                   | varchar(32)   | YES  |         | NULL    |       |
| email                 | varchar(32)    | YES  |         | NULL    |       |
| gmt_create       | datetime         | YES  |         | NULL    |       |
| gmt_modified    | datetime        | YES  |         | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  2228017 |
+----------+
1 row in set (1.78 sec)

现在对它进行表结构变更,增加一列:

mysql> alter table t1 add(tel varchar(20));
Query OK, 2304923 rows affected (41.03 sec)
Records: 2304923  Duplicates: 0  Warnings: 0

在上述表结构变更过程中,启动另外一个会话,进行select查询操作和一个更新操作:

mysql> select count(*) from t1;
+---------------+
| count(*)      |
+---------------+
|  2304923     |
+---------------+
1 row in set (2.10 sec)

mysql> select * from t1 limit 10;
+------+-------+------------------------+----------------------------+----------------------------+
| id      | nick   | email                       | gmt_create                 | gmt_modified              |
+------+-------+------------------------+----------------------------+----------------------------+
|    0   | nick0 | nick0@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    1   | nick1 | nick1@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    2   | nick2 | nick2@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    3   | nick3 | nick3@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    4   | nick4 | nick4@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    5   | nick5 | nick5@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    6   | nick6 | nick6@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    7   | nick7 | nick7@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    8   | nick8 | nick8@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
|    9   | nick9 | nick9@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
+------+-------+------------------------+----------------------------+----------------------------+
10 rows in set (0.00 sec)

mysql> update t1 set nick='test_nick' where id=1;
Query OK, 4 rows affected (43.89 sec)          --这里是阻塞的时间
Rows matched: 4  Changed: 4  Warnings: 0

通过以上实验可以看出,对表进行ddl操作时,mysql并不阻塞select查询,但会严重阻塞dml操作。另外,如果你要对表进行ddl操作,由于有一个拷贝操作,你要计算好你的可用空间够不够?如果你的系统经常要进行表结构变更,那么你将不得不要考虑此问题!

--EOF--

膘叔:说实话,一般对于表的操作往往都是在夜深人静的时候,虽然select并不影响,但为避免在更新表结构的时候更新数据,这还是必须的。。。以防万一啊

 

Tags: mysql, database, 修改, 表结构, taobao dba

MySQL主从服务器的一些技巧

问题:主从服务器表类型的选择

一般的共识是主服务器使用innodb,从服务器使用myisam,以便各尽其能。

问题:主从服务器字段类型的选择

字段类型对于分页等操作有很大影响。主服务器一般是innodb,因为不涉及查询,所以可以使用varchar等来存储字符串来节省空间,从服务器一般是 myisam,因为涉及查询,所以必须在char和varchar之间仔细权衡,没有varchar, text, blob字段的表是静态表,反之是动态表,静态表的检索效率要比动态表好若干倍,一般来说,所有涉及大结果集的查询都应该尽可能保证在静态表上完成,这里 说一个例子:比如说常见的articles表有title(varchar), body(text)等字段,在做文章列表的时候,因为不是静态表,所以查询不会很快,下面开始重构解决方案:把原来的articles表拆分成 subjects表和contents表,title字段设置为一个足够的char类型放在subjects表里,body字段还保持是text类型放到 contents表里,subjects和contents表之间的关系是一对多,这样,顺带着也方便的实现了多页文章的功能,而且更重要的是在查询文章 列表的时候,操作都是在subjects静态表里完成,效率肯定会比前一种方案提升很多。

问题:主从服务器NOW()函数造成数据不一致

假设在主服务器上执行一条INSERT .... VALUES ( ..., NOW()),那么在从服务器上也会同样执行一条的SQL语句,但是一来主从服务器各自的时间设置可能就不一致,二来主从服务器间的SQL同步也可能存在 时间上的的延迟,这样,NOW()在两台服务器上的结果就可能不一致。解决方法是显而易见的,就是不要使用NOW(),时间的计算在应用程序里完成。这里 介绍一个额外的小技巧:在PHP里如果想获得当前时间的时间戳,不要用time(),而应该使用$_SERVER[‘REQUEST_TIME’] (PHP版本大于5.1有效),这样少做了一次系统调用,更有效率。

问题:主从服务器读写分离时读操作失败

先重现一下问题:比如说添加一条新数据,添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是在主服务 器上完成的,浏览新数据的操作实在从服务器上完成的,不过由于主从服务器间SQL同步存在延迟,所以当使用last_insert_id在从服务器上查询 的时候,从服务器很可能还没有还没来得及同步到此记录,所以读操作失败。解决思路也不复杂,在代码里加入一个缓存层(可以使用memcached),新添 加的数据都顺手放到缓存层里一份,新数据的读操作也先查询缓存层,这样就不会再有读操作失败的问题了,当然删除或者更新数据的时候也要顺带着处理好缓存数 据,可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录,对于读取列表的记录的情况,则是无效的。

问题:主从服务器索引是否有必要保持一致

一般都是利用主从服务器完成读写分离,从服务器上进行读操作,主服务器进行写操作,这样的话,主服务器上仅保留主键,外键,唯一索引等必要的索引即可,以 便保持数据合法性,而对于那些原本用于优化SELECT操作的索引,可以全部删除,如此的话主服务器的写操作效率会提升很多。

作者:老王
原文:http://hi.baidu.com/thinkinginlamp/blog/item/5d72dd5469b1885fd0090633.html

Tags: database, mysql, 主从数据库, myisam, innodb

精通MYSQL数据库——连载十

本来觉得二进制字段没有什么好介绍的,本来嘛,二进制字段,不就是xxxtext变成了xxxblob?长度,大小都没有变化,只是存储的类型变成了二进制,如果把那些xxxtext字段的属性加一个binary,那就和xxxblob差不多了。
只是在没有加上binary属性的时候,xxxText字段的排序和比较是按字符串类型处理,而xxxBlob是按二进制处理的。加了BINARY属性自然是一样的。
关于在数据库里存在BLOB信息,历来就是一个有争议的话题。最早的VBB论坛就是把附件存在数据库里的,后来还有人单独做HACK把附件剥出来。很多人认为数据库就应该存储一些文本信息,对于二进制数据(图像、附件等)就只应该存储链接,而把文件单独存储出去。这样可以更加有效的利用数据库的空间。
但支持把附件存储在数据库的人却认为,二进制数据存储在数据库内,有利于数据的迁移、备份,提高了数据的集成程度,而且在程序里也能够使用统一的形式访问数据。把常用的数据和这些二进制数据存储在同一个表内,一般被DBA们所痛恨,因为这会导致所有的数据记录的存取速度变慢,而且BLOB数据在正常操作情况下只能作为一个整体来读出。也就是说如果一个BLOB数据长度是1024KB,如果你仅仅想读最后那24KB的内容,没有直接的办法,只能先读出来,然后再定位到最后,BLOB数据只能以一个整体来读写和传输。
从5.0.3开始,原来的BIT型字段正式变形,成为二进制字段了,它的最大宽度达到了64,为了这个BIT型字段,MYSQL还特地增加了一条用来写出二进制数据的新语法:b'0101',SELECT查询的时候,遇到BIT型,将返回二进制数据。(我没有5.0.3的版本,没有测试过怎么读取,书上介绍,可以先使用 seelct bit_field + 0 命令把二进制转为整数,再使用select bin(bit_field + 0 ) 将这些整数显示为二进制)
对于二进制来说,数据会有溢出现象,它分为上溢出和下溢出,但不管是如何的溢出,只要是溢出了,所有的二进制位都将被设置为1。例如将-1,0,1,7,8五个数字依次存入BIT(3)数据列的时候,实际存放的是b'111',b'000',b'001',b'111',b'111'。
除开这些二进制字段,剩下的就是具有MYSQL特色的字段了,如果没有什么必要,确实不太建议使用,因为其他的数据库都不支持这两种类型,一旦在数据迁移的时候,很有可能会造成丢失或增大工作量,是什么字段呢?他们就是ENUM和SET(枚举和集合)。
对于MYSQL来说,这两种字段对于涉及字符的操作,有很高的效率,表面上存储的可能是文本,但在实际处理的时候,是按设置的下标来进行操作,即按INT类型操作,所以效率极高。
ENUM是一个字符串集合,它的成员最多可以有65535个,ENUM字 取值只能是这个集合中的某一个成员(不允许是不同成员的一个组合 ),相当于数学意义上的“排列”。
而SET虽然采用了类似的思路,但允许数据表中的SET字段的取值是集合成员的任意组合(组合数量不得超过64个),相当于数学意义上的“组合”。在内部,这些字符串分别与2的幂(1,2,4,8等)相对应,所以字符串的组合就相当于二进制位的组合 。因为每个安符串分别对应一个二进制位,所以SET类型的空间战胜比ENUM大。
虽然这两种类型的效率相对较高,但由于和其他数据库的不兼容性,因此多创建一个关联数据表来关联这些数据才是更有实用价值。


介绍完MYSQL所支持的所有字段的属性,也该介绍一些在创建数据列的时候涉及到的属性,注意,有一些属性只能用于特定的数据类型

属性名    含义
NULL    数据列可以NULL值(一般是默认设置)
NOT NULL    不允许包含NULL值(因为bTree索引不支持NULL)
DEFAULT xxx    如果输入时没有指定值,则默认以xxx为值
DEFAULT CURRENT_TIMESTAMP 这个在介绍日期时间类型的时候介绍过,默认插入当前时间
ON UPDATE CURRENT_TIMESTAMP   在数据更新的时候,自动更新为当前时间
PRIMARY KEY   定义为主键
AUTO_INCREMENT    自动输入一个序列编号,只能用于整数类型的数据列,一般与主键对应使用,必须与NOT NULL,或UNIQUE 属性同时使用
UNSIGNED    无符号整数,值得注意的是,无论怎么计算,即使是1-2,返回的也是无符号的整数
CHARACTER SET name  仅适用于字符串列,指定一种字符集和一种可选排序方式

  
虽然MYSQL有DEFAULT XXX这种属性,但实际上,MYSQL并不允许使用函数来设定默认值,比如DEFAULT rand()就不被允许了。

介绍完这些,以后就该介绍数据库的设计技巧了。之所以在介绍设计技巧之前连载了这么长时间的数据类型,是因为在设计中,使用合适的类型可以适当的增加效率,具体的还是看设计吧。

Tags: database, mysql, 连载

Records:2312345