技术分享 | 深入理解 MySQL 中的 SQL_MODE

爱可生开源社区

发布于 2024-09-14 10:57:40

23800

代码可运行

文章被收录于专栏：爱可生开源社区爱可生开源社区

运行总次数：0

代码可运行

作者：许祥，爱可生 MySQL DBA 团队成员，负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 2600 字，预计阅读需要 8 分钟。

1什么是 SQL_MODE？

SQL_MODE 是 MySQL 数据库中的一个系统变量，用于控制 MySQL 如何处理 SQL 语句和数据校验。它可以看作是一组约束和规范，确保数据的准确性、完整性和一致性。例如，可以控制如何处理无效日期、是否允许插入不完整的记录、是否区分大小写等。

在 MySQL 中，通过设置 SQL_MODE 可以解决下面几类问题：

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

通过阅读本文，你将收获以下知识点：

MySQL 不同版本的默认 SQL_MODE 值
常用的 SQL_MODE 设置及其含义
SQL_MODE 在数据迁移中的关键作用

2SQL_MODE 有哪些值？

MySQL 5.7 的默认值和描述

ONLY_FULL_GROUP_BY：在 GROUP BY 子句中没有出现的列，若出现在 SELECT 列表、HAVING 条件、ORDER BY 条件中时会被拒绝。
STRICT_TRANS_TABLES：非法日期，超过字段长度的值插入时，直接报错，拒绝执行。例如，如果向一个整数列插入超出范围的值，将触发错误。
NO_ZERO_DATE：针对日期 '0000-00-00'，执行逻辑如下：如果 SQL_MODE 中包含 STRICT TRANS TABLES，则日期被拒绝写入，但可以通过加 IGNORE 关键字写入 '0000-00-00'，有警告。
1. disable：可以正常插入，没有警告。
2. enable：可以正常插入，有警告。
NO_ZERO_IN_DATE：日期中针对月份和日期部分，如果为0，比如 '2024-00-00'，有不同的执行逻辑：如果 SQL_MODE 中包含STRICT TRANS TABLES 则日期被拒绝写入，但可以通过加 IGNORE 关键字写入 '0000-00-00'。
1. disable：可以正常插入，实际插入值还是 '2024-00-00' 没有警告。
2. enable：可以正常插入，有警告。
ERROR_FOR_DIVISION_BY_ZERO：除数为 0（包括 MOD(N,0)），执行逻辑如下：如果 SQL_MODE 中包含 STRICTTRANSTABLES，则数据被拒绝写入，但可以通过加 IGNORE 关键字写入 NULL，有警告。
1. disable：插入 NULL，没有警告。
2. enable：插入 NULL，有警告。
NO_AUTO_CREATE_USER：防止使用不带密码子句的 GRANT 语句来创建一个用户。
NO_ENGINE_SUBSTITUTION：执行 CREATE TABLE 或者 ALTER TABLE 语句时，如果指定的存储引擎不可用，MySQL 会抛出错误，而不是使用默认存储引擎。
1. disable：CREATE TABLE 会自动替换后执行，ALTER TABLE 不会执行，两个命令都有警告。
2. enable：两个命令直接报错。

MySQL 8.0 的默认值和描述

STRICT_TRANS_TABLES：同 MySQL 5.7，确保插入或更新的数据严格符合表的定义。
NO_ZERO_DATE：同 MySQL 5.7，禁止日期字段为 '0000-00-00'，要求输入有效的日期。
NO_ZERO_IN_DATE：同 MySQL 5.7，禁止日期或日期时间字段中的月份或日部分为零。
ERROR_FOR_DIVISION_BY_ZERO：同 MySQL 5.7，当除以零时，MySQL 将抛出错误，而不是返回 NULL。
NO_AUTO_CREATE_USER：同 MySQL 5.7，禁止通过 GRANT 语句自动创建用户。
NO_ENGINE_SUBSTITUTION：同 MySQL 5.7，确保指定的存储引擎必须存在，不会自动替换。

查询 SQL_MODE

-- MySQL 5.7
mysql> 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.00 sec)

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

3SQL_MODE 常见设置模式

SQL_MODE 的常见设置模式是一组可以选择的选项，这些选项可以根据需要组合使用，以改变数据库的行为。以下是一些常见的模式及其含义：

ANSI 模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报 WARNING 警告。
STRICT_TRANS_TABLES 模式：严格模式，进行数据的严格校验，错误数据不能插入，报 ERROR 错误。只对支持事务的表有效。
STRICT_ALL_TABLES 模式：严格模式，进行数据的严格校验，错误数据不能插入，报 ERROR 错误。对所有表都有效。
TRADITIONAL 模式：严格模式，当向 MySQL 数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报 ERROR 错误。用于事务时，会进行事务的回滚。

