SQL报错this is incompatible with sql_mode=only_full_group_by_functionally dependent on columns in group by clau

一、bug记录

1.1.bug截图 

1.2.sql语句 

SELECT id,batch_no,if_code,channel_mch_no,bill_date,bill_type,currency,order_id,
channel_order_no,channel_amount,channel_fee_amount,channel_success_at,
channel_user,channel_state,org_pay_order_id,channel_refund_amount,created_at,
updated_at 
FROM t_check_channel_bill 
WHERE batch_no = 'zjpay_M1638950514_20230410' AND order_id IS NOT NULL 
GROUP BY order_id 
HAVING MAX(channel_success_at)

1.3.完整错误 

Expression #1 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'xyzpay.t_check_channel_bill.id' which is not 
functionally dependent on columns in GROUP BY clause; this is 
incompatible with sql_mode=only_full_group_by

二、情景复现 

 三、bug处理

        在上面，我在其他数据库，仿照着写出来的SQL语句，并没有出现示例错误，在这种情况下，可以先分析错误原因：

        1）sql语句错误

        2）数据错误

        3）库的问题

3.1.sql错误分析和数据错误

        原错误：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xyzpay.t_check_channel_bill.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

翻译过来就是select选择出来的列不在group by后面也不依赖于group by列的功能。这与sql_mode=only_full_group_by不兼容。是因为出现了错误查询。如果语句中有group by子句，那么select后面就必须是group by后面的属性或者是 某聚集函数（group by后面没有的属性）【例如avg(),max(),min()…】

        但很明显，可以看到，我的 order_id 是包含在查询语句中的，所以这个错误描述很明显是不准确的。

        无论将SQL如何调整，这个错误始终跟随。并且错误的最后一句话：这与sqlmode=onlyfull_groupby不兼容， 那我们就去查一下sqlmode

3.2.库错误分析 

        首先使用出错误的库进行查询

sql语句：
    
    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

        然后查询“错误复现”失败的库

sql语句：
    
    select @@sql_mode
 
查询结果：
 
    STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

         可以看出这查出结果并不一样，那可以修改一下库，看看能不能复现这个错误。

        值得一提的是：修改数据库本身，并不能使用update语句，直接使用 set 即可。

        set修改数据库： 

SET @@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'

修改结果        

         重新模拟错误，可以看到错误被复现了，说明找的地方正确

         然后就发现，原本能正常运行的sql，现在出现了问题。那么可以确定，这个SQL错误是数据库的原因。既然是数据库的问题，那反过来修改数据库，把这个字段修改掉，那就没问题了。

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

 

        这个修改方式有缺陷，因为这个是临时修改，SQL重启后就会失效。但我使用的是公司数据库，所以数据库基本不会重启， 就没这个问题了。但如果是自己本地做测试，那么重启后就会失效。

3.3.持久化修改

        

修改my.ini（Windows中MySQL配置文件）
在里面添加

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1'

        （目的是将ONLY_FULL_GROUP_BY关闭，为了便于展示，我将语句用回车分作两行了，实际添加中，无需如此。）
        在修改过程中，注意使用英文单引号  ‘’  将数据包起来，不能使用中文。然后将文件保存为ANSI编码格式。

        注：my.ini 是静态文件，与服务是否在运行没什么关系。所以说可以不必先 stop 服务再修改 my.ini。

四、拓展

        下面是官方MySQL 5.7参考手册中关于ONLY_FULL_GROUP_BY的解释。SQL使用文档

  

        翻译下就是：

        拒绝select列表、HAVING条件或ORDER BY列表引用非聚合列的查询，这些列既不在GROUP BY子句中命名，也不依赖于GROUP BY列的功能(由GROUP BY列唯一确定)。
        从MySQL5.7.5开始，默认的SQL模式包括ONLY_FULL_GROUP_BY。(在5.7.5之前，MySQL没有检测到函数依赖，默认情况下也没有启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为，请参阅MySQL 5.6参考手册。 ）
        标准SQL的MySQL扩展允许在HAVING子句中引用select列表中的别名表达式。在MySQL 5.7.5之前，启用ONLY_FULL_GROUP_BY将禁用此扩展，因此需要使用无别名表达式编写HAVING子句。从MySQL 5.7.5开始，这个限制被取消了，因此HAVING子句可以引用别名，而不管是否启用了ONLY_FULL_GROUP_BY。
        有关更多的讨论和示例，请参见12.20.3节，“MySQL对GROUP BY的处理”。