MySQL sql_mode的使用详解

我们来讲解一下MySQL sql_mode的使用。

什么是MySQL sql_mode

MySQL sql_mode 是 MySQL 提供的一种配置，用于控制 MySQL 在执行 SQL 语句时的行为。它定义了一组规则，来判断 SQL 语句是否合法，以及如何处理 SQL 语句中的错误。

MySQL sql_mode 常见的取值

1. STRICT_TRANS_TABLES

• 描述：在严格模式下，如果有任何非法数据插入到一个事务表中，就会报错并回滚事务。
• 取值：STRICT_TRANS_TABLES 或者不指定。
• 示例：创建一张名为 mytable 的表，包含一个整数类型的列 id 和一个字符串类型的列 name，同时设置 STRICT_TRANS_TABLES 模式。
  ```
  CREATE TABLE mytable (
  id INT NOT NULL,
  name VARCHAR(10) NOT NULL,
  PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET sql_mode = 'STRICT_TRANS_TABLES';
INSERT INTO mytable (id, name) VALUES ('1', 'MySQL');
```

2. STRICT_ALL_TABLES

• 描述：在严格模式下，所有插入到表中的非法数据都会报错。
• 取值：STRICT_ALL_TABLES 或者不指定。
• 示例：创建一张名为 mytable 的表，包含一个整数类型的列 id 和一个字符串类型的列 name，同时设置 STRICT_ALL_TABLES 模式。
  ```
  CREATE TABLE mytable (
  id INT NOT NULL,
  name VARCHAR(10) NOT NULL,
  PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET sql_mode = 'STRICT_ALL_TABLES';
INSERT INTO mytable (id, name) VALUES ('1', 'MySQL');
```

3. ANSI_QUOTES

• 描述：启用后，所有使用双引号的字符串被视为标识符，而不是字符串，因此必须使用反引号来转义标识符。
• 取值：ANSI_QUOTES 或者不指定。
• 示例：创建一个名为 "mytable" 的表，并设置 ANSI_QUOTES 模式。
  SET sql_mode = 'ANSI_QUOTES';
CREATE TABLE "mytable" (
"id" INT NOT NULL PRIMARY KEY,
"name" VARCHAR(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

MySQL sql_mode 的设置方法

1. 设置全局 sql_mode

在 MySQL 配置文件 my.cnf 或者 my.ini 中，通过设置 sql_mode 的值来控制 MySQL 的 sql_mode 行为。例如：

[mysqld]
...
sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

设置完毕后，需要重启 MySQL 服务，才能生效。

2. 设置会话级别 sql_mode

使用以下 SQL 语句，来设置会话级别 sql_mode：

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

在这个会话中，所有的 SQL 语句都会遵循这个 sql_mode 规则。如果需要恢复原来的 sql_mode 值，可以使用以下命令：

SET sql_mode = '';

总结

MySQL sql_mode 可以帮助我们避免在 SQL 语句执行时遇到一些意料之外的坑，提高了数据的完整性。

推荐在开发过程中启用 sql_mode，尽量在生产环境中也开启，尤其对于重要数据的表格，例如账户、财务数据等，更应开启sql_mode来增强数据的安全性。