运行模式

向军大叔每晚八点在 抖音 和 bilibli 直播

MYSQL 运行时可以使用不同的模式，这是很多语言中都有的特性比如 JS/PHP 等。在 MYSQL5.7 前 SQL 运行在宽松模式，会以开放不严谨的方式运行。

在 5.7 后默认运行模式较为严格，这会造成在升级数据库时，数据库的操作出现问题。所以有必要理解 MYSQL 的运行模式机制。

模式说明

查看当前运行模式

SELECT @@sql_mode

模式选项

不同运行模式即对不同模式选项的配置，下面是常用的模式选项

选项	说明
ONLY_FULL_GROUP_BY	结果中的字段需要在 GROUP BY 中出现
STRICT_TRANS_TABLES	如果一个值不能插入到一个事务表中，则中断当前的操作
NO_ZERO_IN_DATE	不能插入为 0 的日期和月份
NO_ZERO_DATE	不能添加 0000-00-00 格式的日期
ERROR_FOR_DIVISION_BY_ZERO	除数不能为零，禁止该模式后插入的结果为 NULL

常用模式

常用模式是系统将不同的模式选项过行的组合

模式	说明
ANSI	宽松模式：对长度超过字段定义等错误进行截取等操作。报 WARNING 警告错误
TRADITIONAL	严格模式：对数据进行严格校验。事务处理中会进行事务回滚操作。非事务时，发生错误时就立即报错终止，会造成有部分数据插入。

设置当前会话为宽松模式

set session sql_mode=ANSI;
# 或
SET sql_mode = ANSI

设置全局模式限制

SET GLOBAL sql_mode='';

设置为严格模式

set session sql_mode=TRADITIONAL;

对比分析

我们来看下在不同模式下的表现，首先宽松模式可以插入'0000-00-00'的日期格式

SET sql_mode = ANSI

INSERT INTO stu SET birthday  = '0000-00-00'

当使用严格模式时将不可以插入

set sql_mode=TRADITIONAL;

INSERT INTO stu SET birthday  = '0000-00-00'

内容超过字段长度时宽松模式会过行截断并可以正常插入，下面的字段 rank 使用的是 smallint

SET sql_mode = ANSI

INSERT INTO article SET rank =1000000;

当改为严格模式时不能插入数据会报错

set sql_mode=TRADITIONAL;

INSERT INTO article SET rank =1000000;

ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY 要求 SELECT 中的字段是在与 GROUP BY 中使用的字段

• 如果 GROUP BY 是主键或 UNIQUE NOT NULL 时可以在 SELECT 中列出其他字段
• 使用 max/min/avg/count 等聚合函数时不受 ONLY_FULL_GROUP_BY 模式影响

问题分析

下面获取男生和女生的人数

• 下面的班级编号 class_id 是男/女两个组中的，这个值是不确定的也是无意义的
• 使用 ONLY_FULL_GROUP_BY 模式后将报错

SELECT class_id,count(*) AS c FROM stu u GROUP BY sex

去除 ONLY_FULL_GROUP_BY 模式后可以读到 class_id ，但针对这个 SQL 来讲 class_id 结果是不确定的也是无意义的

SELECT @@sql_mode

SELECT class_id,count(*) AS c FROM stu u GROUP BY sex

any_value

有些情况下确实要取到非 GROUP BY 中的字段，使用 any_value 函数可以从组中读取第一个值，解决使用 ONLY_FULL_GROUP_BY 报错的问题

any_value 会读取使用 group by 分组后的每组中第一个数据

SET sql_mode = 'ONLY_FULL_GROUP_BY,TRADITIONAL'

SELECT any_value(class_id),count(*) AS c FROM stu u GROUP BY sex;

聚合函数

在 SELECT 中使用 max/min/avg/count 等聚合函数时不受 ONLY_FULL_GROUP_BY 模式影响

SET sql_mode = 'ONLY_FULL_GROUP_BY,TRADITIONAL'

SELECT max(class_id),count(*) AS c FROM stu u GROUP BY sex

primary/unique

GROUP BY 使用主键或 unique not null 字段时，不受 ONLY_FULL_GROUP_BY 的约束。下面是获取每班的人数的示例

SELECT c.id,c.cname,count(*) FROM stu u
INNER JOIN class c
ON c.id = u.class_id
GROUP BY c.id;