MySQL运维实战(4.5) SQL_MODE之NO_ZERO_DATE和NO_ZERO_IN_DATE
NO_ZERO_DATE：日期中不允许'0000-00-00'
NO_ZERO_IN_DATE：日期中年、月或日不允许为0，如不允许'2021-00-01', '2021-01-00'
date, datetime, timestamp都受这2个sql mode的影响
这2个sql mode需要跟strict模式一起使用。如果不开启strict模式，异常数据还是能写入到数据库。
NO_ZERO_DATE
只设置NO_ZERO_DATE, 异常数据会产生Warning，但是数据能写入
mysql> create table t_date( a date);
Query OK, 0 rows affected (0.01 sec)
-- 只设置NO_ZERO_DATE, 异常数据会产生Warning，但是数据能写入
mysql> set sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t_date values(0);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from t_date;
+------------+
| a |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
mysql> insert into t_date values('2021-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_date;
+------------+
| a |
+------------+
| 0000-00-00 |
| 2021-00-00 |
+------------+
2 rows in set (0.00 sec)NO_ZERO_IN_DATE
-- 设置NO_ZERO_IN_DATE, 日期、月份为0的数据会触发warning
mysql> set sql_mode='NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into t_date values('2022-01-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t_date;
+------------+
| a |
+------------+
| 0000-00-00 |
| 2021-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)STRICT模式
同时设置STRICT和NO_ZERO_DATE,NO_ZERO_IN_DATE，才能避免写入异常日期数据：
-- 增加STRICT模式，异常数据无法写入
mysql> set sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t_date values(0);
ERROR 1292 (22007): Incorrect date value: '0' for column 'a' at row 1
mysql> insert into t_date values('2023-00-00');
ERROR 1292 (22007): Incorrect date value: '2023-00-00' for column 'a' at row 1
mysql> insert into t_date values('2023-01-02');
Query OK, 1 row affected (0.00 sec)如果只设置STRICT模式，不设置NO_ZERO_IN_DATE,NO_ZERO_DATE，还是能写入为0的日期：
mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t_date values('');
ERROR 1292 (22007): Incorrect date value: '' for column 'a' at row 1
mysql> select * from t_date;
Empty set (0.00 sec)
mysql> insert into t_date values(0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_date;
+------------+
| a |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)总结
建议同时设置NO_ZERO_IN_DATE,NO_ZERO_DATE和STRICT_TRANS_TABLES，可以避免写入异常的日期数据。
