mysql的sqlmode mysql的sqlmodes的作用是什么 ruhe

转载

mob6454cc63af5e 2023-08-26 09:12:52

文章标签 mysql的sqlmode SQL Mode mysql 数据库 SQL 文章分类 MySQL 数据库

SQL Mode 及相关问题

与其他数据库不同，MySQL 可以运行在不同的 SQL Mode （SQL 模式）下。SQL Mode 定义了MySQL 应用支持的SQL语法、数据校验等，这样可以更容易地在不同地环境中使用MySQL。

MySQL SQL Mode 简介

在MySQL中，SQL Mode 常用来解决以下几类问题：

通过SQL Mode，可以完成不同严格程度的数据校验，有效地保证数据准确性。
通过设置 SQL Mode 为 ANSI 模式，来保证大多数SQL符合标准地SQL语法，这样应用在不同的数据库之间进行迁移时，则不需要对业务SQL进行较大地修改。
在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL上的数据更方便地迁移到目标数据库中，

在MySQL5.7中，SQL Mode 有了较大的变化，查询默认地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 (不同的小版本可能略有差异)。这些SQL Mode 含义如下：

sql_mode 的值	描述
ONLY_FULL_GROUP_BY	在 group by 子句中没有出现的列，出现在select 列表、having 条件，order by 条件中时会被拒绝。
STRICT_TRANS_TABLES	非法日期，超过字段长度的值插入时，直接报错，拒绝执行
NO_ZERO_IN_DATE	日期中针对月份和日期部分，如果为0，比如‘2018-00-00’，有不同的执行逻辑 1. disable：可以正常插入，实际插入值还是‘2018-00-00’ 没有警告 2. enable：可以正常插入，有警告；如果mode中包含 STRICT_TRANS_TABLES,则日期被拒绝写入，但可以通过ignore 关键字写入‘0000-00-00’；
NO_ZERO_DATE	针对日期‘0000-00-00’，执行逻辑如下： 1. disable：可以正常插入，没有警告 2. enable：可以正常插入，有警告；如果mode中包含STRICT_TRANS_TABLES,则日期拒绝被写入，但可以通过关键字 ignore 写入 ‘0000-00-00’，有警告。
ERROR_FOR_DIVISION_BY_ZERO	除数为0（包括MOD(N,0)）,执行逻辑如下： 1. disable：插入NULL，没有警告 2. enable：插入NULL，有警告；如果mode中包含STRICT_TRANS_TABLES,则数据被拒绝写入，但可以通过加关键字 ignore 写入NULL，有警告。
NO_AUTO_CREATE_USER	防止使用不带密码子句的grant 语句来创建一个用户。
NO_ENGINE_SUBSTITUTION	执行create table 或者alter table语句时，如果指定了不支持（包括 disable 或未编译）的存储引擎，是否自动替换为默认的存储引擎 1. disable：create table 会自动替换后执行，alter table 不会执行，两个命令都有警告 2. enable：两个命令直接报错

相比之前的版本，MySQL5.7.5 之后的版本最大的区别是在 SQL Mode的默认设置中，增加了严格的事物表模式（STRICT_TRANS_TABLES）,在这种模式下不允许插入字段类型不一致的值，不允许插入超过字段长度的值，这在绝大多数场景下都更加合理。如果不设置STRICT_TRANS_TABLES，那么上述操作会被允许，只是在插入后，MySQL会返回一个warning，从而导致表中写入异常数据。

NO_ZERO_DATE、NO_ZERO_IN_DATE、ERROR_FOR_DIVISION_BY_ZERO这几种SQL Mode 很少单独使用，通常和STRICT_TRANS-tables 一起使用，官网宣称这几种SQL Mode 将来可能会合并。下面来看看具体的例子。

（1）查看默认 SQL Mode 的命令：

mysql> 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_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（2）查看测试表 t 的表结构的命令如下：

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

（3）首先取消SQL Mode 的严格模式

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

（4）在表中插入一条记录，其中 name 故意超出了实际的定义值 varchar(10):

mysql> insert into t values (1, 'beijing@126.com');
Query OK, 1 row affected, 1 warning (0.01 sec)

（5）可以发现，记录可以插入，但是显示了一个warning，查看warning 内容:

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

（6）warnings 提示对插入的 name 值进行了截断，从表 t 中查看实际插入值：

mysql> select * from t;
+------+------------+
| id   | name       |
+------+------------+
|    1 | beijing@12 |
+------+------------+
1 row in set (0.00 sec)

果然，记录虽然插入进去，但是只截取了前10 字符。

（7）接下来设置SQL Mode 为 STRICT_TRANS_TABLES（严格的事务表达模式）：

mysql> set session sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

（8）再次尝试插入上面的测试记录：

mysql> insert into t values (1, 'beijing@126.com');
ERROR 1406 (22001): Data too long for column 'name' at row 1

