mysql5.7 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' 换版引发的问题
- 问题背景
- sql_mode
- 上机演示
- 5.7
- 8.4
问题背景
在项目mysql版本由5.7 换版到8.4版本后,我们进行回归测试时,却发现一个积年代码报错了,是数据库插入报的错 xxx can not be null
,!!!∑(゚Д゚ノ)ノ
这都是多早的老代码了,怎么会报这个错!人直接傻了。查看代码,那个字段确实没有设置任何值,然后查看数据库表结构,发现那个字段定义有点奇怪,upTime TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间'
,默认值是 0000-00-00 00:00:00
,而不是常见的CURRENT_TIMESTAMP
。但似乎也啥关联,要命的是字段明明是非空,为什么在5.7版本会插入成功!??
sql_mode
sql_mode 是 MySQL 中的一个系统变量,用于控制 MySQL 服务器运行时的 SQL 模式,影响 MySQL 的行为和功能。SQL 模式定义了 MySQL 服务器应该执行 SQL 语句的方式,包括语法检查、数据验证、警告和错误处理等方面。
在 MySQL 中,sql_mode 可以通过多种方式设置,包括在配置文件中指定默认的 sql_mode,在会话级别或全局级别动态修改 sql_mode。以下是一些常见的 sql_mode 设置:
STRICT_TRANS_TABLES
:在这种模式下,MySQL 会对插入、更新和删除操作进行严格的数据类型检查,确保数据的完整性。如果数据类型不匹配或值超出范围,将会产生错误。NO_ZERO_DATE
:禁止插入 ‘0000-00-00’ 或 ‘0000-00-00 00:00:00’ 作为日期或日期时间值, 如果发现这样的值,会产生错误。ONLY_FULL_GROUP_BY
:在这种模式下,如果在 GROUP BY 子句中的列没有在 SELECT 列表中出现,将会产生错误,要求所有非聚合列都出现在 GROUP BY 子句中。ERROR_FOR_DIVISION_BY_ZERO
:在除法运算中如果除数为 0,会产生错误,而不是返回 NULL。ANSI_QUOTES
:启用 ANSI_QUOTES 模式后,双引号被视为引用标识符的引号,而不是字符串引号。NO_ZERO_IN_DATE
:设置该值,mysql数据库在严格模式下,不允许日期和月份为零
通过设置合适的 sql_mode,您可以控制 MySQL 的行为,使其符合您的需求和标准。根据项目的要求,可以选择不同的模式来确保数据的正确性和一致性。
那么,我们查看mysql5.7的官方文档,发现,mysql5.7 里面的 默认 sql_mode
模式,里面有NO_ZERO_DATE
、NO_ZERO_IN_DATE
。
所以,其实在5.7 版本默认配置中,这样的建表语句是非法的。那么我们找一个环境试一试!我用的是一个在线网站,小闪电
上机演示
5.7
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 5.7.42-0ubuntu0.18.04.1 |
+-------------------------+
1 row in set (0.00 sec)mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> create database te-> ;
Query OK, 1 row affected (0.00 sec)mysql> use te;
Database changedmysql> CREATE TABLE stu (-> id INT,-> upTime TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',-> st CHAR(2) NOT NULL DEFAULT '0' COMMENT '状态'-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学生表';
ERROR 1067 (42000): Unknown error 1067
果然出错了,于是我们修改一下,sql_mode ,在试一遍
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> CREATE TABLE stu (-> id INT,-> upTime TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',-> st CHAR(2) NOT NULL DEFAULT '0' COMMENT '状态'-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学生表';
Query OK, 0 rows affected (0.34 sec)mysql> insert into stu(id,upTime,st) values (1,null,null);
ERROR 1048 (23000): Unknown error 1048
mysql> insert into stu(id,upTime,st) values (1,null,'1');
Query OK, 1 row affected (0.06 sec)mysql> insert into stu(id,upTime,st) values (2,null,null);
ERROR 1048 (23000): Unknown error 1048
mysql> select * from stu;
+------+---------------------+----+
| id | upTime | st |
+------+---------------------+----+
| 1 | 2024-09-02 09:37:07 | 1 |
+------+---------------------+----+
1 row in set (0.00 sec)
我们可以看到插入成功了,而且,upTime 的值 竟然是当前时间!而且,当我同时令st
也为null 时,插入报错了!于我最初设想的结果有些出入,看来是只有 TIMESTAMP
这个类型,mysql5.7 做了特殊的处理,其他类型设置为非空时,即使有默认值也不能直接插入null。
8.4
然而,mysql8.4 的版本中,即使你重新修改了 sql_mode
的值,允许0日期。但是也不能直接插入null给一个表结构为非空的日期字段。感兴趣的同学可以试一试。这边没什么好贴图的,直接就报了xx can not be null