MySQL时间类型和模式

当我在 MySQL 数据库中尝试插入一条带有时间戳的数据时报错：

mysql> insert into alarm_service values (6, '1970-01-01 08:00:00'); 
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

# 查看表结构
mysql> show create table alarm_service;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                         |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| alarm_service | CREATE TABLE `alarm_service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以发现错误信息提示是时间值错误，但是我们这明显是一个合法的时间点啊。

经过查询资料，发现原因是在 MySQL 中， timestamp 类型的合法区间是 1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC ，而在存储是，会先将你插入的数据转换为 UTC 时间，然后存储起来，读取的时候，再转换为你的本地时间。由于我的时区为东八区，因此转换后就变为了 1970-01-01 00:00:00 UTC ，成为了非法时间。

解决方案为：

MySQL

下面我们详细说明相关的内容。

MySQL 时间类型

MySQL 时间类型分为三种：

• DATE： 用于只包含日期不包含时间的时候， MySQL 会将格式转换为 YYYY-MM-DD ，合法范围为 1000-01-01 - 9999-12-31 。
• DATETIME： 用于包含日期 + 时间的时候，格式为 YYYY-MM-DD HH:MM:SS ，合法范围为 1000-01-01 00:00:00 - 9999-12-31 23:59:59 。
• TIMESTAMP： 用于包含日期 + 时间的时候，格式为 YYYY-MM-DD HH:MM:SS ，合法范围为 1997-01-01 00:00:01 - 2038-01-19 03:14:07 UTC 。

同时， DATETIME 和 TIMESTAMP 还都支持一个 6 位微秒的数据支持，格式为 YYYY-MM-DD HH:MM:SS[.fraction] ，合法范围为 .000000 - .999999 。

DATETIME 和 TIMESTAMP 还都提供自动初始化并更新为当前日期和时间的数据。

对于 TIMESTAMP 类型， MySQL 会在存储时将数据值转换为 UTC 标准时间来存储，读取时再转为当前时间。如果你的时区没有发生改变，则该值就是你存储的值，如果你改变了时区，读取到的值就会发生变化。这个特性不会对 DATETIME 生效。

查看时区

mysql> show variables like '%zone%';                                       
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

可以看到当前设置的时区是 SYSTEM ，即跟操作系统保持一致，同时系统的时区是 CST(China Standard Time 北京标准时间) ，查看系统时间也可以看到是 东8区(+0800) ：

$ date -R
Tue, 23 Apr 2019 11:22:47 +0800

因此我们输入 1970-01-01 08:00:00 时 MySQL 会纠正为 1970-01-01 00:00:00 ，而成为一个非法值。

非法时间值

对于非法的时间值，针对不同的时间类型， MySQL 会将其转为合适的值： 0000-00-00 或 0000-00-00 00:00:00 。

比如月份为 1-12 月，当你尝试插入 2019-13-01 00:00:00 时，就会被纠正为 0000-00-00 00:00:00 ，因为不存在 13 月，为非法值。

严格模式

当我们插入非法时间值时，虽然会被纠正，但是在严格模式下，不会插入数据，反而会报错：

ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

我们可以通过设置模式，来调整 MySQL 的行为，首先查看 MySQL 的模式：

mysql> show variables like '%sql_mode%';            
+----------------------------+--------------------------------------------+
| Variable_name              | Value                                      |
+----------------------------+--------------------------------------------+                               |
| sql_mode                   | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+----------------------------+--------------------------------------------+

在这个模式下，非法时间会直接报错，我们可以调整模式为 ALLOW_INVALID_DATES ：

mysql> set session sql_mode = 'ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sql_mode%';            
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | ALLOW_INVALID_DATES |
+---------------+---------------------+
1 row in set (0.00 sec)

在这个模式下，不会再完备检查日期的合法性，只会检查月份的范围在 1-12 ，日期在 1-31 。这在处理用户输入的时候很合适，但是这个模式只对于 DATE 和 DATETIME 很合适，对于 TIMESTAMP ，依然需要一个合法的值，否则就会纠正为 0000-00-00 00:00:00 。

在非法值时，如果这个模式启用，就会报错；如果禁用，就会纠正为 0000-00-00 00:00:00 并产生一个警告：

mysql> insert into alarm_service values (7, '1970-01-01 08:00:00'); 
Query OK, 1 row affected, 1 warning (0.00 sec)

总结

对于这种问题，有两种解决方法：

MySQL

参考资料

1. 11.3.1 The DATE, DATETIME, and TIMESTAMP Types： https://dev.mysql.com/doc/ref...
2. 5.1.13 MySQL Server Time Zone Support: https://dev.mysql.com/doc/ref...
3. 5.1.11 Server SQL Modes: https://dev.mysql.com/doc/ref...