MySQL中的SQL Mode及其作用

网友投稿 585 2023-06-01

MySQL中的SQL Mode及其作用

MySQL中的SQL Mode及其作用

与其它数据库不同，MySQL可以运行在不同的SQL Mode下。SQL Mode定义MySQL应该支持什么样的SQL语法，以及它应该执行什么样的数据验证检查。

SQL Mode可以设置为一组应做检查的代号列表(模式值列表)，也可以设置为预定义好的组合代号。

MySQL 5.7中的默认SQL Mode包括以下值：

ONLY_FULL_GROUP_BYSTRICT_TRANS_TABLESNO_ZERO_IN_DATENO_ZERO_DATEERROR_FOR_DIVISION_BY_ZERONO_AUTO_CREATE_USERNO_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值全列表：

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

我们通过一些例子，看看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 ***',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 ***',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 ***’超过了字段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。***，等同于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质控解决方案、***数据库、TimesTen、GoldenGate等。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。

标签：关系型数据库 数据库
上一篇:MySQL8 主从复制踩坑指南
下一篇:MySQL面试常问：一条语句提交后，数据库都做了什么？
相关文章