MySQL中的SQL Mode及其作用-mysql sql

本文转载自微信公众号「数据和云」，作者巩飞。转载本文请联系数据和云公众号。

与其它数据库不同，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值	说明
ALLOW_INVALID_DATES	Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.
ANSI_QUOTES	Treat " as an identifier quote character (like the `quote character) and not as a string quote character.You can still use` to quote identifiers with this mode enabled.
ERROR_FOR_DIVISION_BY_ZERO	The ERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includes MOD(N,0).ERROR_FOR_DIVISION_BY_ZERO is deprecated.
HIGH_NOT_PRECEDENCE	The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.
IGNORE_SPACE	Permit spaces between a function name and the ( character.
NO_AUTO_CREATE_USER	Prevent the GRANT statement from automatically creating new user accounts if it would otherwise do so, unless authentication information is specified.
NO_AUTO_VALUE_ON_ZERO	NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.
NO_BACKSLASH_ESCAPES	Disable the use of the backslash character () as an escape character within strings and identifiers. With this mode enabled, backslash becomes an ordinary character like any other.
NO_DIR_IN_CREATE	When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives.
NO_ENGINE_SUBSTITUTION	Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.
NO_FIELD_OPTIONS	Do not print MySQL-specific column options in the output of SHOW CREATE TABLE.
NO_KEY_OPTIONS	Do not print MySQL-specific index options in the output of SHOW CREATE TABLE.
NO_TABLE_OPTIONS	Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE.
NO_UNSIGNED_SUBTRACTION	Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result or negative.
NO_ZERO_DATE	The NO_ZERO_DATE mode affects whether the server permits ‘0000-00-00’ as a valid date.NO_ZERO_DATE is deprecated.
NO_ZERO_IN_DATE	The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0.
NLY_FULL_GROUP_BY	Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
PAD_CHAR_TO_FULL_LENGTH	By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.
PIPES_AS_CONCAT	Treat
REAL_AS_FLOAT	Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.
STRICT_ALL_TABLES	Enable strict SQL mode for all storage engines. Invalid data values are rejected.
STRICT_TRANS_TABLES	Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines.

我们通过一些例子，看看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。

关于作者

巩飞，云和恩墨应用架构产品部总经理，2002年工作至今，围绕数据库领域，搞过开发、架构、运维等，如今专注于产品;经历了两层架构时代关系型数据库技术的蓬勃发展，并在三层架构时代关系型数据库技术中砥砺前行，一直到现在互联网+时代数据库技术面临的诸多挑战。作为数据领域的老兵，很高兴能继续奋战在一线，和大家一起学习成长，乐在其中;擅长场景化的SQL质控解决方案、Oracle数据库、TimesTen、GoldenGate等。