MySQL中的SQL Mode及其作用

与其它数据库不同，MySQL可以运行在不同的SQL Mode下。SQL Mode定义MySQL应该支持什么样的SQL语法，以及它应该执行什么样的数据验证检查。

SQL Mode可以设置为一组应做检查的代号列表（模式值列表），也可以设置为预定义好的组合代号。

MySQL 5.7中的默认SQL Mode包括以下值：

• 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。

root@database-one 22:48:  [(none)]> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | 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.05 sec)

root@database-one 22:48:  [(none)]> 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在MySQL中，SQL Mode常用来解决下面问题：

• 通过设置SQL Mode，可以完成不同严格程度的数据校验，保障数据准确性。
• 通过设置SQL Mode为ANSI，保证大多数SQL符合标准的SQL语法。
• 通过设置SQL Mode，可以使MySQL上的数据更方便的迁移到目标数据。

SQL Mode最常用的值：

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

我们去验证下：

root@database-one 21:19:  [(none)]> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

root@database-one 21:20:  [(none)]> set session sql_mode='ANSI';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@database-one 21:20:  [(none)]> select @@session.sql_mode;
+--------------------------------------------------------------------------------+|
@@session.sql_mode                                                             |
+--------------------------------------------------------------------------------+
| 
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@database-one 21:20:  [(none)]> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@database-one 21:24:  [(none)]> select @@session.sql_mode;
+---------------------+|
@@session.sql_mode  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1
 row in set (0.00 sec)
root@database-one 21:24:  [(none)]> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@database-one 21:25:  [(none)]> select @@session.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL 5.7中支持的SQL Mode值全列表：

我们通过一些例子，看看SQL Mode的部分值效果。

root@database-one 22:38:  [(none)]> use gftest;
Database changed
root@database-one 22:39:  [gftest]> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

root@database-one 22:39:  [gftest]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  | MUL | NULL    |       |
| age      | int(11)       | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

root@database-one 22:39:  [gftest]> insert into emp values('Anastasia Cassandra',33,8200,now(),10);
ERROR 1406 (22001): Data too long for column 'ename' at row 1
root@database-one 22:39:  [gftest]> set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@database-one 22:40:  [gftest]> insert into emp values('Anastasia Cassandra',33,8200,now(),10);
Query OK, 1 row affected, 2 warnings (0.00 sec)

root@database-one 22:40:  [gftest]> select * from emp;
+------------+------+---------+------------+--------+
| ename      | age  | sal     | hiredate   | deptno |
+------------+------+---------+------------+--------+
| 郭军       |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰       |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳       |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽       |   26 | 7200.00 | 2018-07-06 |     30 |
| 陈实       |   31 | 9000.00 | 2019-07-01 |     10 |
| Anastasia  |   33 | 8200.00 | 2020-04-29 |     10 |
+------------+------+---------+------------+--------+
6 rows in set (0.00 sec)

可以看到，当SQL Mode包含STRICT_TRANS_TABLES时，要插入的值’Anastasia Cassandra’超过了字段ename的长度，报错无法插入。当SQL Mode不包含STRICT_TRANS_TABLES时，可以插入，但插入时做了值截断。

为了方便使用，MySQL预定义好一批SQL Mode值组合代号。

MySQL 5.7的清单如下：

• ANSI，等同于REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY。
• DB2，等同于PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS。
• MAXDB，等同于PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER。
• MSSQL，等同于PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS。
• MYSQL323，等同于MYSQL323, HIGH_NOT_PRECEDENCE。
• MYSQL40，等同于MYSQL40, HIGH_NOT_PRECEDENCE。
• ORACLE，等同于PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER。
• POSTGRESQL，等同于PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS。
• TRADITIONAL，等同于STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION。