下载掌阅APP，畅读海量书库
立即打开
MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异，MySQL 8.0版本支持的日期和时间类型主要有YEAR、DATE、TIME、DATETIME、TIMESTAMP。每一个类型都有合法的取值范围，如表5-3所示。MySQL8默认的SQL_MODE是严格模式，插入不合法日期时间值将会报错。如果SQL_MODE是宽松模式，插入不合法值时系统将用“零”值代替。关于SQL_MODE请关注本书的姊妹篇《剑指MySQL——架构、调优与运维》。
表5-3 MySQL中的日期时间类型说明
这些数据类型的主要区别如下。
· 如果仅仅是表示年份信息，可以只使用YEAR类型，这样更节省空间，格式为“YYYY”，例如“2022”。YEAR允许的值范围是1901—2155。YEAR还有格式为“YY”2位数字的形式，值是00～69，表示2000—2069年，值是70～99，表示1970—1999年，从MySQL 5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR（4），从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR（4）数据类型。
· 如果要表示年月日，可以使用DATE类型，格式为“YYYY-MM-DD”，例如“2022-02-04”。
· 如果要表示时分秒，可以使用TIME类型，格式为“HH：MM：SS”，例如“10:08:08”。
· 如果要表示年月日时分秒的完整日期时间，可以使用DATATIME类型，格式为“YYYY-MM-DD HH：MM：SS”，例如“2022-02-04 10:08:08”。
· 如果需要经常插入或更新日期时间为系统日期时间，则通常使用TIMESTAMP类型，格式为“YYYY-MM-DD HH：MM：SS”，例如“2022-02-04 10:08:08”。TIMESTAMP与DATETIME的区别在于TIMESTAMP的取值范围小，只支持1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC范围的日期时间值，其中UTC是世界标准时间，并且TIMESTAMP类型的日期时间值在存储时会将当前时区的日期时间值转换为时间标准时间值，检索时再转换回当前时区的日期时间值，这会更友好。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。另外，TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大。
例如，下面演示“YEAR”类型的插入和显示问题，SQL语句示例如下。
（1）创建临时表“t5_year”，字段“d1”的类型为“YEAR”。
（2）查看表结构。
（3）添加6条如下记录。
（4）查询添加结果。从结果中可以看出YEAR类型的字段值可以用字符串形式表示，即加单引号表示法，也可以使用数字形式表示，即直接写年份数字。区别在于0和‘0’，数字0是处理为“0000”，而‘0’则处理为“2000”。
（5）添加第7条记录失败。因为“3000”已经超过YEAR类型表示范围，所以提示“Out of range”错误。
例如，下面演示“DATE”“TIME”“DATETIME”“TIMESTAMP”类型，SQL语句示例如下。
（1）创建临时表“t5_date_time”，字段“d1”至“d4”的类型依次为“DATE”“TIME”“DATETIME”“TIMESTAMP”类型。
（2）查看表结构。
（3）添加第1条记录。
（4）查询添加结果。
（5）添加第2条记录。
（6）查看添加结果。从结果中可以看出，上面的添加记录操作是成功的，数据显示也是正确的。这里要说明的是，除了“TIME”类型，其他的日期时间类型，日期值的间隔符不仅限于“-”，时间值的间隔符不仅限于“：”，也可以是“%”“＃”等。
（7）添加第3条记录。
（8）查看添加结果。从结果中可以看成，上面的添加记录操作是成功的，数据显示也是正确的。这里要说明的是，在表示日期时间值时，也可以省略分隔符。但是省略时，最好年份使用4位数字，月份、日期等使用2位数字，不够两位的用0补充，例如，“2020-8-8”表示成“20200808”，否则容易导致无法正确解析日期时间值。
（9）设置当前时区为“+9:00”。
（10）查看“t5_date_time”表数据。从结果中可以发现“TIMESTAMP”列存储时会对当前时区进行转换，检索时转换回当前时区。
