MySQL的SQL_MODE：在不同模式下的DDL（Data Definition Language）操作与数据导入的兼容性与风险 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL的SQL_MODE：在不同模式下的DDL操作与数据导入的兼容性与风险

大家好，今天我们来深入探讨MySQL中一个非常重要的概念：SQL_MODE。 SQL_MODE控制着MySQL服务器如何处理SQL语句，特别是在DDL（Data Definition Language）操作和数据导入方面。不同的SQL_MODE设置会对语句的执行结果产生显著影响，理解并正确配置SQL_MODE对于保证数据库的兼容性、数据完整性和应用稳定性至关重要。

什么是SQL_MODE？

SQL_MODE是一组SQL模式的集合，每个模式定义了MySQL服务器在处理SQL语句时应遵循的特定规则。不同的模式可以控制诸如数据类型检查的严格程度、对非法值的处理方式、以及对特定SQL语法的支持等行为。

你可以通过以下SQL语句查看当前的SQL_MODE：

SELECT @@sql_mode;

或者，针对会话级别查询：

SELECT @@session.sql_mode;

要设置SQL_MODE，可以使用以下SQL语句：

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

或者，针对会话级别设置：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

需要注意的是，GLOBAL设置影响所有新连接到MySQL服务器的客户端，而SESSION设置仅影响当前会话。修改GLOBAL值通常需要SUPER权限。

常见的SQL_MODE值及其含义

以下是一些常用的SQL模式及其含义：

SQL模式	含义
`STRICT_TRANS_TABLES`	启用严格模式，对于事务性存储引擎，当插入或更新数据时发生错误，语句会回滚。对于非事务性存储引擎，语句会停止执行，但之前的更改不会回滚。
`STRICT_ALL_TABLES`	与`STRICT_TRANS_TABLES`类似，但对所有存储引擎都启用严格模式，包括非事务性存储引擎。
`NO_ZERO_IN_DATE`	禁止日期或日期时间部分为零，例如’2023-00-01’或’2023-01-00’。
`NO_ZERO_DATE`	禁止插入’0000-00-00’日期。
`ERROR_FOR_DIVISION_BY_ZERO`	在除数为零时产生错误，而不是返回`NULL`。
`NO_ENGINE_SUBSTITUTION`	如果请求的存储引擎不可用，则产生错误，而不是使用默认存储引擎。
`ONLY_FULL_GROUP_BY`	在`SELECT`语句中，如果使用了`GROUP BY`，则`SELECT`列表中的所有非聚合列必须出现在`GROUP BY`子句中。
`ANSI`	相当于`REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION, NO_DIR_IN_CREATE, POSTGRESQL, ORACLE, MSSQL`的组合（某些模式可能因MySQL版本而异）。
`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`的组合。
`ALLOW_INVALID_DATES`	允许诸如 ‘2023-02-30’ 之类的无效日期存在，仅检查月份范围 (1-12) 和日期范围 (1-31)。
`IGNORE_SPACE`	允许在函数名和括号之间存在空格。
`PIPES_AS_CONCAT`	将 `\|\|` 视为字符串连接运算符（类似于Oracle）。

SQL_MODE对DDL操作的影响

SQL_MODE会直接影响DDL语句的执行。例如，在严格模式下，如果尝试创建一个表，但指定的存储引擎不可用，MySQL会报错并阻止创建表。而在非严格模式下，MySQL可能会默默地使用默认存储引擎，这可能会导致意外的行为和性能问题。

示例1：存储引擎不可用

假设我们尝试创建一个使用InnoDB存储引擎的表，但InnoDB未启用或不可用。

在NO_ENGINE_SUBSTITUTION模式下：

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE test_table (id INT) ENGINE=InnoDB;

这将导致错误，提示无法找到指定的存储引擎。

在没有NO_ENGINE_SUBSTITUTION模式下（或使用空字符串''）：

SET SESSION sql_mode = '';
CREATE TABLE test_table (id INT) ENGINE=InnoDB;

MySQL可能会使用默认存储引擎（例如MyISAM），而不会报错。这可能会导致数据完整性和事务性方面的问题，因为MyISAM不支持事务。

示例2：严格模式下的数据类型检查

