MySQL 关于 sql_mode 解析与设置讲解

在讲解 sql_mode 之前，我们需要先了解什么是 SQL，以及 MySQL 又是什么。

SQL 是什么

SQL 是指结构化查询语言，在关系数据库管理系统中，被广泛用于数据库的管理与操作。常用的 SQL 语法有：SELECT、INSERT、UPDATE、DELETE，以及创建表、索引等相关的语法。

在我们使用 SQL 进行数据操作时，往往需要根据需求进行定制化的操作。而 sql_mode 则是 MySQL 数据库提供的用于定制化的工具。

MySQL 与 sql_mode

MySQL 是目前较为流行的开源数据库管理系统，支持存储、管理和检索各种数据，极大地方便了开发者在数据管理方面的工作。

而 sql_mode 则是 MySQL 中用于控制 SQL 语句的执行方式和行为的组合变量。通常，我们可以通过设置 sql_mode 来控制 MySQL 在执行 SQL 操作时的三个主要特性：

• 数据检查（data values checking）
• 数据处理（data handling）
• 数据存储（data storage）

sql_mode 的设置

MySQL 提供了多种方式来设置 sql_mode，分别为：

• 在启动 MySQL 处设置（适用于全局设置）
• 对于特定的客户端对象或会话（适用于个别设置）

通常情况下，我们在 MySQL 的配置文件中设置 sql_mode 后，会影响全局的数据库操作。

在 MySQL 命令行界面下，可以使用以下命令获取当前的 sql_mode 配置：

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

其中 @@GLOBAL.sql_mode 表示全局的 sql_mode 配置，而 @@SESSION.sql_mode 则表示当前会话（session）中的 sql_mode 配置。

sql_mode 的值

使用以下命令即可获取 MySQL 提供的所有 sql_mode 值：

SELECT @@sql_mode;

以下是 MySQL 中常用的 sql_mode 取值：

• STRICT_TRANS_TABLES：启用严格模式，强制所有数据插入、更新为遵循 SQL 标准。
• NO_ZERO_IN_DATE：禁止 '0000-00-00' 日期值。
• NO_ZERO_DATE：禁止 '0000-00-00' 和 '0000-00-00 00:00:00' 日期时间值。
• ERROR_FOR_DIVISION_BY_ZERO：在除零操作时抛出错误。
• STRICT_ALL_TABLES：在插入错误或警告时禁止自动填充默认值。
• TRADITIONAL：启用了严格模式、ERROR_FOR_DIVISION_BY_ZERO 和 NO_AUTO_CREATE_USER。

示例说明

我们可以通过以下示例说明 SQL 模式的作用：

示例一：

默认情况下，MySQL 允许插入空字符串（''）到 INT 类型的字段中，但实际上它会被转换成 0。这在某些场景下会导致意外的问题。为了解决这个问题，我们可以设置 sql_mode，在执行 SQL 操作时强制检查值的类型。

-- 查看当前的 sql_mode
SELECT @@sql_mode;

-- 启用严格模式
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL';

-- 插入空字符串时会抛出错误
INSERT INTO `test` (`id`, `value`) VALUES (1, '');

示例二：

在默认情况下，MySQL 允许操作者在某些操作中使用 NULL，例如：

• 对于数据类型中，当字段在未插入时，值就为NULL。
• 在一个 AUTO_INCREMENT 字段中插入 NULL，会插入下一个自动增量值。

然而，这在某些场合下可能会被误用，例如存储日期时使用 NULL 值表示缺失。因此，我们可以通过设置 sql_mode，来禁止使用 NULL。

-- 查看当前的 sql_mode
SELECT @@sql_mode;

-- 启用只读模式
SET sql_mode = 'READ_ONLY';

-- 以下操作将会抛出错误
INSERT INTO `test` (`id`, `date`) VALUES (1, NULL);

UPDATE `test` SET `date` = NULL WHERE `id` = 1;

总结

sql_mode 作为 MySQL 非常重要的组合变量，可以为数据库操作者提供更加严格、规范的数据管理方式。因此，在进行 MySQL 数据库操作时，我们需要对他进行更加深入的了解和设置，以达到更好的数据管理效果。