百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

一文弄懂 MySQL 中的 SQL_MODE

wptr33 2025-02-11 13:45 35 浏览

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

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

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

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

什么是 SQL_MODE？

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

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

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

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

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

SQL_MODE 有哪些值？

MySQL 5.7 的默认值和描述

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

SQL_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 模式同样如此。

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

总结

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

更多技术文章，请访问：https://opensource.actionsky.com/

相关推荐

oracle数据导入导出_oracle数据导入导出工具

关于oracle的数据导入导出，这个功能的使用场景，一般是换服务环境，把原先的oracle数据导入到另外一台oracle数据库，或者导出备份使用。只不过oracle的导入导出命令不好记忆，稍稍有点复杂...

继续学习Python中的while true/break语句

上次讲到if语句的用法，大家在微信公众号问了小编很多问题，那么小编在这几种解决一下，1.else和elif是子模块，不能单独使用2.一个if语句中可以包括很多个elif语句，但结尾只能有一个else解...

python continue和break的区别_python中break语句和continue语句的区别

python中循环语句经常会使用continue和break，那么这2者的区别是？continue是跳出本次循环，进行下一次循环；break是跳出整个循环；例如：...

简单学Python——关键字6——break和continue

Python退出循环，有break语句和continue语句两种实现方式。break语句和continue语句的区别：break语句作用是终止循环。continue语句作用是跳出本轮循环，继续下一次循...

2-1，0基础学Python之 break退出循环、 continue继续循环 多重循

用for循环或者while循环时，如果要在循环体内直接退出循环，可以使用break语句。比如计算1至100的整数和，我们用while来实现：sum=0x=1whileTrue...

Python 中 break 和 continue 傻傻分不清

大家好啊，我是大田。今天分享一下break和continue在代码中的执行效果是什么，进一步区分出二者的区别。一、continue例1：当小明3岁时不打印年龄，其余年龄正常循环打印。可以看...

python中的流程控制语句:continue、break 和 return使用方法

Python中，continue、break和return是控制流程的关键语句，用于在循环或函数中提前退出或跳过某些操作。它们的用途和区别如下：1.continue（跳过当前循环的剩余部分，进...

L017:continue和break - 教程文案

continue和break在Python中，continue和break是用于控制循环（如for和while）执行流程的关键字，它们的作用如下：1.continue：跳过当前迭代，...

作为前端开发者，你都经历过怎样的面试?

已经裸辞1个月了，最近开始投简历找工作，遇到各种各样的面试，今天分享一下。其实在职的时候也做过面试官，面试官时，感觉自己问的问题很难区分候选人的能力，最好的办法就是看看候选人的github上的代码仓库...

面试被问 const 是否不可变?这样回答才显功底

作为前端开发者，我在学习ES6特性时，总被const的"善变"搞得一头雾水——为什么用const声明的数组还能push元素？为什么基本类型赋值就会报错？直到翻遍MDN文档、对着内存图反...

2023金九银十必看前端面试题!2w字精品!

导文2023金九银十必看前端面试题！金九银十黄金期来了想要跳槽的小伙伴快来看啊CSS1.请解释CSS的盒模型是什么，并描述其组成部分。答案：CSS的盒模型是用于布局和定位元素的概念。它由内容区域...

前端面试总结_前端面试题整理

记得当时大二的时候，看到实验室的学长学姐忙于各种春招，有些收获了大厂offer，有些还在苦苦面试，其实那时候的心里还蛮忐忑的，不知道自己大三的时候会是什么样的一个水平，所以从19年的寒假放完，大二下学...

由浅入深，66条JavaScript面试知识点(七)

作者：JakeZhang转发链接：https://juejin.im/post/5ef8377f6fb9a07e693a6061目录由浅入深，66条JavaScript面试知识点（一）由浅入深，66...

2024前端面试真题之—VUE篇_前端面试题vue2020及答案

添加图片注释，不超过140字（可选）1.vue的生命周期有哪些及每个生命周期做了什么？beforeCreate是newVue()之后触发的第一个钩子，在当前阶段data、methods、com...

今年最常见的前端面试题，你会做几道?

在面试或招聘前端开发人员时，期望、现实和需求之间总是存在着巨大差距。面试其实是一个交流想法的地方，挑战人们的思考方式，并客观地分析给定的问题。可以通过面试了解人们如何做出决策，了解一个人对技术和解决问...

一周热门
最近发表
标签列表
赣ICP备2024040476号-12本站文章收集于网络以及网友投稿，其版权归原创者所有，如有侵害到你的权益，或有不妥之处，请联系删除。