您当前的位置：首页 > 电脑百科 > 数据库 > MYSQL

MySQL中的sql_mode的解读

时间：2021-06-08 09:06:46 来源：今日头条作者：程序猿集锦

+ 加入收藏

前言
sql_mode参数
如何查看sql_mode的值
如何设置sql_mode的值
sql_mode参数列表
- ONLY_FULL_GROUP_BY
- ANSI_QUOTES
- PIPES_AS_CONCAT
- NO_AUTO_CREATE_USER
- STRICT_TRANS_TABLES
  - 严格模式下的实验
  - 非严格模式下的实验
- STRICT_ALL_TABLES
- ERROR_FOR_DIVISION_BY_ZERO
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
- NO_ENGINE_SUBSTITUTION
- NO_AUTO_VALUE_ON_ZERO
总结

前言

sql_mode是MySQL中比较重要的一个全局参数，这个参数的值对MySQL整体的运行有着极为重要的意义。但是一般情况下，大家并不怎么的去修改它的值，都是使用默认值。但是当我们在数据库之间迁移的时候，很有可能遇到这个参数引起的坑。

下面我们把这个参数的具体功能和参数值逐一分析一下。

sql_mode参数

如何查看sql_mode的值

查看sql_mode的值很简单，使用如下show variables like 'sql_mode'命令即可查看MySQL数据库的sql_mode的值。

mysql> mysql> show variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.01 sec)

mysql>

如何设置sql_mode的值

sql_mode可以配置很多值，多个值之间使用逗号分隔即可。如下：

set session sql_mode="a,b,c,d";
set global sql_mode="a,b,c,d";

需要注意的是：sql_mode的值，可以分为session级别和global两个级别，session级别的修改，只针对当前登录的session有效，其他session登录后，还是使用原先的global级别的值。global级别的修改，除了针对当前的session有效之外，对以后新创建的任何其他session都是有效的。

sql_mode参数列表

参数sql_mode的值，可以设置为哪些值呢？下面我们针对每一个参数值看一下。

备注：下面所有的实验，对sql_mode的修改，都是基于session级别的修改。

ONLY_FULL_GROUP_BY

sql_mode配置上这个参数之后，表示SQL语句中涉及到的聚合函数的使用的时候，需要在group_by子句中包含所有的分组列，不能漏掉任何一个分组列。言外之意就是在所有的select字段中，除了聚合函数之外的所有字段，都应该出现在group by子句中。

示例演示。创建一个表T，结构和初始化数据如下所示，我们接下来的操作针对这个表来做实验。

mysql> desc t;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| a     | varchar(16) | YES  |     | NULL    |                |
| b     | varchar(16) | YES  |     | NULL    |                |
| c     | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from t;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | x1   | y1   |   10 |
|  2 | x1   | y1   |    5 |
|  3 | x1   | y3   |    2 |
|  4 | x2   | y2   |   20 |
|  5 | x2   | y3   |   20 |
|  6 | x2   | y3   |    6 |
+----+------+------+------+
6 rows in set (0.00 sec)

mysql>

我们把先当前的sql_mode的值设置为空，然后再把sql_mode的值设置为ONLY_FULL_GROUP_BY。针对这两种设置方式，我们来测试同一个group by语句的执行效果是怎样的。

mysql> set session sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

接下来我我们执行下面的group_by语句，看下结果是什么样子的。

mysql> select * from t;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | x1   | y1   |   10 |
|  2 | x1   | y1   |    5 |
|  3 | x1   | y3   |    2 |
|  4 | x2   | y2   |   20 |
|  5 | x2   | y3   |   20 |
|  6 | x2   | y3   |    6 |
+----+------+------+------+
6 rows in set (0.00 sec)

mysql> select a, b, sum(c) from t group by a, b; # 可以执行成功
+------+------+--------+
| a    | b    | sum(c) |
+------+------+--------+
| x1   | y1   |     15 |
| x1   | y3   |      2 |
| x2   | y2   |     20 |
| x2   | y3   |     26 |
+------+------+--------+
4 rows in set (0.01 sec)

# 下面在缺少一个group_by字段的时候，也可以执行成功，只不过结果和上面有些区别。
mysql> select a, b, sum(c) from t group by a;
+------+------+--------+
| a    | b    | sum(c) |
+------+------+--------+
| x1   | y1   |     17 |
| x2   | y2   |     46 |
+------+------+--------+
2 rows in set (0.00 sec)

mysql>

下面我们把sql_mode的参数修改为ONLY_FULL_GROUP_BY，再分别执行一下上面的两个group by语句。

mysql> set session sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| sql_mode      | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

下面看执行group by语句的效果：

mysql> select * from t;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | x1   | y1   |   10 |
|  2 | x1   | y1   |    5 |
|  3 | x1   | y3   |    2 |
|  4 | x2   | y2   |   20 |
|  5 | x2   | y3   |   20 |
|  6 | x2   | y3   |    6 |
+----+------+------+------+
6 rows in set (0.00 sec)

mysql> select a, b, sum(c) from t group by a, b; # 可以执行成功
+------+------+--------+
| a    | b    | sum(c) |
+------+------+--------+
| x1   | y1   |     15 |
| x1   | y3   |      2 |
| x2   | y2   |     20 |
| x2   | y3   |     26 |
+------+------+--------+
4 rows in set (0.00 sec)

mysql> select a, b, sum(c) from t group by a; # 执行失败，提示sql_mode的值包含only_full_group_by。
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.t.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

通过以上两种参数的设置，相信你对sql_mode的参数ONLY_FULL_GROUP_BY的作用应该有一个深刻的体会了。建议将sql_mode的参数值设置为包含ONLY_FULL_GROUP_BY选项，因为这样更加的符合SQL规范标准，在以后做跨类型数据库迁移的时候，也方便把当前的SQL语句，直接迁移到其他类型的数据库中去，例如：Oracle、SQLserver等数据库。

ANSI_QUOTES

这个参数的含义是在SQL语句中，对于字符串需要使用单引号来包裹起来，而不是既支持单引号又支持双引号。下面我们来看实例。

首选，我们的sql_mode是不包含ANSI_QUOTES参数的，如下所示：

mysql> show session variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| sql_mode      | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

下面看下我们的两个查询字符串的SQL语句：

mysql> select * from t where a='x1' and b='y3'; # 字符串通过单引号包裹可以执行
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  3 | x1   | y3   |    2 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where a="x1" and b="y3"; # 字符串通过双引号包裹也可以执行
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  3 | x1   | y3   |    2 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql>

通过上面的两个SQL语句，我们发现：当sql_mode的参数中，不包含ANSI_QUOTES参数的时候，在SQL语句中，字符串既可以用单引号包裹也可以用双引号包裹。下面我们在看下在sql_mode参数中，配置上ANSI_QUOTES参数后，看下SQL语句的效果是什么样子的。

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| sql_mode      | ANSI_QUOTES,ONLY_FULL_GROUP_BY |
+---------------+--------------------------------+
1 row in set (0.00 sec)

mysql>

在sql_mode的参数包含了ANSI_QUOTES值之后，我们执行如下的SQL语句，观察执行的结果是怎么样的：

mysql> select * from t where a='x1' and b='y3'; # 字符串通过单引号包裹可以执行
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  3 | x1   | y3   |    2 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where a="x1" and b="y3";  # 字符串通过双引号包裹，执行失败
ERROR 1054 (42S22): Unknown column 'x1' in 'where clause'
mysql>

通过上面的SQL执行结果，我们可以发现：在sql_mode参数中，包含了ANSI_QUOTES值之后，在SQL语句中所有的字符串，都只能够通过单引号来包裹，不再支持双引号包裹。

建议sql_mode参数中包含ANSI_QUOTES的设置，这样使得我们的开发中写的SQL更加的符合SQL标准规范，便于以后数据库的迁移。

PIPES_AS_CONCAT

当sql_mode的值中包含PIPES_AS_CONCAT的时候，表示在SQL语句中，可以使用||来表示字符串的连接，这个字符不会被当做或运算符，否则MySQL中只能使用concat函数来连接字符串，||这个字符会被当做或运算符。

