MySQL中的SQL Mode及其作用
作者： 鞏飛
與其它數(shù)據(jù)庫不同，MySQL可以運行在不同的SQL Mode下。SQL Mode定義MySQL應該支持什么樣的SQL語法，以及它應該執(zhí)行什么樣的數(shù)據(jù)驗證檢查。
本文轉載自微信公眾號「數(shù)據(jù)和云」，作者鞏飛。轉載本文請聯(lián)系數(shù)據(jù)和云公眾號。
與其它數(shù)據(jù)庫不同，MySQL可以運行在不同的SQL Mode下。SQL Mode定義MySQL應該支持什么樣的SQL語法，以及它應該執(zhí)行什么樣的數(shù)據(jù)驗證檢查。
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，可以完成不同嚴格程度的數(shù)據(jù)校驗，保障數(shù)據(jù)準確性。
- 通過設置SQL Mode為ANSI，保證大多數(shù)SQL符合標準的SQL語法。
- 通過設置SQL Mode，可以使MySQL上的數(shù)據(jù)更方便的遷移到目標數(shù)據(jù)。
SQL Mode最常用的值：
- ANSI，此模式更改語法和行為，使其更接近標準SQL。它等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI。
- STRICT_TRANS_TABLES，如果無法按給定的方式將值插入到事務表中，請中止該語句。對于非事務表，如果值出現(xiàn)在單行語句或多行語句的第一行中，則中止該語句。
- TRADITIONAL，使MySQL的行為像一個“傳統(tǒng)”的SQL數(shù)據(jù)庫系統(tǒng)。在向列中插入錯誤值時，此模式“給出錯誤而不是警告”。它等同于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。
關于作者
鞏飛，云和恩墨應用架構產(chǎn)品部總經(jīng)理，2002年工作至今，圍繞數(shù)據(jù)庫領域，搞過開發(fā)、架構、運維等，如今專注于產(chǎn)品;經(jīng)歷了兩層架構時代關系型數(shù)據(jù)庫技術的蓬勃發(fā)展，并在三層架構時代關系型數(shù)據(jù)庫技術中砥礪前行，一直到現(xiàn)在互聯(lián)網(wǎng)+時代數(shù)據(jù)庫技術面臨的諸多挑戰(zhàn)。作為數(shù)據(jù)領域的老兵，很高興能繼續(xù)奮戰(zhàn)在一線，和大家一起學習成長，樂在其中;擅長場景化的SQL質控解決方案、Oracle數(shù)據(jù)庫、TimesTen、GoldenGate等。
責任編輯：武曉燕
來源：
數(shù)據(jù)和云
