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

俊达2年前技术文章1799

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运维实战(4.7) SQL_MODE之ANSI_QUOTES

默认情况下,mysql使用反引号(`)作为标识符的引号。使用mysql关键字作为表名、字段名会报语法错误,这时可以加上反引号( `),避免报错。设置ANSI_QUOTES后,使用双引号(")...

MySQL优化器特性(一)IN和Exists(semijoin)子查询优化策略

这篇文章中的SQL和执行计划在mysql 8.0.31环境下进行测试。测试的表结构和数据:表结构mysql> show create table tp\G...

MySQL运维实战之Clone插件(10.2)Clone插件原理

MySQL运维实战之Clone插件(10.2)Clone插件原理

clone插件实现clone操作主要分为几个阶段:1、初始阶段。初始阶段,会开启页面跟踪(Page Tracking)。开启页面跟踪后,修改过的页面的编号会被记录下来。页面的修改可分为两个阶段:首先在...

MySQL运维实战(5.1) 字符和编码的基本概念

MySQL运维实战(5.1) 字符和编码的基本概念

字符和编码字符字符是符号,是人们用于交流的各类符号,如26个英文字母、汉字、标点符号、数学运算符、其他语言的字母和符号。编码编码是计算机中以二进制方式存储字符的方式。字符集字符集是字符和编码的映射表。...

MySQL运维实战(4.9) SQL_MODE之NO_UNSIGNED_SUBTRACTION

在mysql数据库中,unsigned表示不存负数,如果unsigned类型的字段作运算,得到的结果为负数,SQL会报错。mysql> create table t...

MySQL运维实战(2.2)忘记密码如何处理

如果忘记了一个普通用户的密码,可以使用管理员账号登录,修改其他用户的密码。但是如果所有管理员账号的密码都忘记了,应该怎么处理呢?如果忘记root密码,可以使用skip-grant-tables参数启动...

发表评论    

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