MySQL编程进阶之：`SQL_MODE`对编程的影响：如何确保代码在不同环境下的兼容性。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位程序猿、媛，大家好！我是老码农，今天咱们来聊聊MySQL编程里一个容易被忽略，但又非常重要的东西——SQL_MODE。这玩意儿就像地雷，平时风平浪静，一不小心踩上去，你的代码就炸给你看。

开场白：SQL_MODE是什么鬼？

简单来说，SQL_MODE就是MySQL服务器的“行为准则”，它定义了MySQL应该如何处理SQL语句。就像一个严格的老师，它会检查你的代码是否符合规范，一旦发现问题，轻则警告，重则直接报错。

SQL_MODE的“七十二变”

SQL_MODE不是一个简单的开关，而是一组开关的集合。每个开关控制着MySQL的某一个行为。你可以通过设置不同的SQL_MODE，让MySQL表现出不同的性格。

常用的SQL_MODE值包括：

SQL_MODE 值	描述
`ONLY_FULL_GROUP_BY`	要求`SELECT`语句中的`GROUP BY`子句必须包含所有非聚合列。这个模式防止了`GROUP BY`的隐式排序，并且确保结果集是明确的。
`STRICT_TRANS_TABLES`	开启严格模式，如果事务存储引擎（如InnoDB）的表遇到无效或缺失数据，则会回滚整个语句。对于非事务存储引擎（如MyISAM），行为取决于具体的错误，但通常会产生警告或错误。
`NO_ZERO_IN_DATE`	禁止日期中的月份或日部分为零。例如，’2023-00-01’或’2023-01-00’将被视为无效日期。
`NO_ZERO_DATE`	禁止使用’0000-00-00’作为有效日期。这个模式通常与`NO_ZERO_IN_DATE`一起使用，以确保日期数据的完整性。
`ERROR_FOR_DIVISION_BY_ZERO`	将除以零的操作视为错误，而不是返回`NULL`。这个模式有助于及早发现潜在的错误。
`NO_AUTO_CREATE_USER`	禁止`GRANT`语句自动创建用户。用户必须先通过`CREATE USER`语句创建，然后才能授予权限。
`ONLY_FULL_GROUP_BY`	这个模式强制你明确地在GROUP BY子句中包含所有未聚合的列。这有助于避免一些模糊和潜在的错误结果。当你使用GROUP BY时，SELECT语句中所有没有被聚合函数包裹的列（例如SUM(), AVG(), COUNT()等）都必须出现在GROUP BY子句中。
`NO_ENGINE_SUBSTITUTION`	如果请求的存储引擎不可用，则阻止自动替换为默认存储引擎。这可以帮助确保你的数据库使用你指定的存储引擎，并且你不会意外地使用不同的存储引擎。
`STRICT_ALL_TABLES`	启用所有表的严格SQL模式。当启用此模式时，MySQL会更加严格地执行数据验证和类型检查。如果插入或更新操作违反了表的定义，则会引发错误。
`STRICT_TRANS_TABLES`	对事务存储引擎启用严格SQL模式。与`STRICT_ALL_TABLES`类似，但仅适用于事务存储引擎（例如InnoDB）。
`NO_ZERO_DATE`	禁止使用`'0000-00-00'`作为有效的日期值。默认情况下，MySQL允许使用零日期，但启用此模式可以强制执行更严格的日期验证。
`NO_ZERO_IN_DATE`	禁止日期或月份部分为零的日期值。与`NO_ZERO_DATE`类似，但更具体地禁止零值出现在日期或月份字段中。
`ERROR_FOR_DIVISION_BY_ZERO`	当尝试除以零时，将引发错误而不是返回`NULL`。这可以帮助捕获潜在的算术错误。
`NO_AUTO_CREATE_USER`	阻止`GRANT`语句自动创建用户。在MySQL 8.0中已弃用，建议使用`CREATE USER`语句来创建用户。
`NO_ENGINE_SUBSTITUTION`	如果请求的存储引擎不可用，则阻止自动替换为默认存储引擎。这可以帮助确保你的数据库使用你指定的存储引擎，并且你不会意外地使用不同的存储引擎。
`PAD_CHAR_TO_FULL_LENGTH`	将CHAR列填充到其最大长度。默认情况下，MySQL不会填充CHAR列，但启用此模式可以强制填充。

你可以通过以下方式查看当前MySQL服务器的SQL_MODE：

SELECT @@sql_mode;

你可以通过以下方式设置SQL_MODE：

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'; -- 全局设置，影响所有新连接
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'; -- 会话设置，只影响当前连接

SQL_MODE对编程的影响：一颗老鼠屎坏了一锅粥

SQL_MODE对代码的影响是潜移默化的，它可能会导致你的代码在不同的环境下表现出不同的行为。想象一下，你的代码在开发环境跑得飞起，到了生产环境却各种报错，这很可能就是SQL_MODE在捣鬼。

下面我们通过几个例子来说明SQL_MODE的影响：

例子1：ONLY_FULL_GROUP_BY的坑

假设你有一个orders表，包含order_id, customer_id, order_date, amount等字段。你想要查询每个客户的订单总金额和最早的订单日期：

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

在SQL_MODE没有开启ONLY_FULL_GROUP_BY的情况下，这条SQL语句可以正常执行。但是，如果开启了ONLY_FULL_GROUP_BY，MySQL会报错，因为它要求SELECT语句中的所有非聚合列（这里是order_date）都必须出现在GROUP BY子句中。

