MySQL SQL_MODE详解：严格模式、兼容行为与数据校验

在数据库服务应用过程中存在SQL_mode概念（SQL模式），规范SQL执行行为和数据的准确性，能够符合数据录入常识和执行结果意义

例如：日期信息不能出现 0000-00-00 信息，月份只能是1-12，日期只能是1-31，一旦违反常识便会报错；

例如：在进行数据运算时，除法运算时，除数不能为0；

例如：当定义数据类型为char(10)，不能超过字符长度，超过长度就报错；

例如：设置only_full_group_by（5.7以后的特性），禁止进行分组查询时，出现聚合信息1对多的显示输出；

获取SQLmode设置的默认信息：

mysql> select @@sql_mode;

Day003-数据库服务基础命令应用-图10

SQLmode配置参数信息解释说明：

序号	模式参数配置	解释说明
01	`ONLY_FULL_GROUP_BY`	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。
02	`STRICT_TRANS_TABLES`	在该模式下，如果一个值不能插入到一个事物表中，则中断当前的操作，对非事物表不做限制
03	`NO_ZERO_IN_DATE`	在严格模式下，不允许日期和月份为零
04	`NO_ZERO_DATE`	设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
05	`ERROR_FOR_DIVISION_BY_ZERO`	在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
06	NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
07	NO_AUTO_VALUE_ON_ZERO	该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，该列又是自增长的，那么这个选项就有用了。
08	NO_AUTO_CREATE_USER	禁止GRANT创建密码为空的用户
09	PIPES_AS_CONCAT	将"\|\|"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
10	ANSI_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

验证模式参数NO_ZERO_IN_DATE和NO_ZERO_DATE

# 创建数据库
mysql> create database db12 charset utf8 collate utf8_general_mysql500_ci;

