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 即可

show variables like 'sql_mode';

临时修改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