sql_mode=only_full_group_by

MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一,这可能导致一些sql语句失效。

比如下表game:

idgroup_idnamescore
1A小刚20
2B小明19
3B小花17
4C小红18

执行sql: 
select name, group_id from game group by group_id(记为sql_make_group) 
返回:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'game.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by1

解决方法

  1. 把group by字段group_id设成primary key 或者 unique NOT NULL。这个方法在实际操作中没什么意义。

  2. 使用函数any_value把报错的字段name包含起来。如,select any_value(name), group_id from game group by group_id

  3. 在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY.。msqyl的默认配置是sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION。可以把ONLY_FULL_GROUP_BY去掉,也可以去掉所有选项设置成sql_mode=,如果你确信其他选项不会造成影响的话。

mysql官方文档有详细解译。

执行成功后,返回结果应该是

namegroup_id
小刚A
小明B
小红C

为什么默认设置ONLY_FULL_GROUP_BY限制?

对于上述的报错信息,我的理解是select字段里包含了没有被group by条件唯一确定的字段name。

因为执行sql_make_group语句实际上把两行纪录小明小花合并成一行,搜索引擎不知道该返回哪一条,所以认为这样的sql是武断的(arbitrary).

解决办法2和3都是禁止检查返回结果的唯一性。

进阶讨论

上述办法可以解决报错的问题,但也说明sql语句本身有逻辑问题。name字段不应该出现在返回结果,因为它是不确定的。

考虑这样的需求:按group_id分组后,找出每组得分最少的人。

执行sql: select any_value(name) as name, group_id, min(score) as score from game group by group_id order by min(score)

返回结果

namegroup_idscore
小明B17
小红C18
小刚A20

B组的name小明(因为小明的id更小),而期望结果应该是小花

所以单纯使用group by无法实现这样的需求。可以使用临时表的方法:

select id, name,t.group_id, t.score from (select group_id, min(score) as score from game group by group_id order by min(score)) t inner join game on t.group_id=game.group_id and t.score=game.score

如果要实现更为复杂的需求,可以考虑使用group_concat,请参考这里

Laravel的相关配置

把my.cnf中的ONLY_FULL_GROUP_BY去掉后,laravel的运行结果仍然报错。翻过代码后发现,laravel在每一次请求都会设定sql_mode。实现代码在Illuminate\Database\Connectors\MySqlConnector,不同版本稍有不同。

5.2和5.3可以在config/databse.php修改配置。

[
'connections' =>    'msyql' => [
                                   'strict' => true,
                                          'modes' => [ 
                      'option1', 
                                 'option2',            ...
                              ]
                                      ],
]

strict=true(默认)即采用mysql的默认配置,当然也包含ONLY_FULL_GROUP_BY。 
strict=false即设置sql_mode=NO_ENGINE_SUBSTITUTION。 
也可以自定义,在modes下添加相应的配置项。modes的优先级比strict高。