# 创建数据表，并设置相同的字符集，以及不同的字符校对规则(mysql8环境)；
mysql> CREATE TABLE db12.t1 (
  `info` char(3) DEFAULT NULL,
  `日期` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 插入数据信息
mysql> INSERT INTO db12.t1 (info) VALUES ('a'), ('A'), ('b'), ('B'), ('c'), ('C');

#验证查看
mysql> select * from db12.t1;
+------+--------+
| info | 日期   |
+------+--------+
| a    | NULL   |
| A    | NULL   |
| b    | NULL   |
| B    | NULL   |
| c    | NULL   |
| C    | NULL   |
+------+--------+
6 rows in set (0.00 sec)

#因为sql_mode定义了NO_ZERO_IN_DATE,NO_ZERO_DATE，所以设置日期为0会报错
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update db12.t1 set 日期='0000-00-00' where info='a';
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column '日期' at row 1

#暂时将sql_mode设置为空，退出登录后再次进行测试
mysql> set global sql_mode='';
mysql> quit

#重新登录重新插入数据
[root@master ~]# mysql

# 查看sql_mode设置参数，观察到为空
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

# 再次尝试插入日期为0的数据内容，观察到插入成功
mysql> update db12.t1 set 日期='0000-00-00' where info='a';

#重新插入后进行验证
mysql> select * from db12.t1;
+------+------------+
| info | 日期       |
+------+------------+
| a    | 0000-00-00 |
| A    | 0000-00-00 |
| b    | NULL       |
| B    | NULL       |
| c    | NULL       |
| C    | NULL       |
+------+------------+
6 rows in set (0.00 sec)

#验证完成后复原环境
mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql> quit
[root@master ~]# mysql
mysql> select @@sql_mode\G;
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

验证模式参数ERROR_FOR_DIVISION_BY_ZERO

# 创建数据库
mysql> create database db12 charset utf8 collate utf8_general_mysql500_ci;

# 创建数据表，并设置相同的字符集，以及不同的字符校对规则(mysql8环境)；
mysql> CREATE TABLE db12.t2 (
  `info` char(3) DEFAULT NULL,
  `日期` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 插入数据信息
mysql> INSERT INTO db12.t2 (info) VALUES ('a'), ('A'), ('b'), ('B'), ('c'), ('C');

#增加1列，列名为values
mysql> ALTER TABLE db12.t2
ADD COLUMN `values` VARCHAR(255) DEFAULT NULL;

#验证添加结果
mysql> select * from db12.t2;
+------+------------+--------+
| info | 日期       | values |
+------+------------+--------+
| a    | NULL       | NULL   |
| A    | NULL       | NULL   |
| b    | NULL       | NULL   |
| B    | NULL       | NULL   |
| c    | NULL       | NULL   |
| C    | NULL       | NULL   |
+------+------------+--------+
6 rows in set (0.00 sec)

#添加运算结果到values
mysql> update db12.t2 set `values`=20+1 where info='a';

#结果验证查看
mysql> select * from db12.t2;
+------+--------+--------+
| info | 日期   | values |
+------+--------+--------+
| a    | NULL   | 21     |
| A    | NULL   | 21     |
| b    | NULL   | NULL   |
| B    | NULL   | NULL   |
| c    | NULL   | NULL   |
| C    | NULL   | NULL   |
+------+--------+--------+
6 rows in set (0.00 sec)

#验证ERROR_FOR_DIVISION_BY_ZERO(被0除报错)
mysql> update db12.t2 set `values`=20/0 where info='a';
ERROR 1365 (22012): Division by 0

#暂时将sql_mode设置为空，退出登录后再次进行测试
mysql> set global sql_mode='';
mysql> quit

#重新登录重新插入数据
[root@master ~]# mysql
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

#重新将被0除的运算结果更新到values值
mysql> update db12.t2 set `values`=20/0 where info='a';

#验证结果，观察到被0除后结果为NULL
mysql> select * from db12.t2;
+------+--------+--------+
| info | 日期   | values |
+------+--------+--------+
| a    | NULL   | NULL   |
| A    | NULL   | NULL   |
| b    | NULL   | NULL   |
| B    | NULL   | NULL   |
| c    | NULL   | NULL   |
| C    | NULL   | NULL   |
+------+--------+--------+
6 rows in set (0.00 sec)

#验证完成后复原环境
mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql> quit
[root@master ~]# mysql
mysql> select @@sql_mode\G;
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

验证模式参数STRICT_TRANS_TABLES

# 创建数据库
mysql> create database db12 charset utf8 collate utf8_general_mysql500_ci;

# 创建数据表，并设置相同的字符集，以及不同的字符校对规则(mysql8环境)；
mysql> CREATE TABLE db12.t3 (
  `info` char(3) DEFAULT NULL,
  `日期` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 插入数据信息
mysql> INSERT INTO db12.t3 (info) VALUES ('a'), ('A'), ('b'), ('B'), ('c'), ('C');

#验证查看
mysql> select * from db12.t3;
+------+--------+
| info | 日期   |
+------+--------+
| a    | NULL   |
| A    | NULL   |
| b    | NULL   |
| B    | NULL   |
| c    | NULL   |
| C    | NULL   |
+------+--------+
6 rows in set (0.00 sec)

#因为info默认设置为3个字节，当大于3个字节因为STRICT_TRANS_TABLES参数配置导致会发生报错
mysql> insert into db12.t3 values ('abcdefg','2024-11-11');
ERROR 1406 (22001): Data too long for column 'info' at row 1

#针对此类报错有两种解决方法：
##方法一：调整info默认字节
mysql> ALTER TABLE db12.t3 MODIFY COLUMN `info` char(7) DEFAULT NULL;
mysql> desc db12.t3;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| info   | char(7) | YES  |     | NULL    |       |
| 日期   | date    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
mysql> insert into db12.t3 values ('abcdefg','2024-11-11');
Query OK, 1 row affected (0.01 sec)

##方法二：insert插入时满足info默认字节
mysql> insert into db12.t3 values ('abc','2024-11-11');

#最后验证
mysql> select * from db12.t3;
+---------+------------+
| info    | 日期       |
+---------+------------+
| a       | NULL       |
| A       | NULL       |
| b       | NULL       |
| B       | NULL       |
| c       | NULL       |
| C       | NULL       |
| abcdefg | 2024-11-11 |
| abc     | 2024-11-11 |
+---------+------------+
8 rows in set (0.00 sec)

当进行数据库服务版本升级时，可能之前版本数据信息不能满足新版本数据库服务的SQL_mode信息设定，需要暂时设置SQLmode为空

mysql> set global sql_mode='';
mysql> \q

# 配置完毕后，可以重新登录数据库服务进行检查确认
mysql> select @@sql_mode;

评论区