# 运行模式

houdunren.com @ 向军大叔

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 users  SET birthday  = '0000-00-00'

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

set sql_mode=TRADITIONAL;

INSERT INTO users  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中列出其他字段

# 问题分析

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

  • 下面的班级编号 class_id 是男/女两个组中的,这个值是不确定的也是无意义的
  • 使用ONLY_FULL_GROUP_BY模式后将报错
SELECT class_id,count(*) AS c FROM users u GROUP BY sex

去除ONLY_FULL_GROUP_BY模式后可以读到class_id ,但针对这个SQL是无意义的

SELECT @@sql_mode

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

# any_value

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

SET sql_mode = 'ONLY_FULL_GROUP_BY,TRADITIONAL'

SELECT class_id,count(*) AS c FROM users 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 users u GROUP BY sex

# primary/unique

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

SELECT c.id,c.name,count(*) FROM users u
INNER JOIN class c 
ON c.id = u.class_id 
GROUP BY c.id