数据库系列之MySQL中的SQL Mode介绍_mysql sqlmode

SQL mode定义了不同的SQL语法操作，本文简要介绍了MySQL中的SQL mode类型及使用，结合具体案例使用加深理解，适用于兼容MySQL语法的分布式数据库。

---

1、Server SQL Mode介绍

MySQL Server可以运行在不同的SQL Modes下，每一类SQL Modes定义MySQL应该支持什么样的SQL语法。DBA可以设置系统级别的SQL mode，每个应用也可以根据需要设置session级别的SQL mode。在MySQL 8.0版本中默认设置以下SQL mode：

mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

在MySQL 8.0版本中支持以下SQL mode：

其中ANSI、STRICT_TRANS_TABLES和TRADITIONAL为最常用的SQL Mode。

• ANSI更接近于标准SQL，等同于REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE和ONLY_FULL_GROUP_BY
• STRICT_TRANS_TABLES：如果无法按给定的方式将值插入到事务表中，请中止该语句。对于非事务表，如果值出现在单行语句或多行语句的第一行中，则中止该语句
• TRADITIONAL：使MySQL的行为像一个“传统”的SQL数据库系统，在向列中插入错误值时，此模式“给出错误而不是警告”。等同于STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO和NO_ENGINE_SUBSTITUTION

1.1 ANSI_QUOTES

双引号””不能作为引用字符串，而是会解释为标识符。

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

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)
mysql> select "test" from tango.test01;
+------+
| test |
+------+
| test |
| test |
| test |
+------+
3 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

设置sql_mode为ansi_quotes，可以看到test标识为column列名进行查询

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

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| ANSI_QUOTES        |
+--------------------+
1 row in set (0.00 sec)

mysql> select "test" from tango.test01;
ERROR 1054 (42S22): Unknown column 'test' in 'field list'
1
2
3
4
5
6
7
8
9
10
11
12
13

1.2 ERROR_FOR_DIVISION_BY_ZERO

ERROR_FOR_DIVISION_BY_ZERO模式影响insert或update时出现除以0或者以0取模(mod(N,0))，根据是否设置为严格模式有以下几种情况：

• 当不设置ERROR_FOR_DIVISION_BY_ZERO，除以0或以0取模的结果为null，不产生告警；
• 当设置ERROR_FOR_DIVISION_BY_ZERO，除以0或以0取模的结果为null，并产生告警；
• 当设置ERROR_FOR_DIVISION_BY_ZERO和严格模式，除以0或以0取模会产生报错，除非insert和update配合ignore适用；
• 对于select查询，除以0或以0取模在任何情况下都会返回null，如果设置ERROR_FOR_DIVISION_BY_ZERO，则会产生warnings；

ERROR_FOR_DIVISION_BY_ZERO并不包含在严格模式里，但这个选项即将被废弃，在未来的版本会合并到严格模式里。

1.3 HIGH_NOT_PRECEDENCE

NOT操作的顺序，比如NOT a BETWEEN b AND c被解释为NOT (a BETWEEN b AND c)。

mysql> set @@session.sql_mode='';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      0 |
+------------------------+

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

1.4 IGNORE_SPACE

允许函数名和()之间有空格，这会导致内置的函数为关键字。只是对于内置函数而言，对于用户定义的函数，总是允许和()之间有空格。

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

mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.10 sec)
1
2
3
4
5

当设置ignore_space后

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

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i INT)' at line 1

mysql> CREATE TABLE `count` (i INT);
Query OK, 0 rows affected (0.10 sec)
1
2
3
4
5
6
7
8

由于count是内置函数，创建表名为count时需要加标识符

1.5 NO_UNSIGNED_SUBTRACTION

当两个整数相减时，其中一个unsigned，会默认生成unsigned的结果。但是如果结果是负数，则会出错。

mysql> set @@session.sql_mode='';

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
1
2
3
4

如果设置了NO_UNSIGNED_SUBTRACTION，结果如下：

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

如果两个整数相减的结果用来更新一个unsigned列，默认情况下是该列数据类型的最大值，如果开启了NO_UNSIGNED_SUBTRACTION，则设置为0。但是在strict SQL mode下，列值不会变化，并且会报错。

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

mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE t1;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| c2    | bigint unsigned | NO   |     | 0       |       |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE t2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| c2    | bigint | NO   |     | 0       |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

1.6 ONLY_FULL_GROUP_BY

对于group by聚合操作，如果在select中出现的列没有在group by中出现，那么这种SQL是不合法的。

##创建表并插入数据
mysql> DROP TABLE IF EXISTS mytable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE mytable (
    -> id INT UNSIGNED NOT NULL PRIMARY KEY,
    -> a VARCHAR(10),
    -> b VARCHAR(10),
    -> c INT
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 'qrs', 1000),
    ->        (2, 'abc', 'tuv', 2000),
    ->        (3, 'def', 'qrs', 4000),
    ->        (4, 'def', 'tuv', 8000),
    ->        (5, 'abc', 'qrs', 16000),
    ->        (6, 'def', 'tuv', 32000);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

