mysql的sql_mode中ONLY_FULL_GROUP_BY 完全解析
一、背景
今天接到一个需求,开发一个批量查询某个多关系聚合结果给前端。Device是主表,而Param是子逻辑表,它们之间存在一对多的关系。因为是聚合查询操作,自然而然想到使用MySql的group by 聚合操作。于是写下以下SQL语句:
SELECT * FROM `device_param` GROUP BY device_id
然后执行的结果很骨感,在workplace工作台中显示如下报错信息:
> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ds_equipment_group.device_param.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> 时间: 0.001s
是的,除了一堆错误并没有得出想要的结果。接着调整了下字段,发现如果前后查询的字段一致也就是select的字段和group by跟着的字段一致,那么语句是可以正常执行并得出结果。
记得以前使用MySql的时候并没有那么严格的要求,那么问题到底出现在哪呢?
二、完全解析
既然是MySql的原因,那么是不是在某个版本升级后带来的问题呢?带着这个疑问,去翻了下网上的文档信息。果然和笔者猜的一样,在Mysql5.6中其实也有类似的设置,只是到了MySql5.7版本默认的设置就是sql_mode=only_full_group_by
。从而得知,上面语句报错的原因是:MySQL5.7版本默认设置了sql_mode = only_full_group_by
属性,而该模式要求查询的列必须包含在Group by后面的列,否则会报错。
sql_mode = only_full_group_by
官方解释为:
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中。
举个例子:
select a,b from table group by a,b,c; (正确)
select a,b from table group by a,b; (正确)
select a,b,c from table group by a,b; (错误)
上面的例子也就解释了为何我在调整字段后能够运行成功,因为前后一致的情况下,正好满足查询的列被包含在Group by的列中这个要求。
三、解决办法
那么解决的办法是哪些呢?其实从上面的解释中,我们已经得出两种解决办法。
- 第一种解决办法:调整SQL语句,对齐前后查询的字段,或者被包含在group by中,让语句能够执行起来。
- 第二种解决办法:此方法可以一劳永逸解决问题,让你的SQL能够随意飞起来。这个办法就是将Mysql5.7设置的
sql_mode
设置成别的模式。针对某个数据库参考脚本:
sql_mode=
‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
别的设置办法自行搜索。
四、扩展知识
除了sql_mode=only_full_group_by
设置值,还有哪些值并且含义是什么?下面我们逐一列举:
- NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。(不信的可以试试,默认的sql_mode你在自增主键列设置为0,该字段会自动变为最新的自增值,效果和null一样),如果用户希望插入的值为0(不改变),该列又是自增长的,那么这个选项就有用了。 - STRICT_TRANS_TABLES
在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制。(InnoDB默认事务表,MyISAM默认非事务表;MySQL事务表支持将批处理当做一个完整的任务统一提交或回滚,即对包含在事务中的多条语句要么全执行,要么全部不执行。非事务表则不支持此种操作,批处理中的语句如果遇到错误,在错误前的语句执行成功,之后的则不执行;MySQL事务表有表锁与行锁非事务表则只有表锁) - NO_ZERO_IN_DATE
在严格模式下,不允许日期和月份为零 - NO_ZERO_DATE
设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。 - ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL - NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户 - NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常 - PIPES_AS_CONCAT
将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似 - ANSI_QUOTES
启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符
到此我们已经详细解答了sql_mode相关知识点,希望对大家认识并正确使用sql_mode有帮助。
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。