场景:同一个商品,不同的型号,有不同的价格,例如1个手机有128G,2000元,256G,2500元,但是商品展示页就一条,价格显示2000元起,如果就一个商品,就不显示“起”字,这里采用的方法是,查询同一id的商品均价,与第一个价比较,如果不同,返回0,相同则返回1,通过0,1,让前端再来判断。
sql:使用avg函数查询price平均价,与第一条price比较,返回0或1,但是报错
因为在MySQL5.7.5后,mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”,这个配置严格执行了"SQL92标准"。所以导致了之前的一些SQL无法正常执行,其实,是我们的SQL不规范造成的,因为group by 之后,返回的一些数据是不确定的,所以才会出现这个错误。
在sql执行时,出现该原因,简单来说就是:
由于开启了ONLY_FULL_GROUP_BY的设置,如果select 的字段不在 group by 中,
并且select 的字段未使用聚合函数(SUM,AVG,MAX,MIN等)的话,那么这条sql查询是被mysql认为非法的,会报错误
ONLY_FULL_GROUP_BY 这种模式的特点
1:只要有聚合函数 sum(),count(),max(),avg() 等函数就需要用到 group by , 否则就会报上面的错误。
2:group by id (id 是主键) 的时候, select 什么都没有问题, 包括有聚合函数
3:group by role (非主键) 的时候, select 只能是聚合函数和 role ( group by 的字段) , 否则报错
解决方法有两个:
一、修改mysql配置文件:
找到mysql安装目录,用记事本直接打开my.ini文件
编辑my.cnf文件,在[mysql]标签下追加内容
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
重启mysql 服务
二、修改sql语句:
使用函数ANY_VALUE()包含报错字段
SELECT ANY_VALUE(avg( price )= price) FROM pms_sku_stock ss WHERE ss.product_id =27
ANY_VALUE()函数说明:
MySQL有any_value(field)函数,它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒绝。 这样sql语句不管是在ONLY_FULL_GROUP_BY模式关闭状态还是在开启模式都可以正常执行,不被mysql拒绝。 any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。 官方有介绍,地址:https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value
参考链接:MySQL错误-this is incompatible with sql_mode=only_full_group_by完美解决方案