##设置SQL MODE为ONLY_FULL_GROUP_BY
mysql> SET sql_mode='ONLY_FULL_GROUP_BY';
mysql> select a,b,max(c) from mytable group by a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tango.mytable.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select a,b,max(c) from mytable group by a,b;
+------+------+--------+
| a    | b    | max(c) |
+------+------+--------+
| abc  | qrs  |  16000 |
| abc  | tuv  |   2000 |
| def  | qrs  |   4000 |
| def  | tuv  |  32000 |
+------+------+--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

1.7 PAD_CHAR_TO_FULL_LENGTH

默认情况下，列类型为char会裁剪掉，但是如果设置了PAD_CHAR_TO_FULL_LENGTH，CHAR类型的列会使用空Ox20进行填充。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

设置SQL Mode为PAD_CHAR_TO_FULL_LENGTH后

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

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

1.8 TIME_TRUNCATE_FRACTIONAL

当date、time或timestamp中带有飞秒值时根据列的精度是truncate还是rounding，默认是使用rounding，设置该SQL mode时候会使用truncate。

mysql> CREATE TABLE t2 (id INT, tval TIME(1));
Query OK, 0 rows affected (0.02 sec)
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 (id, tval) VALUES(1, 1.55);
Query OK, 1 row affected (0.01 sec)
mysql> SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 (id, tval) VALUES(2, 1.55);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id, tval FROM t2 ORDER BY id;
+------+------------+
| id   | tval       |
+------+------------+
|    1 | 00:00:01.6 |
|    2 | 00:00:01.5 |
+------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2、Strict SQL mode

Strict mode控制着MySQL在update/insert时候对于invalid或missing数据的处理。插入或更新的值invalid有多种原因，比如数据类型错误、值越界等；值缺失是因为向NOT NULL列插入数据的时候没有显示的指定DEFAULT语句。Strict mode也会影响到create table语句。而对于strict mode下的select语句，只会生成一个warning，不会报错。

如果没有生效strict mode，MySQL在插入invalid或缺失值的时候会自动调整，并产生warning信息。在Strict mode下，也可以使用INSERT IGNORE或UPDATE IGNORE产生同样的效果。
Strict mode在设置了STRICT_ALL_TABLES或STRICT_TRANS_TABLES生效，这两个模式有以下区别：

• 对于业务的表，在两种模式下当insert/update语句出现了invalid或missing values会报错，语句会中断并回滚
• 对于非业务的表，对于insert/update第一行数据的时候跟业务表有同样的行为，语句出错并回滚。但是对于多行记录，并且出错的不在第一行记录的时候，会有所不同
  • 对于STRICT_ALL_TABLES，mysql会报错并忽略余下所有的记录数，这样就会造成有部分成功了。为了避免这个问题，可以使用single-row语句。
  • 对于STRICT_TRANS_TABLES，mysql会把invalid值转换为最接近的valid值并插入；如果值缺失了，会插入默认的值。同时mysql会产生一个warning并继续处理下面的操作

2.1 IGNORE和Strict SQL mode对比

下表对比了当默认值是error还是warning时候IGNORE和Strict SQL mode的不同表现：

从以上可以看出，当同时设置strict SQL mode和IGNORE下，IGNORE优先级更高

2.1.1 IGNORE的影响

在mysql中有些语句支持IGNORE关键字，它会将某些类型的错误降级并产生warning来替代。对于multi-row语句，错误降级为warning可以继续处理接下去的语句，同时IGNORE也会跳过出错的rows。

如下表t3中的主键包含唯一性约束，当插入相同的数据时会提示重复键值。

mysql> CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY);
mysql> INSERT INTO t3 (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 't3.PRIMARY'
1
2
3

加入IGNORE参数后，重复值依然不会插入，但是会报一个warning，语句并没有出错中断

mysql> INSERT IGNORE INTO t3 (i) VALUES(1),(1);
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't3.PRIMARY' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

在MySQL中以下语句支持IGNORE关键字：CREATE TABLE … SELECT、DELETE、INSERT、LOAD DATA和UPDATE。IGNORE关键字会忽略以下错误：

ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED
1
2
3
4
5
6
7
8
9
10
11

2.1.2 Strict SQL Mode的影响

在Strict SQL mode下，会将特定的warning升级为error。比如在non-strict mode下，插入字符串到整数列会将值转为0，并产生warning。

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

mysql> INSERT INTO t3 (i) VALUES('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t3;
+---+
| i |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

如果在strict mode下，invalid value会被拒绝并且报错：

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

mysql> INSERT INTO t3 (i) VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1
1
2
3
4
5

Strict SQL mode在值invalid或缺失的时候会产生以下错误信息：

ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

以上是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, PIPES_AS_CONCAT, IGNORE_SPACE。

---

参考资料：

1. https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
2. https://blog.csdn.net/weixin_39004901/article/details/89378097
3. https://www.modb.pro/db/24613

---

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/119696848
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！