说在前面
在这一路学习过来,每次不管看书还是网上看的资料,对于MySQL数据类型中的时间日期类型总是一扫而过,不曾停下来认认真真的研究学习。最近看了一本关于MySql的书籍,打算全面的学习研究一遍。
虽然每次都没有怎么注意,但是使用起来确实非常的糟糕。每次都转换起来非常不便。比如明明我只需要日期,却给我一个时间戳或者时分秒都有的东西之类的。
在之前,我对于时间日期数据类型不怎么感冒,也没怎么用过这一类型。在我的做项目里用到存贮时间的数据,我都是采用int整型数据类型来存储,即是存储时间戳。但是在后面学习MySQL优化的时候,就有一个原则就是存储数据时应采用最小占用空间的数据类型。int类型是4个字节,TIMESTAMP也是4个字节,但是在需要使用日期时,时间戳还需要进一步转换,而TIMESTAMP类型数据就不需要了。
时间日期数据类型总概况
MySQL中有多种表示时间日期的数据类型,主要有YEAR、TIME、DATE、DATETIME、TIMESTAMP等。每一种数据类型都有存储的时间日期格式、以及取值范围,因此在使用时间日期数据类型的时候需要选取最佳的数据类型。
此处注意发现:Time类型HH的取值竟然是800+,而不是我们本以为的24以内的数字,具体原因,下面会有解释
各类型详细讲解
1、Year
见名之意,year用于存储年,存储时只需要一个字节,插入数据时可以使用各种格式指定YEAR值(非常节约内存,所以当你只需要年的时候,用此字段合适)。
支持的常见插入格式为:
1.(推荐甚至强制要求必须)四位字符串或者数字,范围为“1901”~“2155”,写多少即为多少
2.两位字符串格式:范围为“00”“99”,"00""69"被转化为20**(例如:“16”转化为“2016”),“70”~“99”被转化为19**(下同)
3.两位数字格式,范围为199,169被转化为20**(例如:1转化为2001),70~99被转化为19**
2、Time
time用于存储时间信息,存储时需要三个字节。
虽然,小时的范围是0~~23,但是为了表示某种特殊需要的时间间隔,将Time类型的范围扩大了。而且还支持了负值。
支持的常见插入格式为:
1.(推荐甚至强制要求必须)字符串格式:‘HH:MM:SS’,‘HH:MM’,‘D HH:MM’,‘D HH’,'SS’等形式。举个例子,输入‘30’,Time类型会自动转换为00:00:30。
2.‘HHMMSS’格式的字符串或HHMMSS格式的数值表示,例如,输入‘123456’,Time类型会转换成12:34:56;输入123456,Time类型会转换成12:34:56。如果输入0或者‘0’,那么TIME类型会转换为0000:00:00。
3.‘D HH:MM:SS’格式的字符串表示。其中,D表示天数,取值范围是0~~34。保存时,小时的值等于(D*24+HH)。举个例子,输入‘2 11:30:50’,Time类型会转换为59:30:50。
4.使用current_time或者current_time()或者now()输入当前系统时间。(一般用于默认值)
SQL示例:
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '1 01:50:50'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '01:50:50'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '50:05'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '1 05:05'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '59'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', '66'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', '123456'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 123456); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 0); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', '0'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('11', now()); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('12', current_time); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('13', current_time());
Time类型专门用来存储时间数据,而且只占3个字节,所以如果只需要记录时间,选择Time类型是最合适的。
3、Date
date用于存储日期,没有时间部分,存储时需要三个字节。
MySQL中是以YYYY-MM-DD的形式显示date类型的值。
支持的常见插入格式为:
(推荐甚至强制要求必须) ‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串表示,这种方式可以表达的范围是‘1000-01-01’~‘9999-12-31’。
MySQL中还支持一些不严格的语法格式,任何标点都可以用来做间隔符。如’YYYY/MM/DD‘,’YYYY@MM@DD‘,’YYYY.MM.DD‘等分隔形式。举个例子,输入’2011.3.8‘,date类型将转换为2011-03-08。
’YY-MM-DD‘或者’YYMMDD‘格式的字符串表示,其中’YY‘的取值,’00‘’69‘转换为20002069,’70‘’99‘转换为19701999。与year类型类似。
使用current_date或now()来输入当前系统时间。
SQL示例:
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '2008-08-08'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '20080808'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '2008@08@08'); -- 格式出错,所以插入0000-00-00 INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '2008#0808'); -- 格式没错,但是小于了date类型的最小值1000,但是数据库还是插进去了,我晕 INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '0999-08-08'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', '690808'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', '700808'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 690808); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 700808); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', '0'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('11', 0); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('12', now()); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('13', current_date); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('14', current_date());
4、Datetime
datetime类型使用8个字节来表示日期和时间。
MySQL中以‘YYYY-MM-DD HH:MM:SS’的形式来显示dateTime类型的值。
支持的常见插入格式为:
(推荐甚至强制要求必须)‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串表示。这种方式可以表达的范围是‘1000-01-01 00:00:00’~~‘9999-12-31 23:59:59’。
MySQL中还支持一些不严格的语法格式,任何的标点都可以用来做间隔符。情况与date类型相同,而且时间部分也可以使用任意的分隔符隔开,这与Time类型不同,Time类型只能用‘:’隔开呢。
使用now()来输入当前系统日期和时间。
SQL示例:
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '2008-08-08 08:08:08'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '20080808080808'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '2008@08@08 08*08*08'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '69-01-01 11:11:11'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '70-01-01 11:11:11'); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', 20080808080808); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', 690808080808); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 700808080808); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 0); INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', now());
dateTime类型用来记录日期和时间,其作用等价于date类型和Time类型的组合。一个dateTime类型的字段可以用一个date类型的字段和一个time类型的字段代替。但是如果需要同时记录日期和时间,选择dateTime类型是个不错的选择。