作者:俊达
引言
NO_ZERO_DATE 和 NO_ZERO_IN_DATE这两个SQL MODE,主要用于确保日期字段不包含非法的零值,以提高数据的完整性和准确性。
1、**NO_ZERO_DATE 模式:**日期中不允许使用 ‘0000-00-00’ 这样的零值。如果启用了该模式,MySQL将拒绝插入或更新包含这种零值的日期数据。
2、**NO_ZERO_IN_DATE 模式:**日期中年、月或日不允许为0,如不允许’2021-00-01’, ‘2021-01-00’,启用此模式后,MySQL会阻止插入或更新包含这种零值的日期数据。
date, datetime, timestamp都会受这两个SQL MODE的影响,而且它们需要跟 STRICT MODE 一起使用。启用 STRICT MODE 将在遇到数据不符合规范的情况下引发错误,防止非法数据被写入数据库。如果不开启 STRICT MODE,MySQL 在遇到异常数据时可能会尝试进行隐式转换或使用默认值,从而允许非法数据进入数据库。
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, 异常数据会产生Warning,但是数据能写入
-- 设置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,可以确保更加严格的日期数据规范,防止不合法或者异常的日期数据写入数据库,这对于确保数据库中数据的一致性和准确性非常重要。文章来源:https://www.toymoban.com/news/detail-804546.html
更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw文章来源地址https://www.toymoban.com/news/detail-804546.html
到了这里,关于MySQL运维实战(4.5) SQL_MODE之NO_ZERO_DATE和NO_ZERO_IN_DATE的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!