MySQL数据类型--日期时间

一、前言

自接触学习MySQL已有一段时间了，对于MySQL的基础知识还是有一定的了解的。在这一路学习过来，每次不管看书还是网上看的资料，对于MySQL数据类型中的时间日期类型总是一扫而过，不曾停下来认认真真的研究学习。最近在图书馆借了一本关于MysQL的书籍，打算全面的学习研究一遍。

在之前，我对于时间日期数据类型不怎么感冒，也没怎么用过这一类型。在我的做项目里用到存贮时间的数据，我都是采用int整型数据类型来存储，即是存储时间戳。但是在后面学习MySQL优化的时候，就有一个原则就是存储数据时应采用最小占用空间的数据类型。int类型是4个字节，TIMESTAMP也是4个字节，但是在需要使用日期时，时间戳还需要进一步转换，而TIMESTAMP类型数据就不需要了。

所以说认真学习了解每一个知识点是必要的！

二、时间日期数据类型总概况

 　　MySQL中有多种表示时间日期的数据类型，主要有YEAR、TIME、DATE、DATETIME、TIMESTAMP等。每一种数据类型都有存储的时间日期格式、以及取值范围，因此在使用时间日期数据类型的时候需要选取最佳的数据类型。

下图列出了几种数据类型：

三、细讲

1、YEAR

year用于存储年，存储时只需要一个字节，插入数据时可以使用各种格式指定YEAR值。

常见的插入格式解析：

a、四位字符串或者数字格式，范围为“1901”~“2155”，多少即为多少。

b、两位字符串格式，范围为“00”~“99”，"00"~"69"被转化为20**（例如：“16”转化为“2016”），“70”~“99”被转化为19**

c、两位数字格式，范围为1~99，1~69被转化为20**（例如：1转化为2001），70~99被转化为19**

2、TIME

time用于存储时间信息，存储时需要三个字节。从上图中可以了解到time的存储格式，还有数据范围。其中HH小时大于一天的24小时，可以为八百多的数字。这是因为这个类型不仅仅可以用于存储一天的某个时间还可以记录一段时间的间隔。所以小时就24小时。插入数据可以有多种格式。

常见插入格式解析：

a、"D HH:MM:SS"格式字符串格式。还可以使用非严格语法插入，例如“HH:MM:SS”、"D HH:SS"、“MM:SS”、"D HH"、”SS“（D表示天，取值范围为0~34）。在存入数据库是D将会被转换为D*24+HH，最终将当做小时进行入库存储。

b、”HHMMSS“没间隔的字符串格式或者HHMMSS没间隔的数字格式，非严格语法插入可以为，MMSS、SS、”MMSS“等等。要注意的是：1、这两种插入格式采用非严格语法的时候（即为时分秒不完整，只有时分等等），最右边的数据将默认为秒数，例如插入2318时，最终入库为002318。 2、分钟与秒数的数据要有实际意义，假若·MM为65超过59，那么将会发生错误。

3、DATE

date用于存储日期，没有时间部分，存储时需要三个字节。插入数据可以有多种格式。

常见插入格式解析：

a、”YYYY-MM-DD“、"YYYYMMDD"。

b、”YY-MM-DD“、”YYMMDD“，以两位数字来插入年份，这里主要区别”00~69”与“70~99”，前者表示20**，后者表示19**。

c、数字格式YY-MM-DD、YYMMDD，两位数字插入年份，区别于上面一样。

d、使用函数插入，CURRENT_DATE()、NOW()

4、DATETIME

datetime用于存储日期时间数据，存储时需要八个字节。插入数据可以有多种格式。

常见插入格式解析：

a、”YYYY-MM-DD HH:MM:SS“、"YYYYMMDDHHMMSS"，最终插入数据库都为2017-01-17 23:55:59格式。

b、”YY-MM-DD HH:MM:SS“、"YYMMDDHHMMSS"，两位数字插入年份，主要区分“00~69”与“70~99”，区别于上面一样。

c、数字格式插入，YYYYMMDDHHMMSS、YYMMDDHHMMSS，两位数字插入年份问题与上述一致。

5、TIMESTAMP

timestamp用于存储日期时间数据，与datetime存储数据格式一样，二者主要区别在于取值范围。timestamp存储需要四个字节，它的取值范围为“1970-01-01 00:00:01” UTC ~ “2038-01-19 03:14:07” UTC，而datetime取值范围为“1000-01-01 00:00:00” ~ "9999-12-31 23:59:59"（UTC：Coordinated Universal Time 世界时间标准） 。插入格式也有多种。

常见插入格式解析：

与DATETIME一致，可以参考上面。

四、小结

了解MySQL的日期时间数据类型对于选取一种适合存储类型是很有必要的。假若只有存储年份可以选取YEAR、仅存储时间可以选择TIME、又或者需要存储完整日期时间，那么可以根据实际情况选取DATATIME或者TIMESTAMP数据类型。