MySQL中的SQL Mode及其作用

作者：鞏飛 2021-07-13 18:31:22

數據庫 MySQL

與其它數據庫不同，MySQL可以運行在不同的SQL Mode下。SQL Mode定義MySQL應該支持什么樣的SQL語法，以及它應該執行什么樣的數據驗證檢查。

[[411048]]

本文轉載自微信公眾號「數據和云」，作者鞏飛。轉載本文請聯系數據和云公眾號。

與其它數據庫不同，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等。

責任編輯：武曉燕來源：數據和云