MySQL时间类型和模式详情
16lz
2021-12-09
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 |+------------------+--------+
$ date -RTue, 23 Apr 2019 11:22:47 +0800
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> show variables like '%sql_mode%'; +----------------------------+--------------------------------------------+| Variable_name | Value |+----------------------------+--------------------------------------------+ || sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |+----------------------------+--------------------------------------------+
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)
在非法值时，如果这个模式启用，就会报错；如果禁用，就会纠正为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 --versionmysql Ver 14.14 Distrib 5.1.30, for unknown-linux-gnu (x86_64) using EditLine wrapper
更多相关文章
- Android开发从零开始之java-泛型初步
- 链接器解析多重定义的全局变量
- Android(安卓)SDK 和 杂7杂8
- android Uri获取真实路径转换成File的方法
- Android(安卓)识别SIM卡类型
- Android(安卓)验证码输入框 连续性粘贴 使用第三方包
- android 判断联网类型
- Android电池信息(Battery information)
- 安卓4.X系统 增加蓝牙接收文件类型