要解决这个问题，你需要将order_date也添加到GROUP BY子句中，或者使用ANY_VALUE()函数：

-- 方案1：将order_date添加到GROUP BY子句中 (不推荐，因为结果可能不符合预期)
SELECT customer_id, SUM(amount), MIN(order_date)
FROM orders
GROUP BY customer_id, order_date;

-- 方案2：使用ANY_VALUE()函数 (推荐)
SELECT customer_id, SUM(amount), MIN(order_date)
FROM orders
GROUP BY customer_id;

例子2：STRICT_TRANS_TABLES的威力

假设你有一个users表，其中username字段的类型是VARCHAR(20)，并且NOT NULL。如果你尝试插入一个超过20个字符的username，或者插入一个NULL值，STRICT_TRANS_TABLES会阻止你这么做。

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL
);

-- 尝试插入超长username
INSERT INTO users (username) VALUES ('ThisIsAVeryLongUsernameThatExceeds20Characters'); -- 如果开启了STRICT_TRANS_TABLES，会报错

-- 尝试插入NULL username
INSERT INTO users (username) VALUES (NULL); -- 如果开启了STRICT_TRANS_TABLES，会报错

如果没有开启STRICT_TRANS_TABLES，MySQL可能会截断超长的username，或者将NULL值替换为默认值（如果定义了默认值）。但是，开启了STRICT_TRANS_TABLES，MySQL会立即报错，防止脏数据进入数据库。

例子3：NO_ZERO_IN_DATE和NO_ZERO_DATE的较真

假设你尝试插入一个日期值为'2023-00-01'或者'0000-00-00'，如果没有开启NO_ZERO_IN_DATE和NO_ZERO_DATE，MySQL可能会接受这些日期值。但是，开启了这两个SQL_MODE，MySQL会报错，因为它认为这些日期值是无效的。

-- 尝试插入包含0的日期
INSERT INTO orders (order_date) VALUES ('2023-00-01'); -- 如果开启了NO_ZERO_IN_DATE，会报错
INSERT INTO orders (order_date) VALUES ('0000-00-00'); -- 如果开启了NO_ZERO_DATE，会报错

确保代码在不同环境下兼容性的葵花宝典

既然SQL_MODE这么重要，那么我们应该如何确保代码在不同环境下都能正常运行呢？

了解不同环境的SQL_MODE

在开发、测试和生产环境，SQL_MODE的设置可能不同。你需要了解每个环境的SQL_MODE，才能避免出现问题。

你可以通过以下方式查询当前环境的SQL_MODE：
```
SELECT @@sql_mode;
```

在代码中显式地设置SQL_MODE

为了确保代码在不同的环境下表现一致，你可以在代码中显式地设置SQL_MODE。例如，在连接数据库之后，执行以下SQL语句：

<?php
$conn = new PDO("mysql:host=localhost;dbname=your_database", "username", "password");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 设置SQL_MODE
$sql = "SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'";
$conn->exec($sql);

// 你的代码
?>

这样，无论当前环境的SQL_MODE是什么，你的代码都会按照你指定的SQL_MODE来运行。

编写符合标准SQL的代码

尽量编写符合标准SQL的代码，避免使用MySQL的扩展特性。这样可以提高代码的移植性，减少SQL_MODE带来的影响。
使用ORM框架

ORM框架（如Hibernate、MyBatis、Doctrine等）可以帮助你屏蔽底层数据库的差异，简化数据库操作。ORM框架通常会对SQL_MODE进行处理，确保代码在不同的数据库环境下都能正常运行。
进行充分的测试

在发布代码之前，务必进行充分的测试。在不同的SQL_MODE下测试你的代码，确保它能够正常运行。

最佳实践：推荐的SQL_MODE

为了提高代码的健壮性和可维护性，我建议你开启以下SQL_MODE：

STRICT_TRANS_TABLES：开启严格模式，防止脏数据进入数据库。
NO_ENGINE_SUBSTITUTION：阻止自动替换存储引擎，确保数据库使用你指定的存储引擎。
ONLY_FULL_GROUP_BY：要求GROUP BY子句必须包含所有非聚合列，避免GROUP BY的隐式排序。
NO_ZERO_IN_DATE 和 NO_ZERO_DATE: 避免无效日期

你可以通过以下方式设置SQL_MODE：

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE';
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE';

总结：SQL_MODE，防患于未然

SQL_MODE是MySQL服务器的一个重要配置，它可以影响你的代码的行为。为了确保代码在不同的环境下都能正常运行，你需要了解不同环境的SQL_MODE，在代码中显式地设置SQL_MODE，编写符合标准SQL的代码，使用ORM框架，进行充分的测试。开启推荐的SQL_MODE，可以提高代码的健壮性和可维护性。

希望今天的讲座对你有所帮助。记住，SQL_MODE就像一个默默无闻的守卫，它守护着你的数据库，防止出现各种意想不到的问题。掌握它，你就能写出更加健壮、可靠的MySQL代码。

补充说明

在MySQL 5.7及更高版本中，ONLY_FULL_GROUP_BY模式默认是开启的。
在MySQL 8.0中，NO_AUTO_CREATE_USER模式已经被移除。
SQL_MODE的设置会影响数据库的性能。在开启严格模式的同时，也要注意性能的优化。

最后的提醒

不要害怕SQL_MODE，它是你的朋友。花点时间了解它，掌握它，你就能写出更加优秀的MySQL代码。

好了，今天的讲座就到这里。谢谢大家！

发表回复 取消回复

发表回复取消回复