MySQL中的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 |
+---------------------+
1row 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等。
推荐阅读：
推荐下载：
点击下图查看更多 ↓
云和恩墨大讲堂 | 一个分享交流的地方
请备注：云和恩墨大讲堂