考虑以下表结构：

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

在严格模式 (STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES) 下，如果尝试插入一个超出VARCHAR(20)范围的字符串，MySQL会报错。

SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
INSERT INTO test_table (id, name) VALUES (1, 'This is a very long string that exceeds the length of the varchar field');

这将导致错误，因为字符串长度超过了20个字符。

在非严格模式下，MySQL可能会截断字符串，并将截断后的值插入到表中，而不会报错，这可能会导致数据丢失。

SQL_MODE对数据导入的影响

SQL_MODE对数据导入操作（例如使用LOAD DATA INFILE或INSERT语句）也有很大的影响。严格模式会强制执行更严格的数据验证，从而可以防止将无效或不一致的数据导入到数据库中。

示例1：无效日期

假设我们有一个包含日期数据的文件，其中包含一些无效日期，例如’2023-02-30’。

在NO_ZERO_IN_DATE或 NO_ZERO_DATE 或 STRICT_TRANS_TABLES模式下，尝试导入这些数据将会导致错误。

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';
LOAD DATA INFILE 'data.txt' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n';

如果data.txt包含诸如’2023-02-30’的记录，导入操作将会失败。

在允许无效日期的模式下 (ALLOW_INVALID_DATES 并没有开启 NO_ZERO_IN_DATE或 NO_ZERO_DATE 或 STRICT_TRANS_TABLES)，MySQL会尝试将这些无效日期插入到表中，可能会导致数据不一致。虽然 ALLOW_INVALID_DATES 允许插入无效日期，但它不会修复无效日期，仅仅是允许写入。

示例2：除数为零

如果尝试插入一条包含除数为零的计算结果的记录，ERROR_FOR_DIVISION_BY_ZERO模式会产生错误。

SET SESSION sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO';
INSERT INTO test_table (id, value) VALUES (1, 10 / 0);

这将导致错误。

如果没有启用ERROR_FOR_DIVISION_BY_ZERO模式，MySQL通常会返回NULL，而不会报错，这可能会掩盖潜在的错误。

数据类型兼容性

SQL_MODE中的REAL_AS_FLOAT 模式会影响 REAL 数据类型的处理。启用后，REAL 将被视为 FLOAT 类型。而禁用后，REAL 将被视为 DOUBLE 类型。这在数据迁移和兼容性方面需要注意。

ANSI模式的影响

ANSI 模式是一组模式的组合，旨在使MySQL的行为更符合ANSI SQL标准。它包含诸如 PIPES_AS_CONCAT 和 ANSI_QUOTES 等模式，这些模式会影响SQL语法的解析。

PIPES_AS_CONCAT: 将 || 视为字符串连接运算符，这与Oracle和PostgreSQL等数据库的行为一致。
ANSI_QUOTES: 允许使用双引号（") 来引用标识符（例如表名和列名），而不是单引号（'）。

使用 ANSI 模式可以提高MySQL与其他数据库的兼容性，但需要注意它可能会改变某些SQL语句的语义。

ONLY_FULL_GROUP_BY 的重要性

ONLY_FULL_GROUP_BY 模式是SQL_MODE中一个非常重要的选项，它强制要求在使用GROUP BY子句时，SELECT列表中所有非聚合列必须出现在GROUP BY子句中。这可以防止在GROUP BY查询中出现不确定的结果。

例如，考虑以下表结构：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

如果我们想按customer_id分组，并获取每个客户的总订单金额和最早的订单日期，以下查询在没有ONLY_FULL_GROUP_BY 模式时可能会执行成功：

SELECT customer_id, order_date, SUM(amount) FROM orders GROUP BY customer_id;

但是，由于order_date列没有出现在GROUP BY子句中，因此对于每个customer_id，order_date的值是不确定的。 MySQL可能会随机选择一个order_date值，这可能会导致不正确的结果。

启用ONLY_FULL_GROUP_BY 模式后，上述查询将会报错，提示order_date列必须出现在GROUP BY子句中。为了解决这个问题，我们需要将order_date列添加到GROUP BY子句中，或者使用聚合函数（例如MIN(order_date)或MAX(order_date)）来获取确定的值。

正确的查询如下：

