MySQL中的SQL Mode及其作用

数据和云

发布于 2021-07-30 15:34:53

1.9K00

代码可运行

文章被收录于专栏：数据和云数据和云

运行总次数：0

代码可运行

与其它数据库不同，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值全列表：

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的部分值效果。

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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-07-13，如有侵权请联系 cloudcommunity@tencent.com 删除

数据分析