升级MySQL 5.7后的一个隐藏坑

康康目前使用的云主机是上次腾讯一次购买三年的主机,之前的一年主机快到期了,所以迁移到了这台,迁移的时候顺便把MySQL版本升级了一下,以前用的是MySQL 5.5,现在升级到了MySQL 5.7。

本想着版本升级以后应该会很好用,当然之前也一直没察觉出来什么不好用的地方,直到今天……

康康突然想看看博客的留言排行,然后就发现竟然出现错误了,打不开了!

看了下是SQL错误,提示:mysql column which is not functionally dependent GROUP BY clause

康康看了下是group by的问题,于是看了下SQL语句,没什么问题啊!

属于正常执行的:

$sql_7days = $db->select('COUNT(author) AS cnt', 'author', 'url', 'mail')
        ->from('table.comments')
        ->where('status = ?', 'approved')
        ->where('created > ?', $period )
        ->where('type = ?', 'comment')
        ->where('authorId = ?', '0')
        ->where('mail != ?', $options->socialemail)   //排除自己上墙
        ->group('author')
        ->order('cnt', Typecho_Db::SORT_DESC)
        ->limit('51');    //读取几位用户的信息

但为何会出现错误呢?搜索了下才知道是因为MySQL 5.7开始,在sql_mode中有一项配置叫做ONLY_FULL_GROUP_BY,属于MySQL 5.7的新产物,通过这个sql_mode来提供SQL语句GROUP BY合法性的检查。

在之前,MySQL是允许target list中输出的表达式是除聚集函数或group by column以外的表达式,这个表达式的值可能在经过group by操作后变成undefined。

而其他如SQLServer、Oracle、PostgreSql等数据库都不支持select target list中出现语义不明确的列,出现就会报错,所以在MySQL 5.7中对这一语义问题进行了修复。

既然这样,那就是说咱们就必须要对group by语句进行严格的检查才可以执行咯,可是若之前是5.7以前的数据库语句,难道都要通过升级SQL语句才可以?难道不能兼容下?

于是,康康再查了下资料,发现其实可以把ONLY_FULL_GROUP_BY去除,这样就能兼容5.7之前的group by语句了。

首先登陆mysql,并查询当前的sql_mode:

show variables like "sql_mode";

会显示出当前所使用的sql_mode列表

+---------------+--------------------------------------------+ | Variable_name | Value |

+---------------+--------------------------------------------+sql_modeSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

把内容复制出来,然后打开my.cnf文件,在[mysqld]下面添加一行:

sql_mode = xxxx

其中xxx就是刚才复制出来的内容,只要把其中的ONLY_FULL_GROUP_BY去掉就行了。

最后修改:2019 年 04 月 27 日 07 : 29 PM
如果觉得我的文章对你有用,请随意赞赏

发表评论