结果发现，这次记录没有插入成功，给出了一个ERROR，而不仅仅是warning。

上面的例子中，给出了 sql_mode 的一种修改方法，及`SET [SESSION|GLOBAL] sql_mode=‘modes’ ，其中SESSION 选项表示只在本次连接中生效；而GLOBAL 选项表示在本次连接中并不生效，而对于新的连接则生效，这种方法在MySQL4.1 开始有效。另外，也可以通过使用 ‘–sql-mode=“modes”’ 选项，在MySQL启动的时候设置sql_mode。

SQL Mode 的常见功能

下面介绍一下SQL Mode 的常见功能。

（1）校验日期数据合法性，这是SQL Mode 的一项常见功能。

在下面的例子中，观察一下非法日期 ”2007-04-31“，在不同SQL Mode 下能否正常插入。

mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_sql_mode_ansi(d datetime);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t_sql_mode_ansi values ('2007-04-31');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t_sql_mode_ansi;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_sql_mode_ansi values ('2007-04-31');
ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd' at row 1

很显然，在ANSI模式下，非法日期可以插入，但是插入值却变为“0000-00-00 00:00:00”，并且系统给出了warning 信息；而TRADITIONAL模式，会直接提示日期非法，拒绝插入。

（2）启用 NO_BACKSLASH_ESCAPES 模式，使反斜线 “\” 成为普通字符。在导入数据时，如果数据中含有反斜线，那么 NO_BACKSLASH_ESCAPES 模式保证数据的正确性，是一个不错的选择。

一下示例说明了启用 NO_BACKSLASH_ESCAPES 模式前后对反斜线 “\” 插入的变化。

mysql> set session sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t_sql_mode_bs (context varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t_sql_mode_bs values ('\beijing');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_sql_mode_bs;
+---------+
| context |
+---------+
|eijing |
+---------+
1 row in set (0.00 sec)

mysql> insert into t_sql_mode_bs values ('\\beijing');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_sql_mode_bs;
+----------+
| context  |
+----------+
|eijing  |
| \beijing |
+----------+
2 rows in set (0.00 sec)

mysql> set session sql_mode='ANSI,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@SQL_MODE;
+-----------------------------------------------------------------------------------------------------+
| @@SQL_MODE                                                                                          |
+-----------------------------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,NO_BACKSLASH_ESCAPES |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO T_SQL_MODE_BS VALUES ('\\BEIJING');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM T_SQL_MODE_BS;
+-----------+
| context   |
+-----------+
|eijing   |
| \beijing  |
| \\BEIJING |
+-----------+
3 rows in set (0.00 sec)

通过上面的示例可以看到，当在 ANSI 模式中增加了NO_BACKSLASH_ESCAPES 模式后，反斜线变为了普通字符。如果导入的数据存在反斜线，可以设置此模式，保证导入数据的正确性。

（3）启用 PIPES_AS_CONCAT 模式。将 “||” 视为字符串连接操作符，在Oracle 等数据库中，“||” 被视为字符串的连接操作符，所以，在其他数据库中含有“||” 操作符的SQL 在 MySQL中将无法执行，为了解决这个问题，MySQL 提供了 PIPES_AS_CONCAT 模式。

下面通过示例来介绍一下PIPES_AS_CONCAT 模式的作用。

mysql> set session sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'beijing'||'2008';
+-------------------+
| 'beijing'||'2008' |
+-------------------+
| beijing2008       |
+-------------------+
1 row in set (0.00 sec)

通过上面的示例可以看到，ANSI 模式中包含了 PIPES_AS_CONCAT 模式，所以默认情况下，MySQL版本支持将 “||” 视为字符串连接操作符。

需要注意的是，在分区表和主从复制环境中，要谨慎修改SQL Mode。前者可能导致数据的写入逻辑发生变化，新的逻辑可能导致同一条数据在不同的SQL Mode 下写入不同的分区；如果主从服务器的SQL Mode 不同，后者会导致复制的数据在主从服务器上写入逻辑不同。两种情况都可能导致数据的混乱。

常见的 SQL Mode

熟悉并了解经常使用的SQL Mode 会帮助用户更好的使用它。下面总结了常用的SQL Mode 值及其说明。

sql_mode 的值	描述
ANSI	等同于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、ONLY_FULL_GROUP_BY 和 ANSI 组合模式，这种模式遇到异常时倾向于警告而不是立刻返回错误。
STRICT_TRANS_TABLES	STRICT_TRANS_TABLES 适用于事务表和非事务表，它是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告。MySQL5.7版本后添加到默认的SQL Mode中。
TRADITIONAL	TRADITIONAL 模式等同于 STRICT_TRANS_TABLES 、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER 和 NO_ENGINE_SUBSTITUTION 的组合模式，所以它也是严格模式，对于插入不正确的值会给出错误而不是警告。可以应用在事务表和非事务表，用在事务表时只要出现错误就会立即回滚。

可以发现，表格中第一列 SQL Mode 的值大都是一些原子模式的组合，类似于角色和权限的关系。这样当实际应用时，只需要设置一个模式组合，就可以设置很多的原子模式，大大简化了用户的工作。

其中TRADITIONAL 和 MySQL 5.7 的默认模式很相似，都属于严格模式。两者的主要区别在于 TRADITIONAL 包含了原子模式 STRICT_ALL_TABLES。STRICT_ALL_TABLES 和 STRICT_TRANS_TABLES 非常类似，对于事物表（比如InnoDB）的写入规则完全一致，但是对于非事务表有细微的差别，如下示例：

（1）创建非事务表 t1_myisam, 存储引擎设置为MyISAM。

mysql> create table t1_myisam (id int, name varchar(4)) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

（2）设置 SQL Mode 为 STRICT_TRANS_TABLES。

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

（3）分别写入下面的数据，发现第一条正常，第二条有警告，第三条被拒绝。

mysql> insert into t1_myisam values (1, 'z1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1_myisam values (2, 'z1'),(3,'z1'),(4,'z111111111111111111');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> insert into t1_myisam values (2, 'z1000000000000'),(3,'z1'),(4,'z111111111111111111');
ERROR 1406 (22001): Data too long for column 'name' at row 1

（4）查询表发现全部写入，但 name 为 “‘z111111111111111111’” 被截断为 “‘z111’”。

mysql> select * from t1_myisam;
+------+------+
| id   | name |
+------+------+
|    1 | z1   |
|    2 | z1   |
|    3 | z1   |
|    4 | z111 |
+------+------+
4 rows in set (0.00 sec)

（5）更改 SQL Mode 为 STRICT_ALL_TABLES。

mysql> set session sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

（6）写入刚才同样的数据，发现第一条正常，第二条和第三条被拒绝。

mysql> insert into t1_myisam values (1, 'z1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1_myisam values (2, 'z1'),(3,'z1'),(4,'z111111111111111111');
ERROR 1406 (22001): Data too long for column 'name' at row 3
mysql> insert into t1_myisam values (2, 'z1000000000000'),(3,'z1'),(4,'z111111111111111111');
ERROR 1406 (22001): Data too long for column 'name' at row 1

从上例可以看出，对于非事务表，如果一次写入多行记录，在 STRICT_TRANS_TABLES 模式下，只要多行中的第一条写入成功，那么后面的记录即使违反了严格模式的约束，也会自动转换为最接近的数据写入成功；而在 STRICT_ALL_TABLES 模式下则相反，只要多行记录中的任意一行违反严格模式的约束，本次的所有记录都会写入失败。显而易见，两者各有利弊，前者可能导致数据的异常，后者可能导致事务的不一致。避免这种问题的最好办法就是让数据逐条写入。

SQL Mode 在迁移中如何使用

如果MySQL 与其他异构数据库之间有数据迁移的需求，那么MySQL中提供的数据库组合模式就会对数据迁移过程有所帮助。

MySQL 提供了很多数据库的组合模式名称，例如 “ORACLE” ，“DB2” 等。这些模式组合是由很多小的 sql_mode 组合而成，在异构数据库之间迁移数据时可以尝试使用这些模式来导出适用于目标数据库格式的数据，这样就使得导出数据更容易导入目标数据库。

组合后的模式名称	组合模式中的各个sql_mode
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
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

在数据库迁移过程中，可以设置 SQL Mode 为 NO_TABLE_OPTIONS 模式，这样将去掉 show create table 中的 “engine” 关键字，获得通用的建表脚本。

测试如下：

mysql> show create table t1_myisam \G;
*************************** 1. row ***************************
       Table: t1_myisam
Create Table: CREATE TABLE `t1_myisam` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

#### 下面是将书里的结果照搬到这（自己试了，设置时报错）
mysql> set session sql_mode='NO_TABLE_OPTIONS';
mysql> show create table t1_myisam \G;
*************************** 1. row ***************************
       Table: t1_myisam
Create Table: CREATE TABLE `t1_myisam` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(4) DEFAULT NULL
) 
1 row in set (0.00 sec)

小结

本章介绍了 SQL Mode 的含义以及实际用途，重点讨论了以下内容。

SQL Mode 的 “严格模式” 为 MySQL 提供了很好的数据校验功能，保证了数据的准确性，TRADITIONAL 和 STRICT_TRANS_TABLES 是常用的两种严格模式，要注意两者的区别。
SQL Mode 的多种模式可以灵活组合，组合后的模式可以更好地满足应用程序地需求。尤其在数据迁移中，SQL Mode的使用更为重要。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。