MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一,这可能导致一些sql语句失效。
比如下表game:
id | group_id | name | score |
---|---|---|---|
1 | A | 小刚 | 20 |
2 | B | 小明 | 19 |
3 | B | 小花 | 17 |
4 | C | 小红 | 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_by
解决方法
- 把group by字段
group_id
设成primary key 或者 unique NOT NULL。这个方法在实际操作中没什么意义。 - 使用函数
any_value
把报错的字段name
包含起来。如,select any_value(name), group_id from game group by group_id
。 - 在配置文件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官方文档有详细解译。
执行成功后,返回结果应该是
name | group_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)
返回结果
name | group_id | score |
---|---|---|
小明 | B | 17 |
小红 | C | 18 |
小刚 | A | 20 |
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
高。