在新建一个表的时候,初始化一个 timestamp 字段值为 ‘0000-00-00 00:00:00’ 时,mysql报错:ERROR 1067 (42000): Invalid default value for 'last_updated_on'
。这是因为在Mysql中设置了sql_mode模式的 NO_ZERO_IN_DATE 和 NO_ZERO_DATE,这2种模式的意思是在严格模式下,不允许日期和月份为零,如果mysql数据库中插入零日期会抛出错误而不是警告。最简单的办法就是去掉sql_mode模式中的这两项即可。
1、报错示例
在MYSQL中新建一个表的时候,初始化一个 timestamp 字段值为 ‘0000-00-00 00:00:00’ 或往表中插入该类型的值时,报错:ERROR 1067 (42000): Invalid default value for 'last_updated_on'
。具体示例如下:
CREATE TABLE `test` (
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_updated_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
执行上述语句报错:
ERROR 1067 (42000): Invalid default value for 'last_updated_on'
2、错误原因
从上述错误语句可以看到,last_updated_on有一个无效的默认值。last_updated_on字段类型为TIMESTAMP。对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。
- timestamp所能存储的时间范围为:‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
我们再查询下mysql的sql_mode模式:
select version(), @@sql_mode;
返回结果:
8.0.29 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
从查询结果可以看到,mysql是开启了 NO_ZERO_IN_DATE,NO_ZERO_DATE
模式的。
3、解决办法
3.1 解决方案一:禁用NO_ZERO_IN_DATE,NO_ZERO_DATE功能
禁用NO_ZERO_IN_DATE,NO_ZERO_DATE功能,即把 sql_mode中的 NO_ZERO_IN_DATE,NO_ZERO_DATE 去掉即可。
- 办法一:只在当前查询页面有效,不能一次性解决问题!
执行下述语句:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE',''));
再次查询sql_mode后可以发现sql_mode中已经没有了NO_ZERO_IN_DATE,NO_ZERO_DATE:
select version(), @@sql_mode;
- 办法二:修改 my.cnf 文件的 sql_mode (一次性解决问题)
- 查找 my.cnf 文件:
find / -name my.cnf
或者whereis my.cnf
- 获取 sql_mode 字段内容:
SELECT @@sql_mode;
- 去掉 sql_mode 字段中的 NO_ZERO_IN_DATE,NO_ZERO_DATE
- 编辑 my.cnf:
vim /etc/my.cnf
将上一步骤的 sql_mode 字段内容添加至 my.cnf的底部。 - 保存文件,重启mysql服务:
systemctl restart mysqld.service
或service mysqld restart
。
my.cnf添加内容可参考:
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
修改位置:linux系统/etc/my.cnf文件,windows 系统 my.ini
查看修改的结果:
show variables like 'sql_mode';
可以看到已经去掉了 NO_ZERO_IN_DATE,NO_ZERO_DATE
配置。这个时候再执行建表命令,就正常了。
3.2 解决方案二:修改默认值
因为timestamp类型取值范围:1970-01-01 00:00:00 到 2037-12-31 23:59:59,所以必须得把字段 last_updated_on 的默认值修改成:1970-01-01 00:00:00 到 2037-12-31 23:59:59
之间的值,具体如下:
CREATE TABLE `test` (
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_updated_on` timestamp NOT NULL DEFAULT '1970-01-01 8:01:00',
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
执行上述语句,可以看到正常执行成功了。
3.3 拓展:timestamp的最小值问题
从MySQL的官方文档可以看到,timestamp的范围是从
1970-01-01 00:00:00 到 2037-12-31 23:59:59
之间的值。<p>The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of "1970-01-01 00:00:01" UTC to "2038-01-19 03:14:07" UTC.</p>
从官方文档中可以知道,timestamp的最小值为 1970-01-01 0:00:00
,可是当我们修改默认值为 1970-01-01 0:00:00 - 1970-01-01 8:01:00
之间的任意数据时,提示是一个无效的默认值。这是怎么回事呢?
- 因为timestamp类型的起始时间是1970-01-01 00:00:01 UTC,UTC表示该时间和时区是有关系的。MySQL将timestamp类型的值保存的时候,会从当前时区转成UTC时间,而从当前时区转成UTC时间需要减去『8小时』,结果就不在timestamp类型的范围内了。所以我们设置默认值的最小值为
1970-01-01 8:01:00
。
4、sql_mode可选值说明
ONLY_FULL_GROUP_BY:
- 对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO:
- 该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
STRICT_TRANS_TABLES:
- 在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
NO_ZERO_IN_DATE:
- 在严格模式下,不允许日期和月份为零
NO_ZERO_DATE:
- 设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO:
- 在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER:
- 禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION:
- 如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
PIPES_AS_CONCAT:
- 将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
ANSI_QUOTES:
- 启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符
评论区