最近遇到的一个问题，有个项目使用的mysql升级到5.7后，发现项目中有些sql语句执行会报错。

报错类似如下:

1
2
[SQL]select accountname,accounttype from weixin_account group by accountname;
[Err] 1055 - 'wcmgt.weixin_account.accounttype' isn't in GROUP BY

于是便查阅资料了。随后发现，原来mysql5.7.5后，ONLY_FULL_GROUP_BY 默认为真，那么此时select中的字段必须出现在group by中。官方的说明文档如下：

SQL Mode Notes

  • Incompatible Change: These SQL mode changes were made:
    • Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default.
    • Implementation of the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected.
    • MySQL now recognizes when a nonaggregated selected column is functionally dependent on (uniquely determined by) GROUP BY columns.
    • MySQL has an extension to standard SQL that permits references in the HAVING clause to aliased expressions in the select list. Previously, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. This restriction has been lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

In consequence, ONLY_FULL_GROUP_BY is now enabled by default, to prohibit nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

  • The changes to the default SQL mode result in a default sql_mode system variable value with these modes enabled: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION.
  • The ONLY_FULL_GROUP_BY mode is now included in the modes comprised by the ANSI SQL mode.
  • A new function, ANY_VALUE(), is available that can be used to force MySQL to accept queries that it thinks should be rejected with ONLY_FULL_GROUP_BY enabled. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.

If you find that having ONLY_FULL_GROUP_BY enabled causes queries for existing applications to be rejected, either of these actions should restore operation:

  • If it is possible to modify an offending query, do so, either so that nondeterministic nonaggregated columns are functionally dependent on GROUP BY columns, or by referring to nonaggregated columns using ANY_VALUE().
  • If it is not possible to modify an offending query (for example, if it is generated by a third-party application), set the sql_mode system variable at server startup to not enable ONLY_FULL_GROUP_BY.

For more information about SQL modes and GROUP BY queries, see Server SQL Modes, and MySQL Handling of GROUP BY. (Bug #18486310)

解决上面问题的方案有两种:

  1. 在sql查询语句中不需要group by的字段上使用any_value()函数 ;这种方法需要修改sql语句，对于原有系统的改动可能会比较大。
  2. 修改sql_mode的值,去掉ONLY_FULL_GROUP_BY，方式如下: 2.1. 查看sql_mode 
    1
    select @@global.sql_mode
    结果为：

    ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.2. 去掉ONLY_FULL_GROUP_BY，重新设置值 

1
set @@global.sql_mode        ='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

参考:

  1. MySQL Handling of GROUP BY
  2. Changes in MySQL 5.7.5
  3. Server SQL Modes