与其它数据库不同，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。
我们去验证下：
代码语言：javascript复制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值全列表：
代码语言：javascript复制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.我们通过一些例子，看看SQL Mode的部分值效果。
代码语言：javascript复制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。
墨天轮原文链接：https://www.modb.pro/db/24613（复制到浏览器查看）
关于作者
巩飞，云和恩墨应用架构产品部总经理，2002年工作至今，围绕数据库领域，搞过开发、架构、运维等，如今专注于产品；经历了两层架构时代关系型数据库技术的蓬勃发展，并在三层架构时代关系型数据库技术中砥砺前行，一直到现在互联网 时代数据库技术面临的诸多挑战。作为数据领域的老兵，很高兴能继续奋战在一线，和大家一起学习成长，乐在其中；擅长场景化的SQL质控解决方案、Oracle数据库、TimesTen、GoldenGate等。
END