SELECT customer_id, MIN(order_date), SUM(amount) FROM orders GROUP BY customer_id;

如何选择合适的SQL_MODE？

选择合适的SQL_MODE需要考虑以下因素：

数据库的版本: 不同的MySQL版本可能支持不同的SQL模式。
应用程序的需求: 应用程序是否需要严格的数据验证？是否需要与其他数据库的兼容性？
数据迁移: 如果在不同的MySQL服务器之间迁移数据，需要确保SQL_MODE设置一致，以避免数据丢失或损坏。
团队规范: 在团队开发中，应该制定统一的SQL_MODE规范，以确保所有开发人员使用相同的设置。

通常建议在生产环境中使用严格模式，例如STRICT_TRANS_TABLES和ONLY_FULL_GROUP_BY，以提高数据质量和应用程序的稳定性。在开发环境中，可以根据需要选择更宽松的模式，但应该注意潜在的风险。

最佳实践

明确指定SQL_MODE: 不要依赖MySQL服务器的默认SQL_MODE设置。应该在配置文件或连接字符串中明确指定SQL_MODE。
使用一致的SQL_MODE: 在所有环境（开发、测试、生产）中使用相同的SQL_MODE设置。
定期审查SQL_MODE: 随着应用程序的发展，可能需要调整SQL_MODE设置。应该定期审查SQL_MODE设置，并根据需要进行更改。
了解SQL_MODE的影响: 在更改SQL_MODE设置之前，应该充分了解其影响，并进行测试，以确保不会影响应用程序的正常运行。
使用TRADITIONAL或自定义组合: TRADITIONAL 模式是一个很好的起点，因为它包含了许多常用的严格模式。也可以根据需要自定义SQL_MODE设置。
记录SQL_MODE设置: 将SQL_MODE设置记录在文档中，以便团队成员了解和维护。

案例分析：数据迁移中的SQL_MODE问题

假设你正在将一个旧的MySQL数据库迁移到一个新的MySQL 8.0服务器。旧数据库的SQL_MODE设置比较宽松，允许插入无效日期和截断字符串。新的MySQL 8.0服务器默认启用了严格模式。

在迁移数据时，可能会遇到以下问题：

数据导入失败: 由于新的服务器启用了严格模式，因此尝试导入包含无效日期或超出长度的字符串的数据将会导致错误。
数据丢失: 如果使用INSERT IGNORE语句导入数据，无效数据将被忽略，导致数据丢失。
应用程序错误: 应用程序可能会依赖于旧数据库的宽松模式，例如，它可能会假设字符串总是被截断，而不是报错。在新的服务器上，应用程序可能会因为数据验证错误而崩溃。

为了解决这些问题，你需要：

审查数据: 在迁移数据之前，应该审查数据，并修复无效日期和超出长度的字符串。
调整SQL_MODE: 可以临时禁用新的服务器上的严格模式，以便导入数据。但是，在导入完成后，应该重新启用严格模式，并修改应用程序以适应严格模式。
修改应用程序: 应该修改应用程序，以便正确处理数据验证错误。例如，可以添加数据验证逻辑，以确保数据符合数据库的约束。

SQL_MODE的演进

随着MySQL版本的迭代，SQL_MODE也在不断演进。新的版本可能会引入新的SQL模式，或者修改现有SQL模式的行为。因此，在升级MySQL服务器时，应该仔细阅读官方文档，了解SQL_MODE的变化，并根据需要调整应用程序。

总结

SQL_MODE是MySQL中一个非常重要的配置选项，它控制着MySQL服务器如何处理SQL语句。正确配置SQL_MODE对于保证数据库的兼容性、数据完整性和应用稳定性至关重要。应该根据应用程序的需求和数据库的版本选择合适的SQL_MODE设置，并定期审查和调整SQL_MODE设置，以适应应用程序的发展。使用严格模式有助于提高数据质量和应用程序的稳定性。在数据迁移和升级过程中，务必关注SQL_MODE的变化，并采取相应的措施，以避免数据丢失或损坏。

MySQL的SQL_MODE：在不同模式下的DDL操作与数据导入的兼容性与风险

发表回复 取消回复

发表回复取消回复