在下面的例子中，观察一下非法日期“2007-04-31”（因为 4 月没有 31 日）在不同 SQL_MODE 下能否正确插入。


mysql> 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)

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

mysql> 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)

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

mysql> insert into t_sql_mode_ansi values('2007-04-31');
Query OK, 1 row affected, 1 warning (0.00 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, 1 warning (0.00 sec)

 mysql> 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> 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 模式下，会直接提示日期非法，拒绝插入。STRICT_TRANS_TABLES 模式和 STRICT_ALL_TABLES 模式同样如此。

4SQL_MODE 在迁移中的用法

当从 MySQL 迁移到其他异构数据库时，SQL_MODE 的配置和调整在数据迁移过程中起着关键作用。 由于不同数据库系统在 SQL 标准、数据类型处理、错误处理等方面存在差异，正确使用 SQL_MODE 可以帮助确保迁移过程中数据的完整性和一致性。

SQL_MODE 是 MySQL 的一个配置选项，控制着 SQL 语句的解析和执行方式。

例如，SQL_MODE 可以影响 NULL 值的处理、日期格式的验证、GROUP BY 的严格性等。当迁移数据到其他数据库时，目标数据库可能不支持或以不同方式处理这些规则，因此在迁移前需要仔细配置和测试 SQL_MODE。

1. SQL_MODE 在迁移前的调整

在将 MySQL 数据库迁移到其他数据库之前，可以考虑以下步骤来调整 SQL_MODE，以减少迁移时可能出现的兼容性问题：

禁用严格模式：在迁移前，禁用 MySQL 中的严格模式（如 STRICT_TRANS_TABLES）可以帮助识别哪些数据在当前配置下可能会在目标数据库中引发问题。通过禁用严格模式，可以提前发现并处理不兼容的数据。

SET GLOBAL SQL_MODE ='';

禁用 ONLY_FULL_GROUP_BY：MySQL 的 ONLY_FULL_GROUP_BY 模式要求所有非聚合列必须在 GROUP BY 子句中，这在其他数据库中可能不是必需的。禁用该模式可以确保 SQL 查询在目标数据库中能正确执行。
启用宽松模式：通过设置较为宽松的 SQL_MODE（如禁用 NO_ZERO_DATE、NO_ZERO_IN_DATE），可以让 MySQL 接受一些可能在目标数据库中允许的数据格式，并确保这些数据能被顺利迁移。

2. 在迁移过程中处理 SQL_MODE 相关问题

日期和时间处理：一些数据库系统对日期和时间有更严格的要求。例如，0000-00-00 这样的日期在 MySQL 中可能是合法的，但在其他数据库中会引发错误。在迁移前，需要确保数据格式符合目标数据库的要求，或者通过 SQL 脚本清理这些数据。
空字符串与 NULL 的处理：在 MySQL 中，空字符串和 NULL 可能在一些情况下被视为相等，而在其他数据库中并非如此。迁移前，应该明确这些字段的逻辑，并在必要时进行转换。
标识符大小写：MySQL 对标识符的大小写敏感性可能与目标数据库不同。在迁移前，使用 SQL_MODE 的 ANSI_QUOTES 选项可以确保标识符的引用方式符合 SQL 标准，并减少在目标数据库中的兼容性问题。

3. 迁移后的兼容性测试

完成数据迁移后，需要在目标数据库中进行全面的兼容性测试，以确保迁移后的数据和应用程序能正常运行。重点测试的领域包括：

查询结果的准确性：检查涉及 GROUP BY、聚合函数、日期处理等 SQL 查询是否在目标数据库中返回预期结果
数据完整性：确保迁移后的数据没有丢失、截断或被错误转换。
性能：有些 SQL_MODE 设置可能影响查询性能，迁移后需要在目标数据库中优化相关查询。

4. 小结

在从 MySQL 迁移到其他数据库时，合理调整 SQL_MODE 可以显著减少迁移过程中的兼容性问题。通过禁用严格模式、调整日期和空值处理、确保标识符的一致性，以及进行全面的测试，可以确保迁移后的数据和应用程序在新的数据库环境中稳定运行。

5. 迁移过程中需要注意

在数据迁移过程中，可以设置 SQL_MODE 为 NO_TABLE_OPTIONS 模式。这样将去掉 SHOW CREATE TABLE 中的 ENGINE 关键字，获得通用的建表脚本。

测试示例如下:

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `ename` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `hiredate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
mysql>
mysql> set session sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>
mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `ename` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `hiredate` date DEFAULT NULL
)
1 row in set (0.00 sec)

5总结

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

本文关键字：#MySQL# #SQL_MODE# #数据迁移#

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

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

sql