mysql 初始化 timestamp，提示 Invalid default value for ‘xxx‘

现象

在新建一个表的时候，初始化一个 timestamp 为 ‘0000-00-00 00:00:00’ 时报错

ERROR 1067 (42000): Invalid default value for 'last_updated_on'

, 这是因为mysql版本问题, mysql5.7版本中有了一个STRICT+mode（严格模式），而在此模式下默认是不允许设置日期的值为全0值的

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;
           

解决方案

去掉

sql_mode

中的 values:

NO_ZERO_IN_DATE,NO_ZERO_DATE

即可

临时修改sql_mode

set sql_mode=(select replace(@@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

select @@sql_mode; 
select @@global.sql_mode; 
           

永久修改sql_mode

直接修改mysql配置文件

/etc/my.cnf

在

[mysqld]

下面添加一行

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           

例如:

重启mysql服务使配置生效

systemctl restart mysqld
           

参考

1. https://www.cnblogs.com/tracy8881888/articles/9266696.html

2. https://blog.csdn.net/han_cui/article/details/82497329

3. https://jingyan.baidu.com/article/11c17a2c487745b546e39da2.html