一、背景

今天接到一个需求，开发一个批量查询某个多关系聚合结果给前端。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有帮助。