目录
- 1、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严格模式，允许非法时间
下面我们详细说明相关的内容。
1、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生效。
2、查看时区
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，而成为一个非法值。
3、非法时间值
对于非法的时间值，针对不同的时间类型，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月，为非法值。
4、严格模式
当我们插入非法时间值时，虽然会被纠正，但是在严格模式下，不会插入数据，反而会报错：
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严格模式，允许非法时间
5、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
到此这篇关于mysql时间类型和模式详情的文章就介绍到这了,更多相关mysql时间类型和模式内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！