mysql5.7 事件_记录mysql5.5升5.7的group by报错事件

最新推荐文章于 2021-02-07 05:27:26 发布

邵天兰

最新推荐文章于 2021-02-07 05:27:26 发布

阅读量134

点赞数

文章标签： mysql5.7 事件

本文链接：https://blog.csdn.net/weixin_36476695/article/details/113599583

版权

一、执行SQL

SELECT

C.ID AS "id",

A.ID AS "warehouseGroupId",

A.standard_quotation AS "standardQuotation",

C.service_provider_name AS "serviceProviderName",

B.warehouse_group_code AS "warehouseGroupCode",

B.warehouse_group_name AS "warehouseGroupName",

C.service_provider_code AS "serviceProviderCode"

FROM

slp_t_warehouse_group_service_publish A

LEFT JOIN slp_t_warehouse_group B

ON A.warehouse_group_id = B.ID

LEFT JOIN slp_t_service_provider C

ON C.ID = B.service_provider_id

LEFT JOIN slp_t_logistics_service_product_publish D

ON D.ID = A.LOGISTICS_SERVICE_PRODUCT_PUBLISH_ID

LEFT JOIN `slp_t_logistics_service` E ON E.`ID` = D.`LOGISTICS_SERVICE_ID`

WHERE D.SERVICE_STATUS = '01' AND E.`logistics_service_code` = 'CHC01'

AND A.is_show = '01'

AND service_provider_code ='FWS001'

GROUP BY

C.service_provider_name,

B.warehouse_group_code,

B.warehouse_group_name

此SQL在5.5版本下能够正常执行，在5.7的版本下执行报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'slpsit1.C.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

二、解析

根据官方记录mysql 5.7 模式中。默认启用了ONLY_FULL_GROUP_BY。就是说select 下面的字段没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

三、解决(引用博客https://www.jb51.net/article/123595.htm中的解决方案)

1.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';

去掉ONLY_FULL_GROUP_BY即可正常执行sql.

2. 不去ONLY_FULL_GROUP_BY, 时 select字段必须都在group by分组条件内(含有函数的字段除外)。(如果遇到order by也出现这个问题，同理，order by字段也都要在group by内)。

3.利用ANY_VALUE()这个函数　https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. 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.

如上面的sql语句可写成：

SELECT

ANY_VALUE(A.ID) AS "warehouseGroupId",

ANY_VALUE(A.standard_quotation) AS "standardQuotation",

ANY_VALUE(C.ID) AS "id",

ANY_VALUE(C.service_provider_name) AS "serviceProviderName",

ANY_VALUE(B.warehouse_group_code) AS "warehouseGroupCode",

ANY_VALUE(B.warehouse_group_name) AS "warehouseGroupName",

ANY_VALUE(C.service_provider_code) AS "serviceProviderCode"

FROM

slp_t_distribution_warehouse_group_service_publish A

LEFT JOIN slp_t_warehouse_group B