下面我们针对sql_mode中包含这个参数值和不包含这个参数值的两种情况分别做一下测试。在测试之前，我们先看一下当前的sql_mode的值是什么样的，如下：

mysql> show session variables like 'sql_mode';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| sql_mode      | ANSI_QUOTES,ONLY_FULL_GROUP_BY |
+---------------+--------------------------------+
1 row in set (0.04 sec)

mysql>

当前的sql_mode中，没有包含参数值PIPES_AS_CONCAT，下面我们看一下字符串连接的时候，我们使用||来连接，效果是什么样子的，如下：

mysql> select 'hello' || 'mysql';
+--------------------+
| 'hello' || 'mysql' |
+--------------------+
|                  0 |
+--------------------+
1 row in set, 2 warnings (0.01 sec)

mysql> select 'hello' || 123;
+----------------+
| 'hello' || 123 |
+----------------+
|              1 |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select concat('hello','mysql');
+-------------------------+
| concat('hello','mysql') |
+-------------------------+
| hellomysql              |
+-------------------------+
1 row in set (0.01 sec)

mysql>

通过上面的实验我们发现，当sql_mode中不包含PIPES_AS_CONCAT值的时候，字符串的连接不能使用双竖线，只能使用concat函数来连接。下面我们来看一下当sql_mode中包含PIPES_AS_CONCAT值的时候，效果是什么样子的。

在实验之前，我们先设置一下session级别的sql_mode的值，设置如下：

mysql> set session sql_mode='ANSI_QUOTES,ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+------------------------------------------------+
| Variable_name | Value                                          |
+---------------+------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY |
+---------------+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

实验结果如下：

mysql> select 'hello' || 'mysql';
+--------------------+
| 'hello' || 'mysql' |
+--------------------+
| hellomysql         |
+--------------------+
1 row in set (0.00 sec)

mysql> select 'hello' || 123;
+----------------+
| 'hello' || 123 |
+----------------+
| hello123       |
+----------------+
1 row in set (0.00 sec)

mysql> select concat('hello','mysql');
+-------------------------+
| concat('hello','mysql') |
+-------------------------+
| hellomysql              |
+-------------------------+
1 row in set (0.00 sec)

mysql>

通过上面的实验，我们可以发现，当sql_mode的参数值包含PIPES_AS_CONCAT值的时候，SQL语句中的字符串的连接，除了可以使用concat函数之外，还是可以使用双竖线||来连接。

在平时的设置中建议sql_mode的值包含PIPES_AS_CONCAT，因为这样更加符合SQL标准，便于在以后进行数据库的异构迁移。

NO_AUTO_CREATE_USER

这个参数值的作用是用来控制通过grant语句授权并创建用户的时候，是否可以创建密码为空的用户。对于create user语句创建用户是不受这个参数的控制的。下面我们来看一下具体的效果是什么样子的。

先查看当前的sql_mode的参数值是不包含NO_AUTO_CREATE_USER值的，如下所示：

mysql> show session variables like 'sql_mode';
+---------------+------------------------------------------------+
| Variable_name | Value                                          |
+---------------+------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY |
+---------------+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

下面查看grant语句的执行效果：

mysql> select user,host from mysql.user; # 当前没有user1和user2两个用户。
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> grant select on test.* to user1; # 创建user1的时候，不指定密码，创建成功。
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant select on test.* to user2 identified by ''; # 创建user2的时候，指定为空密码，创建也成功了。
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user; # 查询user1和user2两个用户创建成功后的结果。
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| user1         | %         |
| user2         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql>

通过上面的grant语句的实验结果，我们发现：当sql_mode中不包含NO_AUTO_CREATE_USER值的时候，使用grant语句创建并授权用户的时候，是可以设置新用户的密码为空或不指定它的密码的。下面我们在设置一下sql_mode参数的值，把NO_AUTO_CREATE_USER的值配置上，再来验证一下grant语句的效果。

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+--------------------------------------------------------------------+
| Variable_name | Value                                                              |
+---------------+--------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER |
+---------------+--------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

在sql_mode的参数值中包含NO_AUTO_CREATE_USER之后，在使用grant语句授权并且创建用户的时候，需要指定用户的密码不为空才可以创建成功，如下所示：

mysql> grant select on *.* to user3; # 创建user3失败，因为它的密码没有指定
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant select on *.* to user4 identified by ''; # 创建user4失败，因为它的密码指定的为空字符串
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant select on *.* to user5 identified by 'user5'; # 创建user5成功，因为为它指定了非空的密码。
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user; # 查看用户列表，可以发先user5创建成了，user3和user4并没有成功创建。
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| user1         | %         |
| user2         | %         |
| user5         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

mysql>

通过上面的语句实验，可以知道：在sql_mode包含NO_AUTO_CREATE_USER值之后，在通过grant语句创建用户的时候，如果不指定待创建的用户的密码或者指定为空字符串密码，创建是不会成功了。需要指定对应的非空密码才可以创建成功。

建议把sql_mode的值配置上NO_AUT0_CREATE_USER，这样可以避免创建用户的时候不设置用户密码，导致账号不安全，避免MySQL数据库遭受到恶意攻击。

STRICT_TRANS_TABLES

在sql_mode的参数列表中，如果包含STRICT_TRANS_TABLES值，表示当前的MySQL实例是运行在严格模式下。如果没有STRICT_TRANS_TABLES这个值，则表示MySQL实例是运行在非严格模式下。

下面我们区分一下严格模式和非严格模式的区别。

在严格模式下：当我们对支持事务的表(如InnoDB引擎的表)进行插入或修改操作的时候，如果插入或修改后的数据不符合表中字段定义的要求，则会把当前的操作中断，回滚整个事务，不会对表中的数据内容产生任何影响；当我们对不支持事务的表(如MyISAM引擎的表)进行插入或修改操作的时候，如果插入或修改后的数据不符合表中字段定义的要求，则会把当前的错误的数据转换近似的值或者隐式转换为对应的类型后放到表中，后续所有插入的操作将继续进行，整个修改将会对表中的数据内容产生影响。那么有没有办法避免这样的问题发现，让不支持事务的表的插入操作也报错，并终止后续的插入操作呢？答案是肯定的，那就是参数STRICT_ALL_TABLES参数的功能了，后面会介绍它的详细作用。但是这个参数的作用也仅限于终止非法数据行之后的插入操作，对于非法数据行前面的已经入库的数据，它也是无能为力的，因为这种表示不支持事务的，没有办法回滚已经入库的数据。
在非严格模式下：不管我们对支持事务的表还是不支持事务的表进行插入或者修改操作的时候，如果我们插入或修改后的数据，不符合表中的字段对数据的要求，那么所有的操作都会发生隐式的转换或者转换为一个近似的值，然后插入或者更新到表中。对表中的数据内容会有影响，不会发生任何的回滚操作。

下面我们就这对严格模式和非严格模式分别做一下实验。我们知道MySQL中常用的两种存储引擎：InnoDB、MyISAM，其中前者支持事务，后者不支持事务。我们就在两种模式下面分别针对这两种存储引擎的表进行实验。

严格模式下的实验

首先我们将sql_mode设置为严格模式，如下所示是包含STRICT_TRANS_TABLES值的，表示MySQL是运行在严格模式下。

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql>

我们再创建两个表，一个是InnoDB存储引擎的表tab_innodb，一个是MyISAM存储引擎表tab_myisam。如下所示：

mysql> show create table tab_innodbG
*************************** 1. row ***************************
       Table: tab_innodb
