侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 285 篇文章
  • 累计创建 125 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

MySql ERROR 1067字段值报错问题

孔子说JAVA
2022-07-08 / 0 评论 / 0 点赞 / 68 阅读 / 3,829 字 / 正在检测是否收录...

在新建一个表的时候,初始化一个 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 去掉即可。

  1. 办法一:只在当前查询页面有效,不能一次性解决问题!

执行下述语句:

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;
  1. 办法二:修改 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.serviceservice 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后,不能用双引号来引用字符串,因为它被解释为识别符
0

评论区