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

俊达3个月前技术文章157

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




相关文章

容器引擎Podman常用命令浅析

容器引擎Podman常用命令浅析

Podman简介Podman 是一个开源的无守护进程的容器引擎,用于在Linux系统上开发、管理和运行OCI容器。容器既可以以root身份运行,也可以在无root模式下运行。Podman 提供与 Do...

PG的执行计划

一、Explain基本使用1.1 命令解释explain [ ( option [,...] ) ] statement explain [ analyze ] [ verbose ] statem...

某客户k3s网络故障案例

某客户k3s网络故障案例

1、出现问题     在我们吃饭的过程中,小丫告诉我客户的系统出现问题了,我们赶快吃完饭回去帮忙排查。当我们回去的时候,被告知问题已经被修复了,但是问题根源没有找到。故障原因给出的是:  服务重启后对...

Zabbix监控接入

Zabbix监控1、环境实验机器:118.31.158.83(zabbix server)172.17.6.11(zabbix proxy)172.17.6.11(zabbix agent)2、安装z...

开源大数据集群部署(十)Ranger usersync部署

开源大数据集群部署(十)Ranger usersync部署

ranger usersync部署Ø 解压包[root@hd1.dtstack.com ranger]# pwd /opt/ranger [root@hd1.dtstack.com ranger]...

CDH实操--HDFS高可用设置

CDH实操--HDFS高可用设置

1 概述        在HDFS集群中NameNode存在单点故障(SPOF),对于只有一个NameNode的集群,如果NameNode机...

发表评论    

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