Create Table: CREATE TABLE "tab_innodb" (
  "id" int(11) NOT NULL,
  "a" int(11) NOT NULL,
  "b" int(11) DEFAULT NULL,
  "c" varchar(16) NOT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table tab_myisamG
*************************** 1. row ***************************
       Table: tab_myisam
Create Table: CREATE TABLE "tab_myisam" (
  "id" int(11) NOT NULL,
  "a" int(11) NOT NULL,
  "b" int(11) DEFAULT NULL,
  "c" varchar(16) NOT NULL,
  PRIMARY KEY ("id")
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql>

下面我们分别向两个存储引擎的表中批量的插入四条数据，其中第3条数据是不符合数据要求的，其他1、2、4这三条数据是复合要求的。我们看下在这两个表中插入后的效果是什么样子的。

mysql> insert into tab_innodb(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd'); # 插入失败，因为第三行数据不符合数据要求。
ERROR 1048 (23000): Column 'a' cannot be null
mysql> select * from tab_innodb; # 查询表中的数据发现任何数据都没有插入成功，事务回滚了。
Empty set (0.00 sec)

mysql> insert into tab_myisam(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd'); # 插入成功，但是有一条Warning信息。
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> show warnings; # 查看Warning信息是什么
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from tab_myisam; # 查看表中的数据，发现四条数据全部插入成功，只不过第三条数据的a列的值我们设置的为null，插入表中后，被转换成了0。
+----+---+------+------+
| id | a | b    | c    |
+----+---+------+------+
|  1 | 1 |    1 | aaaa |
|  2 | 2 |    2 | bbbb |
|  3 | 0 |    3 | cccc |
|  4 | 4 |    4 | dddd |
+----+---+------+------+
4 rows in set (0.00 sec)

mysql>

通过以上实验，我们可以看出，sql_mode在严格模式下，对数据的验证结果是复合我们前面总结的规律的。下面我们看一下非严格模式下面的实验。

非严格模式下的实验

先把sql_mode设置为非严格模式，如下所示：

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER
1 row in set (0.01 sec)

mysql>

下面我们向前面使用到的两个表进行同样的插入数据的测试，注意在测试之前，先清空两张表中的数据。

mysql> truncate table tab_myisam; # 实验之前，先清空表中的数据。
Query OK, 0 rows affected (0.01 sec)

mysql> truncate table tab_innodb; # 实验之前，先清空表中的数据。
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tab_innodb(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd');  # 向innodb存储引擎的表中批量插入数据，成功，但是有警告信息。
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> show warnings;  # 查看警告信息
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from tab_innodb; # 查看最后表中的数据，有4一条数据，但是第三条数据的a列的值，我们设置为null，但是被转换为0插入表中，这就造成了脏数据。我们设置为null，结果给我们转换为0存进去了。
+----+---+------+------+
| id | a | b    | c    |
+----+---+------+------+
|  1 | 1 |    1 | aaaa |
|  2 | 2 |    2 | bbbb |
|  3 | 0 |    3 | cccc |
|  4 | 4 |    4 | dddd |
+----+---+------+------+
4 rows in set (0.00 sec)

mysql> insert into tab_myisam(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd'); # 向MyISAM存储引擎的表中插入数据，成功，但是也有警告信息。
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> show warnings; # 查看警告信息
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from tab_myisam; # 查看表中的数据，全部成功，但是第三条数据的a列的值是0，并不是我们设置为null。
+----+---+------+------+
| id | a | b    | c    |
+----+---+------+------+
|  1 | 1 |    1 | aaaa |
|  2 | 2 |    2 | bbbb |
|  3 | 0 |    3 | cccc |
|  4 | 4 |    4 | dddd |
+----+---+------+------+
4 rows in set (0.00 sec)

mysql>

通过上面的实验，我们发现，sql_mode配置为非严格模式的时候，实验结果也是符合我们总结的规律的。

**总结：**建议将sql_mode配置为严格模式，因为在严格模式下可以避免脏数据的插入和更新，并且InnoDB存储引擎目前是大家经常使用的存储引擎，在这种支持事务的存储引擎下面，严格模式更能保证脏数据的产生。

STRICT_ALL_TABLES

对于InnoDB存储引擎，这个参数的作用和STRICT_TRANS_TABLES作用一致。
对于MyISAM存储引擎，当插入不是第一行报错时，会将报错，但是之前的数据保存到数据库中，同时终止之后的插入操作，如果不配置这个参数，不会报错，发生隐式转换插入库中，同时后面的数据也会继续插入到库中。下面我们来具体的实验步骤，看下具体的效果。

我们这个实验的时候只配置STRICT_ALL_TABLES，不配置前面的STRICT_TRANS_TABLES参数。单独观察这个参数的效果。下面为sql_mode的配置过程：

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql>

向两种存储类型的表中批量插入多条数据，看下效果是怎么样的。注意：在插入之前，先清空之前的测试数据。

mysql> truncate table tab_innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> truncate table tab_myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tab_innodb(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd'); # 支持事务的innodb存储引擎的表，插入失败，通过下面的查询结果可以看出，一条都没插入成功。
ERROR 1048 (23000): Column 'a' cannot be null
mysql> select * from tab_innodb;
Empty set (0.00 sec)

mysql> insert into tab_myisam(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd'); # 不支持事务的myisam存储引擎的表，插入失败，但是通过下面的查询结果可以看出，前2条成功了，第4条没有成功。
ERROR 1048 (23000): Column 'a' cannot be null
mysql> select * from tab_myisam;
+----+---+------+------+
| id | a | b    | c    |
+----+---+------+------+
|  1 | 1 |    1 | aaaa |
|  2 | 2 |    2 | bbbb |
+----+---+------+------+
2 rows in set (0.00 sec)

mysql>

这就是STRICT_ALL_TABLES参数的作用。在配置上这个参数之后，对于支持事务的表，插入数据的过程中，如果出现非法的数据，那么整个插入全部失败，会回滚前面插入成功的数据，后面的插入操作也不会执行了，这个效果和参数STRICT_TRANS_TABLES的效果是一样的；但是对于不支持事务的表，在插入的过程中，如果有非法数据出现，也会出现错误信息，但是前面已经插入的数据不会回滚，后续的插入也不会执行。但是对于参数STRICT_TRANS_TABLES参数而言，非法的数据行会做隐式转换插入的表中，并且后续的插入数据参照仍然会继续，最后所有的数据行都会插入到表中。

下面看下没有STRCIT_ALL_TABLES参数的效果。

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.01 sec)

mysql>

插入结果验证：

mysql> truncate table tab_innodb; # 插入前，先清空。
Query OK, 0 rows affected (0.01 sec)

mysql> truncate table tab_myisam; # 插入前，先清空。
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tab_innodb(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd'); # 插入成功，但是你有警告信息。
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> show warnings; # 查看警告信息
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from tab_innodb; # 查看支持事务的存储引擎表的数据，都插入了数据，只是不符合数据要求的第3行的a列发生的数据的null到0的隐式转换。
+----+---+------+------+
| id | a | b    | c    |
+----+---+------+------+
|  1 | 1 |    1 | aaaa |
|  2 | 2 |    2 | bbbb |
|  3 | 0 |    3 | cccc |
|  4 | 4 |    4 | dddd |
+----+---+------+------+
4 rows in set (0.00 sec)

mysql> insert into tab_myisam(id,a,b,c) values(1,1,1,'aaaa'),(2,2,2,'bbbb'),(3,null,3,'cccc'),(4,4,4,'dddd');  # 插入成功，但是你有警告信息。
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> show warnings; # 查看警告信息
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from tab_myisam;# 查看不支持事务的存储引擎表的数据，都插入了数据，只是不符合数据要求的第3行的a列发生的数据的null到0的隐式转换。
+----+---+------+------+
| id | a | b    | c    |
+----+---+------+------+
|  1 | 1 |    1 | aaaa |
|  2 | 2 |    2 | bbbb |
|  3 | 0 |    3 | cccc |
|  4 | 4 |    4 | dddd |
+----+---+------+------+
4 rows in set (0.00 sec)

mysql>

通过上面的实验，我们发现在没有配置STRICT_ALL_TABLES参数的时候，不管是否支持事务的表，在插入数据的时候，遇到非法的数据行后，会发生隐式转换并且插入都是成功，后续的所有插入操作都会继续进行，之前插入的数据行也会保留。这个效果和没有配置参数STRICT_TRANS_TABLES的效果是一样的。

建议：在sql_mode中配置上这个参数，这样就可以避免在对于不支持事务存储引擎的表，做批量插入或修改时，遇到非法数据后不报错误，只有警告信息，而且错误警告信息后面的SQL操作也会继续执行。

如果配置了STRICT_ALL_TABLES就不用配置STRICT_TRANS_TABLES参数了，因为前者的功能包含了后者的功能，前者的功能在后者的基础上，对不支持事务的表的操作上，做了的批量插入或修改中断的操作，中断错误行后面的SQL操作。

ERROR_FOR_DIVISION_BY_ZERO

如果sql_mode的参数中不包含这个值，那么在我们写的SQL语句中，如果使用数字0作为分母除数，select语句正常执行返回一个null值，执行insert、update语句的时候，也可以正常执行，只不过结果并不是我们想要的结果，结果也是null值。

如果在sql_mode中设置了

ERROR_FOR_DIVISION_BY_ZERO，当我们在select语句中使用了0作为分母的时候，那么会抛出一个Warning警告信息。在执行insert、updateSQL语句的时候，使用0作为除数的时候，则会出现一个Error信息。

注意：sql_mode参数的值

ERROR_FOR_DIVISION_BY_ZERO生效的前提是在SQL语句的严格模式下才会生效，也就是在sql_mode参数中，配置了STRICT_TRANS_TABLES参数的前提下，

ERROR_FOR_DIVISION_BY_ZERO的功能才会生效。

下面我们来看一下具体的效果，先看下一现在的sql_mode的值，是不包含

ERROR_FOR_DIVISION_BY_ZERO，如下所示：

mysql> show session variables like 'sql_mode';
+---------------+--------------------------------------------------------------------+
| Variable_name | Value                                                              |
+---------------+--------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER |
+---------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

使用0作为分母的时候，select语句执行结果如下，查询语句可以正常执行，并不会出现任何警告信息。insert、update语句执行也没有任何问题。

mysql> select 4/0;
+------+
| 4/0  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select 0/0;
+------+
| 0/0  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> desc t;
+-------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type        | Null | Key | Default           | Extra                       |
+-------+-------------+------+-----+-------------------+-----------------------------+
| id    | int(11)     | NO   | PRI | NULL              | auto_increment              |
| a     | varchar(16) | YES  |     | NULL              |                             |
| b     | varchar(16) | YES  |     | NULL              |                             |
| x     | int(11)     | YES  |     | NULL              |                             |
| y     | int(11)     | YES  |     | NULL              |                             |
| z     | int(11)     | YES  |     | NULL              |                             |
| c     | timestamp   | YES  |     | NULL              |                             |
| d     | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.01 sec)

mysql> select * from t;
+----+------+------+------+------+------+---------------------+---------------------+
| id | a    | b    | x    | y    | z    | c                   | d                   |
+----+------+------+------+------+------+---------------------+---------------------+
|  1 | x1   | y1   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  2 | x1   | y1   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  3 | x1   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  4 | x2   | y2   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  5 | x2   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  6 | x2   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
+----+------+------+------+------+------+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql> update t set z = x / y where id = 2; # update语句中，包含0作为除数的情况，已经可以执行成功，只是z列结果是null。
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into t(a,b,x,y,z) values ('x3','y3',1/0,1,1); # insert语句中，也有0作为除数的请，也可以执行成功。只是x列的值为null。
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+------+------+------+------+---------------------+---------------------+
| id | a    | b    | x    | y    | z    | c                   | d                   |
+----+------+------+------+------+------+---------------------+---------------------+
|  1 | x1   | y1   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  2 | x1   | y1   |    8 |    0 | NULL | 2021-06-01 14:44:29 | 2021-06-01 17:57:42 |
|  3 | x1   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  4 | x2   | y2   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  5 | x2   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  6 | x2   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  7 | x3   | y3   | NULL |    1 |    1 | NULL                | 2021-06-01 17:58:26 |
+----+------+------+------+------+------+---------------------+---------------------+
7 rows in set (0.00 sec)

mysql>

现在，我们把sql_mode的值，增加

ERROR_FOR_DIVISION_BY_ZERO，然后再看下是否还可以使用0作为分母。操作如下：

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec) # 这里设置sql_mode的参数后，出现了一个warning，下面看下具体警告内容

# 通过下面的warning信息，我们知道ERROR_FOR_DIVISION_BY_ZERO参数需要配合STRICT_TRANS_TABLES参数一起使用。
mysql> show warningsG
*************************** 1. row ***************************
  Level: Warning
   Code: 3135
Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1 row in set (0.00 sec)

# 重新设置sql_mode的值，增加上STRICT_TRANS_TABLES的配置。
mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql> select 4/0;
+------+
| 4/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec) # 这里产生了一个警告，下面查看下警告信息是什么。

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

mysql> select * from t;
+----+------+------+------+------+------+---------------------+---------------------+
| id | a    | b    | x    | y    | z    | c                   | d                   |
+----+------+------+------+------+------+---------------------+---------------------+
|  1 | x1   | y1   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  2 | x1   | y1   |    8 |    0 | NULL | 2021-06-01 14:44:29 | 2021-06-01 17:57:42 |
|  3 | x1   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  4 | x2   | y2   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  5 | x2   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  6 | x2   | y3   |    8 |    0 |   11 | 2021-06-01 14:44:29 | 2021-06-01 17:57:36 |
|  7 | x3   | y3   | NULL |    1 |    1 | NULL                | 2021-06-01 17:58:26 |
+----+------+------+------+------+------+---------------------+---------------------+
7 rows in set (0.00 sec)

mysql> insert into t(a,b,x,y,z) values ('x4','y4',2/0,2,2); # 插入语句中，有涉及到0作为分母的情况，插入失败。
ERROR 1365 (22012): Division by 0

mysql> update t set z = x / y where id = 3; # 更新语句中，有涉及到0作为分母的情况，更新失败。
ERROR 1365 (22012): Division by 0

mysql>

通过上面的实验，我们可以得知，当sql_mode设置上

ERROR_FOR_DIVISION_BY_ZERO之后，当我们再使用0作为分母的时候，select语句就会出现相应的警告信息，而insert、update语句就是出现相应的错误信息。

建议将sql_mode的参数设置为包含

ERROR_FOR_DIVISION_BY_ZERO，这样可以方便我们检查我们的SQL语句中使用0作为除数的问题。

注意：Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and '

ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.，通过这个提示，我们知道在配置这三个参数的时候，需要一起配置上严格模式，也就是需要和STRICT_TRANS_TABLES一起使用才可以。

NO_ZERO_IN_DATE

如果sql_mode的参数列表中包含NO_ZERO_IN_DATE，那么表示日期类型YYYY-MM-DD字段中的MM和DD，都不能出现00这样的月或日，如果查询，则属于非法数据，不允许插入或更新到数据库中。但是，有一种特殊情况：0000-00-00这一种数据属于合法数据。

下面是没有设置NO_ZERO_IN_DATE参数值的sql_mode，如下所示：

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.01 sec)

mysql>

我们来验证一些不正确的日期数据插入到表中的结果是怎样的。如下：

mysql> desc t;
+-------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type        | Null | Key | Default           | Extra                       |
+-------+-------------+------+-----+-------------------+-----------------------------+
| id    | int(11)     | NO   | PRI | NULL              | auto_increment              |
| a     | varchar(16) | YES  |     | NULL              |                             |
| b     | varchar(64) | YES  |     | NULL              |                             |
| x     | int(11)     | YES  |     | NULL              |                             |
| y     | int(11)     | YES  |     | NULL              |                             |
| z     | int(11)     | YES  |     | NULL              |                             |
| c     | date        | YES  |     | NULL              |                             |
| d     | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_IN_DATE',1,1,1,'0000-00-00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_IN_DATE',1,1,1,'2021-00-00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_IN_DATE',1,1,1,'0000-06-00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_IN_DATE',1,1,1,'0000-00-01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_IN_DATE',1,1,1,'0000-06-01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_IN_DATE',1,1,1,'2021-00-01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_IN_DATE',1,1,1,'2021-06-00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+----------+-------------------------+------+------+------+------------+---------------------+
| id | a        | b                       | x    | y    | z    | c          | d                   |
+----+----------+-------------------------+------+------+------+------------+---------------------+
|  1 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-00 | 2021-06-02 17:38:49 |
|  2 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-00 | 2021-06-02 17:38:49 |
|  3 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-00 | 2021-06-02 17:38:49 |
|  4 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-01 | 2021-06-02 17:38:49 |
|  5 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-01 | 2021-06-02 17:38:49 |
|  6 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-01 | 2021-06-02 17:38:49 |
|  7 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-06-00 | 2021-06-02 17:38:50 |
+----+----------+-------------------------+------+------+------+------------+---------------------+
7 rows in set (0.01 sec)

mysql>

通过上面的实验结果可以发现，对于YYYY-MM-DD日期格式的数据，不管YYYY和MM还是DD，它们的值都可以为0000或者00。这些数的都是脏数据，我们正常情况是不希望这样的数据可以成功插入表中。下面我们看下在sql_mode中配置上NO_ZERO_IN_DATE之后，这些数据是否可以再次插入成功。

下面设置sql_mode的值中，增加NO_ZERO_IN_DATE参数，如下：

mysql> set session sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.01 sec)

mysql>

再次插入数据看下结果：

mysql>
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE',2,2,2,'0000-00-00');# 成功，月份MM和日DD都为00，同时年份也为0，这是一种特殊的case。
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE',2,2,2,'2021-00-00'); # 失败了，因为月份MM为00，不满足月和日都不为0的要求。
ERROR 1292 (22007): Incorrect date value: '2021-00-00' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE',2,2,2,'0000-06-00'); # 失败了，因为日DD为00，不满足月和日都不为0的要求。
ERROR 1292 (22007): Incorrect date value: '0000-00-06' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE',2,2,2,'0000-00-01'); # 失败了，因为月份MM为00，不满足月和日都不为0的要求。
ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE',2,2,2,'0000-06-01'); # 成功，月份MM和日DD都不为00
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE',2,2,2,'2021-00-01'); # 失败了，因为月份MM为00，不满足月和日都不为0的要求。
ERROR 1292 (22007): Incorrect date value: '2021-01-00' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE',2,2,2,'2021-06-00'); # 失败了，因为日DD为00，不满足月和日都不为0的要求。
ERROR 1292 (22007): Incorrect date value: '2021-00-06' for column 'c' at row 1

mysql> select * from t;
+----+----------+-------------------------+------+------+------+------------+---------------------+
| id | a        | b                       | x    | y    | z    | c          | d                   |
+----+----------+-------------------------+------+------+------+------------+---------------------+
|  1 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-00 | 2021-06-02 17:38:49 |
|  2 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-00 | 2021-06-02 17:38:49 |
|  3 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-00 | 2021-06-02 17:38:49 |
|  4 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-01 | 2021-06-02 17:38:49 |
|  5 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-01 | 2021-06-02 17:38:49 |
|  6 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-01 | 2021-06-02 17:38:49 |
|  7 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-06-00 | 2021-06-02 17:38:50 |
|  8 | sql_mode | with NO_ZERO_IN_DATE    |    2 |    2 |    2 | 0000-00-00 | 2021-06-02 17:42:08 |
|  9 | sql_mode | with NO_ZERO_IN_DATE    |    2 |    2 |    2 | 0000-06-01 | 2021-06-02 17:42:08 |
+----+----------+-------------------------+------+------+------+------------+---------------------+
9 rows in set (0.00 sec)

mysql>

通过上面的实验，我们发现当sql_mode配置上NO_ZERO_IN_DATE之后，在YYYY-MM-DD日期格式的字段中，除了0000-00-00可以成功插入之外，其他的任何日期中，只要是MM或者DD两个位置有任何一个位置是00，那么就会认为这个日期是非法日期。

综上两个实验，我们可以得出结论：NO_ZERO_IN_DATE的作用是，验证月和日两个位置上的数字都不能为00的日期才可以正常插入到表中，它不管年份的值是多少。但是有一种特殊情况除外，就是当年份YYYY的值为0000的时候，即便它的月和日都为00，也就是日期为：0000-00-00时，这一条特殊的数据是可以插入的。

NO_ZERO_DATE

当sql_mode中包含NO_ZERO_DATE参数值的时候，表示日期格式YYYY-MM-DD的数据中，年份YYYY不允许出现0000这样的年份，如果出现了表示这是非法的日期，不允许插入或更新到表中。

设置sql_mode的值，让其不包含NO_ZERO_DATE值，如下所示，注意：此时我们把前面设置的NO_ZERO_IN_DATE参数也去掉，避免NO_ZERO_IN_DATE参数值带来的影响。

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql>

在sql_mode没有配置NO_ZERO_DATE和NO_ZERO_IN_DATE参数的情况下，插入非法的日期数据，看是否才可以插入成功：

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_DATE',3,3,3,'0000-00-00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_DATE',3,3,3,'2021-00-00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_DATE',3,3,3,'0000-06-00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_DATE',3,3,3,'0000-00-01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_DATE',3,3,3,'0000-06-01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_DATE',3,3,3,'2021-00-01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','without NO_ZERO_DATE',3,3,3,'2021-06-00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+----------+-------------------------+------+------+------+------------+---------------------+
| id | a        | b                       | x    | y    | z    | c          | d                   |
+----+----------+-------------------------+------+------+------+------------+---------------------+
|  1 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-00 | 2021-06-02 17:38:49 |
|  2 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-00 | 2021-06-02 17:38:49 |
|  3 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-00 | 2021-06-02 17:38:49 |
|  4 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-01 | 2021-06-02 17:38:49 |
|  5 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-01 | 2021-06-02 17:38:49 |
|  6 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-01 | 2021-06-02 17:38:49 |
|  7 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-06-00 | 2021-06-02 17:38:50 |
|  8 | sql_mode | with NO_ZERO_IN_DATE    |    2 |    2 |    2 | 0000-00-00 | 2021-06-02 17:42:08 |
|  9 | sql_mode | with NO_ZERO_IN_DATE    |    2 |    2 |    2 | 0000-06-01 | 2021-06-02 17:42:08 |
| 10 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-00-00 | 2021-06-02 17:53:11 |
| 11 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 2021-00-00 | 2021-06-02 17:53:11 |
| 12 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-06-00 | 2021-06-02 17:53:11 |
| 13 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-00-01 | 2021-06-02 17:53:11 |
| 14 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-06-01 | 2021-06-02 17:53:11 |
| 15 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 2021-00-01 | 2021-06-02 17:53:11 |
| 16 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 2021-06-00 | 2021-06-02 17:53:12 |
+----+----------+-------------------------+------+------+------+------------+---------------------+
16 rows in set (0.00 sec)

mysql>

通过上面的实验，发现非法的日期是可以插入成功了。下面我们增加上NO_ZERO_DATE参数后，再进行插入实验。注意：此时我们把前面设置的NO_ZERO_IN_DATE参数需要去掉，单独验证NO_ZERO_DATE参数值的功能。避免NO_ZERO_IN_DATE参数值带来的影响。

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.01 sec)

mysql>

在sql_mode只配置NO_ZERO_DATE，没有配置NO_ZERO_IN_DATE参数的情况下，插入非法的日期数据，看是否才可以插入成功：

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_DATE',4,4,4,'0000-00-00'); # 失败，因为年月日三个部分都是0。
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_DATE',4,4,4,'2021-00-00'); # 成功，因为年月日三个部分中，存在至少一个不为0的部分。
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_DATE',4,4,4,'0000-06-00');# 成功，因为年月日三个部分中，存在至少一个不为0的部分。
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_DATE',4,4,4,'0000-00-01');# 成功，因为年月日三个部分中，存在至少一个不为0的部分。
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_DATE',4,4,4,'0000-06-01');# 成功，因为年月日三个部分中，存在至少一个不为0的部分。
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_DATE',4,4,4,'2021-00-01');# 成功，因为年月日三个部分中，存在至少一个不为0的部分。
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_DATE',4,4,4,'2021-06-00');# 成功，因为年月日三个部分中，存在至少一个不为0的部分。
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+----------+-------------------------+------+------+------+------------+---------------------+
| id | a        | b                       | x    | y    | z    | c          | d                   |
+----+----------+-------------------------+------+------+------+------------+---------------------+
|  1 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-00 | 2021-06-02 17:38:49 |
|  2 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-00 | 2021-06-02 17:38:49 |
|  3 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-00 | 2021-06-02 17:38:49 |
|  4 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-00-01 | 2021-06-02 17:38:49 |
|  5 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 0000-06-01 | 2021-06-02 17:38:49 |
|  6 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-00-01 | 2021-06-02 17:38:49 |
|  7 | sql_mode | without NO_ZERO_IN_DATE |    1 |    1 |    1 | 2021-06-00 | 2021-06-02 17:38:50 |
|  8 | sql_mode | with NO_ZERO_IN_DATE    |    2 |    2 |    2 | 0000-00-00 | 2021-06-02 17:42:08 |
|  9 | sql_mode | with NO_ZERO_IN_DATE    |    2 |    2 |    2 | 0000-06-01 | 2021-06-02 17:42:08 |
| 10 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-00-00 | 2021-06-02 17:53:11 |
| 11 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 2021-00-00 | 2021-06-02 17:53:11 |
| 12 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-06-00 | 2021-06-02 17:53:11 |
| 13 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-00-01 | 2021-06-02 17:53:11 |
| 14 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 0000-06-01 | 2021-06-02 17:53:11 |
| 15 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 2021-00-01 | 2021-06-02 17:53:11 |
| 16 | sql_mode | without NO_ZERO_DATE    |    3 |    3 |    3 | 2021-06-00 | 2021-06-02 17:53:12 |
| 17 | sql_mode | with NO_ZERO_DATE       |    4 |    4 |    4 | 2021-00-00 | 2021-06-02 17:56:07 |
| 18 | sql_mode | with NO_ZERO_DATE       |    4 |    4 |    4 | 0000-06-00 | 2021-06-02 17:56:07 |
| 19 | sql_mode | with NO_ZERO_DATE       |    4 |    4 |    4 | 0000-00-01 | 2021-06-02 17:56:07 |
| 20 | sql_mode | with NO_ZERO_DATE       |    4 |    4 |    4 | 0000-06-01 | 2021-06-02 17:56:07 |
| 21 | sql_mode | with NO_ZERO_DATE       |    4 |    4 |    4 | 2021-00-01 | 2021-06-02 17:56:07 |
| 22 | sql_mode | with NO_ZERO_DATE       |    4 |    4 |    4 | 2021-06-00 | 2021-06-02 17:56:07 |
+----+----------+-------------------------+------+------+------+------------+---------------------+
22 rows in set (0.00 sec)

mysql>

通过上面的实验我们发现，在sql_mode总包含NO_ZERO_DATE参数后，对于日期格式YYYY-MM-DD的验证，只要这YYYY、MM、DD三部分中存在任何一个或多个不为0的情况下，日期都是可以正常插入到数据表中的。也就是说，NO_ZERO_DATE参数的作用是控制YYYY-MM-DD年月日中至少有一个不为0才算是合法的日期格式。只有年月日三个部分全都为0的时候，才会认为是非法日期。

基于前面NO_ZERO_IN_DATE的实验，我们已经知道，在sql_mode中包含NO_ZERO_IN_DATE的设置的情况想，在 YYYY-MM-DD日期中，如果MM或DD任何一个位置出现00，则会非法的日期。但是针对一种特殊的情况：0000-00-00，这样的一个日期它并不认为是非法日期，对于年份全部为0这样的日期是可以插入表中的。那么如何排除这样的一种日期呢？参数值NO_ZERO_DATE的作用就是用来过滤这个情况的。

下面我们来实验一下，在sql_mode中同时配置上NO_ZERO_DATE和NO_ZERO_IN_DATE两个值，我们再来进一步测试一下0000-00-00这样的非法日期是否仍然可以正常插入到表中：

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql>

下面我们分别插入在前面实验中sql_mode包含NO_ZERO_IN_DATE参数值的时候，成功插入的两条数据0000-00-00和0000-06-01是否还可以插入成功。

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE and NO_ZERO_DATE',5,5,5,'0000-00-00'); # 失败，因为NO_ZERO_DATE参数控制年月日三个部分至少有一个部分不为0才可以插入成功。
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE and NO_ZERO_DATE',5,5,5,'2021-00-00'); # 失败，因为NO_ZERO_IN_DATE参数控制，月和日必须都不为0才可以插入成功。
ERROR 1292 (22007): Incorrect date value: '2021-00-00' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE and NO_ZERO_DATE',5,5,5,'0000-06-00'); # 失败，因为NO_ZERO_IN_DATE参数控制，月和日必须都不为0才可以插入成功。
ERROR 1292 (22007): Incorrect date value: '0000-06-00' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE and NO_ZERO_DATE',5,5,5,'0000-00-01'); # 失败，因为NO_ZERO_IN_DATE参数控制，月和日必须都不为0才可以插入成功。
ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE and NO_ZERO_DATE',5,5,5,'0000-06-01'); # 成功，因为NO_ZERO_IN_DATE参数控制，月和日必须都不为0才可以插入成功，满足这个条件，同时NO_ZERO_DATE参数控制年月日三个部分至少有一个部分不为0，也满足这个条件，所以可以插入成功。
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE and NO_ZERO_DATE',5,5,5,'2021-00-01'); # 失败，因为NO_ZERO_IN_DATE参数控制，月和日必须都不为0才可以插入成功。
ERROR 1292 (22007): Incorrect date value: '2021-00-01' for column 'c' at row 1
mysql> insert into t(a,b,x,y,z,c) values('sql_mode','with NO_ZERO_IN_DATE and NO_ZERO_DATE',5,5,5,'2021-06-00'); # 失败，因为NO_ZERO_IN_DATE参数控制，月和日必须都不为0才可以插入成功。
ERROR 1292 (22007): Incorrect date value: '2021-06-00' for column 'c' at row 1
mysql>
mysql> select * from t;
+----+----------+---------------------------------------+------+------+------+------------+---------------------+
| id | a        | b                                     | x    | y    | z    | c          | d                   |
+----+----------+---------------------------------------+------+------+------+------------+---------------------+
|  1 | sql_mode | without NO_ZERO_IN_DATE               |    1 |    1 |    1 | 0000-00-00 | 2021-06-02 17:38:49 |
|  2 | sql_mode | without NO_ZERO_IN_DATE               |    1 |    1 |    1 | 2021-00-00 | 2021-06-02 17:38:49 |
|  3 | sql_mode | without NO_ZERO_IN_DATE               |    1 |    1 |    1 | 0000-06-00 | 2021-06-02 17:38:49 |
|  4 | sql_mode | without NO_ZERO_IN_DATE               |    1 |    1 |    1 | 0000-00-01 | 2021-06-02 17:38:49 |
|  5 | sql_mode | without NO_ZERO_IN_DATE               |    1 |    1 |    1 | 0000-06-01 | 2021-06-02 17:38:49 |
|  6 | sql_mode | without NO_ZERO_IN_DATE               |    1 |    1 |    1 | 2021-00-01 | 2021-06-02 17:38:49 |
|  7 | sql_mode | without NO_ZERO_IN_DATE               |    1 |    1 |    1 | 2021-06-00 | 2021-06-02 17:38:50 |
|  8 | sql_mode | with NO_ZERO_IN_DATE                  |    2 |    2 |    2 | 0000-00-00 | 2021-06-02 17:42:08 |
|  9 | sql_mode | with NO_ZERO_IN_DATE                  |    2 |    2 |    2 | 0000-06-01 | 2021-06-02 17:42:08 |
| 10 | sql_mode | without NO_ZERO_DATE                  |    3 |    3 |    3 | 0000-00-00 | 2021-06-02 17:53:11 |
| 11 | sql_mode | without NO_ZERO_DATE                  |    3 |    3 |    3 | 2021-00-00 | 2021-06-02 17:53:11 |
| 12 | sql_mode | without NO_ZERO_DATE                  |    3 |    3 |    3 | 0000-06-00 | 2021-06-02 17:53:11 |
| 13 | sql_mode | without NO_ZERO_DATE                  |    3 |    3 |    3 | 0000-00-01 | 2021-06-02 17:53:11 |
| 14 | sql_mode | without NO_ZERO_DATE                  |    3 |    3 |    3 | 0000-06-01 | 2021-06-02 17:53:11 |
| 15 | sql_mode | without NO_ZERO_DATE                  |    3 |    3 |    3 | 2021-00-01 | 2021-06-02 17:53:11 |
| 16 | sql_mode | without NO_ZERO_DATE                  |    3 |    3 |    3 | 2021-06-00 | 2021-06-02 17:53:12 |
| 17 | sql_mode | with NO_ZERO_DATE                     |    4 |    4 |    4 | 2021-00-00 | 2021-06-02 17:56:07 |
| 18 | sql_mode | with NO_ZERO_DATE                     |    4 |    4 |    4 | 0000-06-00 | 2021-06-02 17:56:07 |
| 19 | sql_mode | with NO_ZERO_DATE                     |    4 |    4 |    4 | 0000-00-01 | 2021-06-02 17:56:07 |
| 20 | sql_mode | with NO_ZERO_DATE                     |    4 |    4 |    4 | 0000-06-01 | 2021-06-02 17:56:07 |
| 21 | sql_mode | with NO_ZERO_DATE                     |    4 |    4 |    4 | 2021-00-01 | 2021-06-02 17:56:07 |
| 22 | sql_mode | with NO_ZERO_DATE                     |    4 |    4 |    4 | 2021-06-00 | 2021-06-02 17:56:07 |
| 23 | sql_mode | with NO_ZERO_IN_DATE and NO_ZERO_DATE |    5 |    5 |    5 | 0000-06-01 | 2021-06-02 18:09:06 |
+----+----------+---------------------------------------+------+------+------+------------+---------------------+
23 rows in set (0.00 sec)

mysql>

经过上面的实验我们发现，在sql_mode增加上NO_ZERO_DATE参数后，对于原本只有NO_ZERO_IN_DATE参数时，可以插入成功的0000-MM-DD这样的非法日期，此时在NO_ZERO_DATE参数的作用下，也不能插入成功了。但是对于0000-06-01这样的日期还是可以插入成功的。因为这种格式的日期，同时满足了NO_ZERO_IN_DATE和NO_ZERO_DATE两个参数的要求。

小结：NO_ZERO_DATE的作用是控制YYYY-MM-DD日期中的年月日三个部分不能全都为0，需要至少存在一个不是0的部分才认为是合法的日期格式；而NO_ZERO_IN_DATE控制的是MM和DD都不能为00才认为是合法的日期格式，月和日两部分任何一个部分为0就认为是非法的日期。在同时设置了NO_ZERO_DATE和NO_ZERO_IN_DATE之后，可以避免0000-00-00这样的日期插入，但是仍然可以插入0000-06-01年份为0的日期数据。

另外一点需要注意：在配置NO_ZERO_DATE和NO_ZERO_IN_DATE参数的时候，需要和STRICT_TRANS_TABLES参数一起配合使用。如果开启了：NO_ZERO_DATA、NO_ZERO_IN_DATE、

ERROR_FOR_DIVISION_BY_ZERO，则需要开启STRICT_TRANS_TABLES，反之亦然。否则开启任何一边，在设置完sql_mode参数值后，后面都会出现警告信息，可以通过show warnings命令查看警告信息的详细内容。

NO_ENGINE_SUBSTITUTION

在sql_mode中配置上NO_ENGINE_SUBSTITUTION参数之后，在我们创建表的时候，如果为表指定的存储引擎在当前的MySQL实例中并不支持或者没有启用的话，那么表会创建失败并抛出一个error错误信息。如果没有配置这个参数，那么当创建一个不支持的存储引擎表的时候，表或创建成功并且会抛出一个warning警告信息，会使用MySQL中默认的存储引擎来创建这个表。

下面我们来看一下在sql_mode在没有设置配置NO_ENGINE_SUBSUITUTION参数的时候，创建一个不支持的存储引擎的表，会有什么效果。

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql>

查看当前MySQL实例锁支持的存储引擎有哪些，使用下面的show engines命令即可：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disAppears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

mysql>

通过上面的输出可以看出当前的MySQL实例并不支持NDB存储引擎，并且其默认的存储引擎为InnoDB，下面我们创建一个NDB存储引擎的表tab1_ndb，看下是否可以创建成功。

mysql> create table tab1_ndb(id int, a varchar(16))engine=ndb;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1286 | Unknown storage engine 'ndb'                    |
| Warning | 1266 | Using storage engine InnoDB for table 'tab1_ndb' |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table tab1_ndbG
*************************** 1. row ***************************
       Table: tab1_ndb
Create Table: CREATE TABLE "tab1_ndb" (
  "id" int(11) DEFAULT NULL,
  "a" varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

通过上面的输出，可以看出NDB存储引擎的表虽然被创建了，但是它的存储因并不是我们想要的NDB存储引擎，而是使用了当前MySQL实例中默认的InnoDB存储引擎的表来代替的NDB存储引擎。并且在创建表的时候，抛出了两条warning信息，提示我们说NDB存储引擎不支持，使用了InnoDB存储引擎来代替了。

上面就是在sql_mode没有配置NO_ENGINE_SUBSTITUTION参数的时候的现象。下面我们来为sql_mode配置上NO_ENGINE_SUBSTITUTION参数后，做同样的实验，看下是否可以创建成功对应的表。

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql>

创建NDB存储引擎的表，看下是否可以创建成，实验过程如下：

mysql> create table tab2_ndb(id int, a varchar(16))engine=ndb;
ERROR 1286 (42000): Unknown storage engine 'ndb'
mysql>

通过上面的输出可以知道，NDB存储引擎的表创建失败，直接抛出了一个错误信息。这就是在sql_mode中配置了NO_ENGINE_SUBSTITUTION参数后的效果。建议在sql_mode中配置这一个参数，这样可以避免我们期望的表创建成功能了，但是存储引擎并不是我们想要的存储引擎，与其以后会采坑，倒不如现在直接让其创建失败并抛出错误异常信息，让我们知道我们的表创建失败，这样会及时的发现问题解决问题。

NO_AUTO_VALUE_ON_ZERO

在sql_mode中没有配置NO_AUTO_VALUE_ON_ZERO这个参数的时候，当我们向一个自增的字段中插入数据的时候，如果我们为自增字段赋予的值为0，则不会使用我们指定的值，而是使用自增值来替换我们指定的值0；而当sql_mode配置上NO_AUTO_VALUE_ON_ZERO参数后，则会使用我们指定的0来给自增字段赋值，而不是使用自增序列的值来填充自增字段。

下面的sql_mode中是没有配置NO_AUTO_VALUE_ON_ZERO参数，我们看下当我们向自增的字段中设置0的时候，插入数据会有什么现象。

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql>

下面针对测试的表进行插入数据，这个表的主键是自增主键，我们把主键值设置为0，看下执行效果：

mysql> desc tab1_ndb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| a     | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into tab1_ndb(id,a) values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1_ndb(id,a) values(0,'b'); # 主键值设置为0，插入的时候，并没有使用0，而是使用的自增的值2
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1_ndb(id,a) values(0,'c'); # 主键值设置为0，插入的时候，并没有使用0，而是使用的自增的值3
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1_ndb(id,a) values(null,'d'); # 主键值设置为null，插入的时候，并没有使用0，而是使用的自增的值4
Query OK, 1 row affected (0.00 sec)

mysql> select * from tab1_ndb; # 查看插入数据后的结果
+----+------+
| id | a    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

mysql>

通过上面的实验，我们可以知道，当我们为自增列的值设置为0的时候，并不会使用0来填充自增字段，而是使用了自增序列的值来填充在自增字段中。这就是在sql_mode中没有配置NO_AUTO_VALUE_ON_ZERO参数的效果。

下面我们为sql_mode配置上NO_AUTO_VALUE_ON_ZERO参数，然后再看下向自增序列中插入0的效果是什么样子的。

mysql> set session sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql>

下面继续向tab1_ndb表中插入数据，验证结果：

mysql> select * from tab1_ndb;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

mysql> insert into tab1_ndb(id,a) values(null,'e');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1_ndb(id,a) values(0,'f');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1_ndb(id,a) values(0,'g');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

mysql> select * from tab1_ndb;
+----+------+
| id | a    |
+----+------+
|  0 | f    |
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
6 rows in set (0.01 sec)

mysql>

通过上面的实验，我们可以看出在sql_mode中配置上NO_AUTO_VALUE_ON_ZERO后，当我们向一个自增字段插入数据的时候，为其赋值为0后，就是使用这个0而不会再从自增序列中获取对应的自增值了。

另外需要注意的一点：不管是否配置NO_AUTO_VALUE_ON_ZERO参数，对于向自增字段中赋予的null值，都会使用自增序列中的值来填充自增字段，而不会使用null来填充。只要0的时候，才会有所区别。

建议把NO_AUTO_VALUE_ON_ZERO值配置在sql_mode中，这样可以避免向自增字段中插入的值不是我们希望的值，例如我们希望赋予0，但是结果去不是以0的值插入的，而是取得一个自增序列的值替代的0。

总结

结合上面的实验，我们可以对sql_mode的各参数值作出如下的总结：

ONLY_FULL_GROUP_BY：配置上这个参数后，在group by语句子句中需要包含出现在select后面所有的非聚合列。建议配置到sql_mode中。
ANSI_QUOTES：配置上这个参数后，在SQL语句中只能使用单引号包裹字符串，不能使用双引号包裹字符串。建议配置到sql_mode中。
PIPES_AS_CONCAT：配置上行这个参数后，在SQL语句中可以使用双竖线||来作为字符串连接的符号，而不是把它当做或运算的运算符。建议配置到sql_mode中。
NO_AUTO_CREATE_USER：配置上这个参数之后，在使用grant语句创建用户并授权的时候，用户的密码必须要指定，并且不能设置为空字符串。建议配置到sql_mode中。
STRICT_TRANS_TABLES：控制对于一些不符合约束条件的数据，在对支持事务的表进行操作的时候，是否会回滚所有的操作。配置上则对于不符合条件的数据抛出异常错误信息，并且回滚当前事务前面的所有操作。不配置上这个参数，对于不符合条件的约束的数据，则发生隐式转换，并且抛出一个警告信息，继续当前事务后面的所有操作。对于不支持事务的表，这个参数不起作用，都不会终止SQL操作。建议配置到sql_mode中。
STRICT_ALL_TABLES：功能和STRICT_TRANS_TABLES类似，但是它主要是用来针对不支持事务的存储引擎来用的，对于支持事务的存储引擎来说，这个参数的功能和STRICT_TRANS_TABLES的功能一样。对于不支持事务的存储引擎，配置上这个参数的作用是对于批量插入的数据，如果执行到中间某个位置的时候，出现不合规的数据行，则终止从当前的操作，之前成功的数据不会回滚，但是从当前行往后的数据不会在支持插入操作。
ERROR_FOR_DIVISION_BY_ZERO：控制SQL语句中使用0作为除数的时候是否抛出错误异常，配置改值后，SQL语句中使用0作为除数的时候，会抛出错误终止SQL语句，不配置只是抛出一个警告信息。建议配置到sql_mode中。
NO_ZERO_IN_DATE：控制YYYY-MM-DD中的MM和DD都不为00才认为是合法的日期。建议配置到sql_mode中。
NO_ZERO_DATE：控制YYYY-MM-DD中的年月日三部分至少有一个不是0才认为是合法的日期。建议配置到sql_mode中。
NO_ENGINE_SUBSTITUTION：如果配置了这个参数后，当创建一个MySQL实例不支持的或者禁用的存储引擎的表的时候，会直接抛出一个错误信息，并终止创建表的语句。如果没有配置这个参数，则会使用MySQL默认的存储引擎来替换不支持的存储引擎，然后创建表成功，但是会抛出一个警告信息。建议配置到sql_mode中。
NO_AUTO_VALUE_ON_ZERO：配置上该这个参数后，对于自增字段的赋值，如果是赋予的0则会使用0来填充自增字段的之后，而不是使用自增序列的值来填充。如果不配置，当我们给自增字段赋值为0的时候，则不会使用0而是从自增序列中获取对应的值来填充到自增字段中。建议配置到sql_mode中。

欢迎转发评论。

Tags：sql_mode 点击:() 评论:()

声明：本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com)，我们将及时更正、删除，谢谢。

▌相关推荐

MySQL中的sql_mode的解读

sql_mode是MySQL中比较重要的一个全局参数，这个参数的值对MySQL整体的运行有着极为重要的意义。但是一般情况下，大家并不怎么的去修改它的值，都是使用默认值。但是当我们在数据库之间迁移的时候，很有可能遇到这个参数引起...【详细内容】

2021-06-08　　Tags: sql_mode 点击:(107)　　评论:(0)　　加入收藏

▌简易百科推荐

MySQL 使用 Mysqldump 备份导入数据导致主从异常

作者：雷文霆爱可生华东交付服务部 DBA 成员，主要负责Mysql故障处理及相关技术支持。爱好看书，电影。座右铭，每一个不曾起舞的日子，都是对生命的辜负。本文来源：原创投稿 *爱可生...【详细内容】

2021-12-24　　爱可生　　　　Tags:MySQL 　点击:(6)　　评论:(0)　　加入收藏

分享MySQL记录锁、间隙锁、临键锁小案例演示，你学废了吗

生成间隙(gap)锁、临键(next-key)锁的前提条件是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过，详细内容可以看这篇文章...【详细内容】

2021-12-14　　python数据分析　　　　Tags:MySQL记录锁　点击:(17)　　评论:(0)　　加入收藏

mysqlbinlog基本认识

binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二...【详细内容】

2021-12-14　　linux上的码农　　　　Tags:mysql 　点击:(13)　　评论:(0)　　加入收藏

百万级数据下的mysql深度解析

为查询优化你的查询大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查...【详细内容】

2021-12-09　　元宇宙iwemeta　　　　Tags:mysql 　点击:(15)　　评论:(0)　　加入收藏

Mysql数据库tinyint，int，bigint，char，varchar究竟用哪个？

测试的目的和原因，公司有很多程序员，每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑，那么我就来测试一下究竟哪种使...【详细内容】

2021-12-08　　吴彬的分享　　　　Tags:Mysql数据库　点击:(14)　　评论:(0)　　加入收藏

Win下部署多个MySQL数据库实例

当你们考虑项目并发的时候，我在部署环境，当你们在纠结使用ArrayList还是LinkedArrayList的时候，我还是在部署环境。所以啊，技术不止境，我在部环境。今天这篇文章缕一下在同一台服...【详细内容】

2021-12-08　　秃头码哥　　　　Tags:MySQL数据库　点击:(16)　　评论:(0)　　加入收藏

MySQL中常用的15个查询子句

对于数据分析来说，MySQL使用最多的是查询，比如对数据进行排序、分组、去重、汇总及字符串匹配等，如果查询的数据涉及多个表，还需要要对表进行连接，本文就来说说MySQL中常用的查询...【详细内容】

2021-12-06　　笨鸟学数据分析　　　　Tags:MySQL 　点击:(19)　　评论:(0)　　加入收藏

15个MySQL常用基本SQL语句

在学习SQL语句之前，首先需要区分几个概念，我们常说的数据库是指数据库软件，例如MySQL、Oracle、SQL Server等，而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】

2021-11-24　　笨鸟学数据分析　　　　Tags:SQL语句　点击:(23)　　评论:(0)　　加入收藏

用 MySQL 实现分布式锁，你听过吗？

概述以前参加过一个库存系统，由于其业务复杂性，搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说，有定时任务域xx.cron，和SystemA域...【详细内容】

2021-11-05　　Java云海　　　　Tags:分布式锁　点击:(31)　　评论:(0)　　加入收藏

MySQL高级SQL语句

MySQL的进阶查询一、按关键字排序使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序，默认排序方式【升序是从小到大】DESC:降序【降序是从大到小】ORDER BY的...【详细内容】

2021-11-05　　Java热点　　　　Tags:SQL语句　点击:(27)　　评论:(0)　　加入收藏

推荐资讯

远程软件发展迅猛，ToDe	倒计时！企业QQ即将下架
极简Windows11与iPhon	iPhone信号问题，花10元
惊人数据：App Store中4	个人所得税递延纳税报
非常实用的 Python 库	等离子电视技术先进，为

无相关信息