MySQL中的SQL Mode及其作用
作者： 鞏飛
與其它數(shù)據(jù)庫不同，MySQL可以運(yùn)行在不同的SQL Mode下。SQL Mode定義MySQL應(yīng)該支持什么樣的SQL語法，以及它應(yīng)該執(zhí)行什么樣的數(shù)據(jù)驗(yàn)證檢查。
本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者鞏飛。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。
與其它數(shù)據(jù)庫不同，MySQL可以運(yùn)行在不同的SQL Mode下。SQL Mode定義MySQL應(yīng)該支持什么樣的SQL語法，以及它應(yīng)該執(zhí)行什么樣的數(shù)據(jù)驗(yàn)證檢查。
SQL Mode可以設(shè)置為一組應(yīng)做檢查的代號(hào)列表(模式值列表)，也可以設(shè)置為預(yù)定義好的組合代號(hào)。
MySQL 5.7中的默認(rèn)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常用來解決下面問題：
- 通過設(shè)置SQL Mode，可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗(yàn)，保障數(shù)據(jù)準(zhǔn)確性。
- 通過設(shè)置SQL Mode為ANSI，保證大多數(shù)SQL符合標(biāo)準(zhǔn)的SQL語法。
- 通過設(shè)置SQL Mode，可以使MySQL上的數(shù)據(jù)更方便的遷移到目標(biāo)數(shù)據(jù)。
SQL Mode最常用的值：
- ANSI，此模式更改語法和行為，使其更接近標(biāo)準(zhǔn)SQL。它等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI。
- STRICT_TRANS_TABLES，如果無法按給定的方式將值插入到事務(wù)表中，請(qǐng)中止該語句。對(duì)于非事務(wù)表，如果值出現(xiàn)在單行語句或多行語句的第一行中，則中止該語句。
- TRADITIONAL，使MySQL的行為像一個(gè)“傳統(tǒng)”的SQL數(shù)據(jù)庫系統(tǒng)。在向列中插入錯(cuò)誤值時(shí)，此模式“給出錯(cuò)誤而不是警告”。它等同于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。
我們?nèi)ヲ?yàn)證下：
- 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 |
- | 陳實(shí) | 31 | 9000.00 | 2019-07-01 | 10 |
- | Anastasia | 33 | 8200.00 | 2020-04-29 | 10 |
- +------------+------+---------+------------+--------+
- 6 rows in set (0.00 sec)
可以看到，當(dāng)SQL Mode包含STRICT_TRANS_TABLES時(shí)，要插入的值’Anastasia Cassandra’超過了字段ename的長度，報(bào)錯(cuò)無法插入。當(dāng)SQL Mode不包含STRICT_TRANS_TABLES時(shí)，可以插入，但插入時(shí)做了值截?cái)唷?/p>
為了方便使用，MySQL預(yù)定義好一批SQL Mode值組合代號(hào)。
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。
關(guān)于作者
鞏飛，云和恩墨應(yīng)用架構(gòu)產(chǎn)品部總經(jīng)理，2002年工作至今，圍繞數(shù)據(jù)庫領(lǐng)域，搞過開發(fā)、架構(gòu)、運(yùn)維等，如今專注于產(chǎn)品;經(jīng)歷了兩層架構(gòu)時(shí)代關(guān)系型數(shù)據(jù)庫技術(shù)的蓬勃發(fā)展，并在三層架構(gòu)時(shí)代關(guān)系型數(shù)據(jù)庫技術(shù)中砥礪前行，一直到現(xiàn)在互聯(lián)網(wǎng)+時(shí)代數(shù)據(jù)庫技術(shù)面臨的諸多挑戰(zhàn)。作為數(shù)據(jù)領(lǐng)域的老兵，很高興能繼續(xù)奮戰(zhàn)在一線，和大家一起學(xué)習(xí)成長，樂在其中;擅長場(chǎng)景化的SQL質(zhì)控解決方案、Oracle數(shù)據(jù)庫、TimesTen、GoldenGate等。
責(zé)任編輯：武曉燕
來源：
數(shù)據(jù)和云
