当我在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
严格模式,允许非法时间
case
汇总
ERROR 1067 (42000): Invalid default value for 'createTime'
查看原因发现设置为:
# 查看创建表单的语句
CREATE TABLE `dimensionsConf` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createTime` datetime DEFAULT CURRENT_TIMESTAMP,
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8;
# 查看数据库版本
$mysql --version
mysql Ver 14.14 Distrib 5.1.30, for unknown-linux-gnu (x86_64) using EditLine wrapper
查阅官方文档发现原因:5.6.5
以下不支持datetime
类型,但可以使用timestamp
类型。
参考资料
- 11.3.1 The DATE, DATETIME, and TIMESTAMP Types:https://dev.mysql.com/doc/ref...
- 5.1.13 MySQL Server Time Zone Support: https://dev.mysql.com/doc/ref...
- 5.1.11 Server SQL Modes: https://dev.mysql.com/doc/ref...
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。