首页 > 编程笔记 > MySQL笔记 阅读：3

MySQL sql_mode设置详解（新手必看）

可以将 sql_mode 翻译为 SQL 模式，它是一组 MySQL 支持的基本语法及数据校验规则。sql_mode 的取值不同会影响 MySQL 支持的基本语法及数据校验规则。

通过设置 sql_mode 的值，可以完成不同严格程度的数据校验，保证数据的准确性。MySQL 服务器可以在不同的 SQL 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 的值。

在 MySQL 5.6 和 MySQL 5.7 及其后续版本中，sql_mode 的默认值是不一样的：

宽松模式与严格模式对比

如果 MySQL 设置的是宽松模式，那么，在插入数据的时候，即便插入了一条错误数据，也可能会被接受，并且不会报错。

例如，在创建一张表时，该表中有一个字段为 name，该字段的数据类型是 CHAR（10）。如果在插入数据的时候，name 字段对应的数据长度超过 10，如插入数据“1234567890abc”，那么此时 MySQL 并不会报错，而会截取前 10 个字符存储到表中。

也就是说，name 字段的数据存储为“1234567890”，“abc”被舍弃了。但是这条数据是错误的，因为其长度超过字段长度。这就是宽松模式的效果。

通过设置 sql_mode 的值为宽松模式，可以保证大多数 SQL 语句符合标准的 SQL 语法，在进行数据库迁移或项目迁移时，就不需要对与业务相关的 SQL 语句进行较大的修改。

严格模式就是当出现宽松模式下的错误时，MySQL会给出报错信息。从MySQL 5.7开始，将sql_mode的默认值更改为严格模式。生产环境下的数据库必须设置为严格模式，开发环境、测试环境下的数据库也必须设置为严格模式，这样在开发测试阶段就可以发现问题。即便我们使用的是MySQL 5.6，也应该自行将sql_mode的值更改为严格模式。

根据以往的开发经验，MySQL 等数据库总想包揽关于数据的所有操作，包括数据校验。其实，我们应该在自己开发的项目程序中进行数据校验，虽然在编写程序代码的时候多了一些数据校验流程，但是在进行数据库迁移或项目迁移时会方便很多。

例如，在严格模式中包含 NO_ZERO_DATE 值，那么 MySQL 将不允许插入零日期，否则会抛出错误。使用如下语句创建表 test_time： 
mysql> CREATE TABLE `test_time` (
         `id` INT NOT NULL,
         `time` date DEFAULT NULL,
         PRIMARY KEY (`id`)
       ) ENGINE = INNODB
         DEFAULT CHARSET = utf8mb4
         COLLATE = utf8mb4_0900_ai_ci;

分别测试下面两条插入语句，从结果中可以看到，当日期设置为“0000-00-00”时，因不满足 sql_mode 中的 NO_ZERO_DATE 条件而抛出错误： 
mysql> INSERT INTO test_time VALUES (1,'1993-01-02');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_time VALUES (2,'0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'time' at row 1

sql_mode 模式查看和设置

使用如下语句查看 sql_mode 的值，从结果中可以看到，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_ENGINE_SUBSTITUTION                 |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------------------------------------------+
| @@GLOBAL.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 的常用值及其含义
常用值 含义
ONLY_FULL_GROUP_BY 对于 GROUP BY 聚合操作，如果在 SELECT 中的列没有在 GROUP BY 中出现，那么这条 SQL 语句是不合法的。
NO_AUTO_VALUE_ON_ZERO 该值影响自增长列的插入。在默认设置下，插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为 0，而该列又是自增长的，该值就有用了。
STRICT_TRANS_TABLES 在该模式下，如果一个值不能被插入一个事务表中，则中断当前操作。对非事务表不进行限制。
NO_ZERO_IN_DATE 在严格模式下，不允许日期和月份为 0。
NO_ZERO_DATE 设置该值后，MySQL 将不允许插入零日期，否则会抛出错误而非警告。
ERROR_FOR_DIVISION_BY_ZERO 在 INSERT 或 UPDATE 过程中，如果数据被 0 除，则会抛出错误而非警告。如果没有设置该值，则数据被 0 除时将返回 NULL。
NO_AUTO_CREATE_USER 禁止 GRANT 创建密码为空的用户。
NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译，则将抛出错误。如果没有设置该值，则用默认的存储引擎代替，并抛出一个异常。
PIPES_AS_CONCAT 将 “||” 视为字符串的连接操作符而非 OR 运算符。这和 Oracle 是一样的，也和字符串拼接函数 CONCAT() 类似。
ANSI_QUOTES 设置该值后，将不能用双引号来引用字符串，因为双引号将被解释为标识符。

设置 sql_mode 有两种方式，分别是临时设置和永久设置：
1) 临时设置就是在命令行窗口中设置 sql_mode，语法如下所示： 
SET GLOBAL sql_mode='modes...';   -- 全局，当前服务生效，重启后失效
SET SESSION sql_mode='modes...';  -- 当前会话生效，关闭会话后失效
举例如下： 
-- 改为严格模式，当前会话生效
SET SESSION sql_mode='STRICT_TRANS_TABLES';
-- 改为严格模式，当前服务生效
SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

2) 永久设置就是在 my.cnf 配置文件中设置 sql_mode。首先在 my.cnf 配置文件（Windows 平台下是 my.ini 配置文件）中新增如下配置，然后重启 MySQL 服务： 
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
当然，在生产环境中对于重启 MySQL 服务是严格控制的。因此，无论是采用临时设置方式，还是采用永久设置方式，即便有一天真的重启 MySQL 服务，对 MySQL 的相关设置也会永久生效。

相关文章