MySQL的变量sql_mode有NO_ZERO_DATE和NO_ZERO_IN_DATE这两个模式/参数值，那么它们的用途是啥呢？又有啥区别呢？下面我们来锊锊和总结一下这方面的知识点。
其实NO_ZERO_IN_DATE、NO_ZERO_DATE这两个变量影响MySQL对零值日期和一些非法日期的处理，简单来说如下：
NO_ZERO_IN_DATE：
在严格模式下，不允许插入日期或月份值为零的日期。但是不影响’0000-00-00’零值日期或者年份为零但是日期和月份值非零的日期值的插入(例如’0000-01-01′)。在非严格模式，可以接受这类日期，但会产生警告。
NO_ZERO_DATE：
在严格模式下，不允许插入’0000-00-00’这样的零日期，也就是说这种日期不是合法日期，不允许插入。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告
官方文档关于NO_ZERO_IN_DATE和NO_ZERO_DATE的详细介绍如下：
NO_ZERO_DATE
The NO_ZERO_DATE mode affects whether the server permits ‘0000-00-00’ as a valid date. Its effect also depends on whether strict SQL mode is enabled.
If this mode is not enabled, ‘0000-00-00’ is permitted and inserts produce no warning.
If this mode is enabled, ‘0000-00-00’ is permitted and inserts produce a warning.
If this mode and strict mode are enabled, ‘0000-00-00’ is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, ‘0000-00-00’ is permitted and inserts produce a warning.
NO_ZERO_DATE is deprecated. NO_ZERO_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa.
Because NO_ZERO_DATE is deprecated, you should expect it to be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.
NO_ZERO_IN_DATE
The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as ‘2010-00-01’ or ‘2010-01-00’, but not ‘0000-00-00’. To control whether the server permits ‘0000-00-00’, use the NO_ZERO_DATE mode.) The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.
If this mode is not enabled, dates with zero parts are permitted and inserts produce no warning.
If this mode is enabled, dates with zero parts are inserted as ‘0000-00-00’ and produce a warning.
If this mode and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as ‘0000-00-00’ and produce a warning.
NO_ZERO_IN_DATE is deprecated. NO_ZERO_IN_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa.
Because NO_ZERO_IN_DATE is deprecated, you should expect it to be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.
关于’0000-00-00’这样的零值日期， 它不是一个合法的日期值，但是由于设计问题以及历史遗留的问题，你还是可能会看到一些地方使用这种零值日期，下面我们通过一些例子来让你加深理解这方面的知识点。
测试环境为MySQL 8.0.28，当前环境的sql_mode设置值如下：
mysql> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
如上所示，sql_mode启用了STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE时，创建表时，如果日期字段默认值使用零值就会遇到错误：ERROR 1067 (42000): Invalid default value for ‘start_time’
mysql> CREATE TABLE `test` (
-> `id` bigint(20) unsigned NOT NULL COMMENT '文件夹ID',
-> `document_id` bigint(20) NULL DEFAULT 0 COMMENT '文件ID',
-> `name` varchar(255) NOT NULL DEFAULT '' COMMENT '文件名称',
-> `describe` varchar(255) NULL DEFAULT '' COMMENT '文件描述',
-> `create_by` varchar(32) NOT NULL DEFAULT '' COMMENT '用户ID',
-> `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建日期',
-> PRIMARY KEY (`id`) USING BTREE,
-> KEY `idx_cloud_document_attribute_document_id` (`document_id`) USING BTREE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
ERROR 1067 (42000): Invalid default value for 'create_time'
mysql>
解决这个问题，比较简单，要么调整sql_mode的参数值/模式，要么不要使用’0000-00-00’这样的零值日期，如下所示，我们使用一个特殊日期时间’1900-01-01 00:00:00’就可以避免这个问题
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL COMMENT '文件夹ID',
`document_id` bigint(20) NULL DEFAULT 0 COMMENT '文件ID',
`name` varchar(255) NOT NULL DEFAULT '' COMMENT '文件名称',
`describe` varchar(255) NULL DEFAULT '' COMMENT '文件描述',
`create_by` varchar(32) NOT NULL DEFAULT '' COMMENT '用户ID',
`create_time` datetime NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '创建日期',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_cloud_document_attribute_document_id` (`document_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
那么接下来，我们设计一个简单案例来看看sql_mode的变量对零值日期或一些非法日期的影响。由于这两个变量都受严格模式的影响，下面我将一一组合测试一次
DROP TABLE test;
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增列',
`c_date` date DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
严格模式下(STRICT_TRANS_TABLES)
如果设置了此模式/变量NO_ZERO_DATE, ‘0000-00-00’则会被认定为非法，并且插入也会产生错误。除非同时带有IGNORE，对于 INSERT IGNORE和UPDATE IGNORE，’0000-00-00’则允许插入但是会产生警告
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'c_date' at row 1
mysql> insert ignore into test(c_date) values('0000-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'c_date' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into test(c_date) values('0000-01-01');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test(c_date) values('0000-01-00');
Query OK, 1 row affected (0.01 sec)
mysql>
如果没有设置此模式/变量NO_ZERO_DATE,那么可以插入’0000-00-00’这种零值日期，但是不会产生告警
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
Query OK, 1 row affected (0.01 sec)
mysql>
非严格模式下
如果设置了此模式/变量NO_ZERO_DATE, 允许插入’0000-00-00’但是会产生告警
mysql> set session sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
如果没有设置了此模式/变量NO_ZERO_DATE, 允许插入’0000-00-00’而且不会产生告警
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
Query OK, 1 row affected (0.01 sec)
mysql>
然后我们再来看看模式/变量NO_ZERO_IN_DATE对日期格式的影响
严格模式下(STRICT_TRANS_TABLES)
如果启用了此模式NO_ZERO_IN_DATE, 不允许插入年份部分非零，但是月份或日期部分为0的日期，但是可以插入’0000-00-00’零值日期。
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into test(c_date) values('2022-00-01');
ERROR 1292 (22007): Incorrect date value: '2022-00-01' for column 'c_date' at row 1
mysql> insert into test(c_date) values('2022-01-00');
ERROR 1292 (22007): Incorrect date value: '2022-01-00' for column 'c_date' at row 1
mysql>
mysql> insert into test(c_date) values('0000-01-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
Query OK, 1 row affected (0.01 sec)
mysql>
如果未启用此模式NO_ZERO_IN_DATE,则允许部分为零的日期插入，并且不会产生任何警告。
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(c_date) values('2022-00-01');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test(c_date) values('2022-01-00');
Query OK, 1 row affected (0.00 sec)
mysql>
非严格模式下
如果启用了此模式NO_ZERO_IN_DATE，允许部分为零的日期插入，但是会有告警产生
mysql> set session sql_mode='NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(c_date) values('2022-00-01');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into test(c_date) values('2022-01-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
如果未启用此模式NO_ZERO_IN_DATE,则允许部分为零的日期插入，并且不会产生任何警告。
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(c_date) values('0000-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(c_date) values('2022-00-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(c_date) values('2022-01-00');
Query OK, 1 row affected (0.00 sec)
mysql>
总结
NO_ZERO_DATE模式影响’0000-00-00’日期的插入，NO_ZERO_IN_DATE模式影响除’0000-00-00’外的月份、日期值为零的日期的插入。另外无论何种模式，YEAR类型都允许0000插入，这两个变量影响的是DATE、DATETIME、TIMESTAMP三种字段类型中对日期部分为零的处理。默认情况下，sql_mode会同时启用STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE这三个变量。一般如果没有特殊需求，不建议修改sql_mode的值。
参考资料：
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
原文始发于微信公众号（DBA闲思杂想录）：MySQL的sql_mode的参数值NO_ZERO_IN_DATE和NO_ZERO_DATE总结
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/227391.html
