MySQL运维实战(4.5) SQL_MODE之NO_ZERO_DATE和NO_ZERO_IN_DATE

俊达2年前技术文章1611

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,可以避免写入异常的日期数据。




相关文章

MySQL优化器特性(二)MRR优化

MySQL优化器特性(二)MRR优化

Index Range Scan索引范围扫描的一般步骤:1、根据where条件,从B+树定位到第一条记录。2、从索引页子节点中获取到行号(rowid),根据rowid回表查询数据。3、使用额外的whe...

MySQL运维实战(7.2) MySQL复制server_id相关问题

MySQL运维实战(7.2) MySQL复制server_id相关问题

主库server_id没有设置主库没有设置server_idGot fatal error 1236 from master when&nb...

MySQL运维实战之备份和恢复(8.4)xtrabackup恢复全量备份

恢复全量备份恢复全量备份大致可以分成以下几步:解压备份文件、prepare备份文件、将数据copy到目标实例相关目录、启动数据库实例。解压文件如果备份时使用了xbstream,需要先解压备份文件。我们...

MySQL运维实战之备份和恢复(8.1)xtrabackup全量备份

xtrabackup是percona开源的mysql物理备份工具。xtrabackup 8.0支持mysql 8.0版本的备份和恢复。xtrabackup 2.4支持mysql 5.7及以下版本的备份...

MySQL运维实战(4.3) SQL_MODE之ONLY_FULL_GROUP_BY

设置ONLY_FULL_GROUP_BY后,对有group by子句SQL,select的字段要么是group by中的字段,要么对字段进行聚合运算,否则sql执行报错。不设置ONLY_FULL_GR...

 MySQL运维实战之Clone插件(10.1)使用Clone插件

MySQL运维实战之Clone插件(10.1)使用Clone插件

clone插件介绍mysql 8.0.17版本引入了clone插件。使用clone插件可以对本地l或远程的mysql实例进行clone操作。clone插件会拷贝innodb存储引擎表,clone得到的...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。