【MySQL】sql_mode 模式

目录

• 前言
• 查看 sql_mode 方式
• • 查看当前会话的 sql_mode
  • 查看全局的 sql_mode
• sql_mode 配置属性
• • • • ONLY_FULL_GROUP_BY
      • STRICT_TRANS_TABLES、STRICT_ALL_TABLES
      • NO_ZERO_IN_DATE
      • NO_ZERO_DATE
      • ERROR_FOR_DIVISION_BY_ZERO
      • NO_ENGINE_SUBSTITUTION
• 设置 sql_mode
• • 设置当前会话的 sql_mode
  • 设置全局的 sql_mode
  • 永久设置 sql_mode

前言

MySQL 的 sql_mode 分两种模式，宽松模式与严格模式。在 MySQL 5.7 版本之前，MySQL 服务的默认模式一直是宽松模式，从版本 5.7 开始，MySQL 服务默认模式为严格模式。

查看 sql_mode 方式

查看当前会话的 sql_mode

查看当前会话的 sql_mode 方式有两种，现在是第一种：

SHOW VARIABLES LIKE 'sql_mode';

如果是严格模式，我们可以看到查询出来的值中有很多属性。我们接下来看第二种查询方式：

SELECT @@session.sql_mode;

通过这样也可查询出当前会话的 sql_mode 配置。

查看全局的 sql_mode

SELECT @@global.sql_mode;

sql_mode 配置属性

我们通过上面的查询方式可以看到 sql_mode 值有以下属性

• ONLY_FULL_GROUP_BY
• STRICT_TRANS_TABLES
• NO_ZERO_IN_DATE
• NO_ZERO_DATE
• ERROR_FOR_DIVISION_BY_ZERO
• NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY

如果配置了该属性，表示在 SQL 语句中，如果用到了 group by 分组，那么只能查询分组的字段。如果要查询没有进行分组的字段就会报错。

STRICT_TRANS_TABLES、STRICT_ALL_TABLES

如果配置了该属性，表示插入数据时，SQL 语句中的字段属性与实际表中的字段属性不匹配或者字符串长度超出等情况就会报错。
如果没有配置的话，在插入数据的时候并不会报错，字段长度超出时只会保存要求的长度，超出的长度就会丢失；字段属性不匹配的话就不会保存该字段的数据。

NO_ZERO_IN_DATE

在插入数据的时候，如果日期数据的月、日是 0，配置了该属性就会报错。

NO_ZERO_DATE

不能插入 0 年 0 月 0 日 的数据。

ERROR_FOR_DIVISION_BY_ZERO

如果配置了该属性，在插入 SQL 中出现了分母为 0 的情况就会报错，数据保存不成功。如果没有配置的话，并不会报错，保存后分母为 0 的字段值为 null。

NO_ENGINE_SUBSTITUTION

sql_mode 配置了该属性，在 SQL 中指定了 MySQL 没有的引擎就会报错。如果没有配置该属性，并不会报错，会使用默认的 innerdb 引擎。

设置 sql_mode

设置当前会话的 sql_mode

SET SESSION sql_mode = '';

单引号中可以配置你想要的属性，这样设置只在当前会话中有效，该会话关闭后就会失效。或者在当前会话设置后并不影响其他会话的配置。

设置全局的 sql_mode

SET GLOBAL sql_mode = '';

通样在单引号中配置自己想要的属性。配置后也是只在当前服务中生效，如果重启 MySQL 服务后该配置就会失效。

永久设置 sql_mode

在 MySQL 的配置文件 my.cnf（windows 系统的 MySQL 的配置文件为 my.ini）中的 [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 服务。
如果在生产环境中想要永久设置 sql_mode 需要重启 MySQL 服务，可能会影响使用。最好是在配置文件 my.cnf 中配置好，先不重启 MySQL 服务，然后设置全局 sql_mode。这样在不重启 MySQL 服务的情况下修改了配置，并且将来如果 MySQL 服务重启配置也不会失效。