mysql only_full_group_by|Annabelle's Blog

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 返回：

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

解决方法

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。mysql的默认配置是

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=，如果你确信其他选项不会造成影响的话。

执行成功后，返回结果如下

name	group_id
小刚	A
小明	B
小红	C

为什么默认设置ONLY_FULL_GROUP_BY限制？

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

因为执行上述语句实际上把两行纪录小明和小花合并成一行，搜索引擎不知道该返回哪一条，所以认为这样的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 (Anna:待了解)。

Laravel的相关配置

把my.cnf中的ONLY_FULL_GROUP_BY去掉后，laravel的运行结果仍然报错。翻过代码后发现，laravel在每一次请求都会设定sql_mode。

实现代码在Illuminate\Database\Connectors\MySqlConnector，不同版本稍有不同。

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

[
'connections' =>
    'msyql' => [
        // Behave like MySQL 5.6
        'strict' => false,

        // Behave like MySQL 5.7
        'strict' => true,
        'modes' => [
            'option1',
            'option2',
            ...
        ]
    ],
]

当strict=true(默认)即采用mysql的默认配置，当然也包含ONLY_FULL_GROUP_BY。
当strict=false即设置sql_mode=NO_ENGINE_SUBSTITUTION。

也可以自定义，在modes下添加相应的配置项。modes的优先级比strict高。

执行语句临时解决

查看sql_mode

SELECT @@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，重新设置值。

SET @@global.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，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';