在新建一个表的时候，初始化一个 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模式中的这两项即可。

相关文章可参考：MySql在ONLY_FULL_GROUP_BY模式下分组查询报错问题的解决

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后，不能用双引号来引用字符串，因为它被解释为识别符

目录CONTENT

MySql ERROR 1067字段值报错问题