问题:
最近遇到个奇怪的问题,有个sql在开发库运行正常,但是发到生产上就报错,最后几经周折找到了原因 原来是group by函数使用不当造成的,5.7及以上版本的MySQL会出现此问题,特此记录以作纪念。
如图所示记录,我们需要查所有用户的no、name信息,sql如下:
select customer_no, customer_name from customer_info group by customer_no;
5.7及以上版本的MySQL报错:
解决:
1、修改sql
翻阅资料发现:在标准SQL中使用group by时,要查询的字段 需要加在group by后面作为分组条件、或者用聚合函数处理一下,例如:
select customer_no, customer_name from customer_info group by customer_no,customer_name ;
或者
select customer_no, max(customer_name) from customer_info group by customer_no;
2、更改mysql参数
在旧版本中之所以没有这种限制是因为:MySQL扩展了 GROUP BY的用途允许你可以使用SELECT 列表中不出现在GROUP BY语句中的列,通过only_full_group_by参数可以控制这种限制,在旧版本中这个模式是默认关闭的,而在5.7.5之后默认开启了这个模式。
查看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';