Docker中MySQL sql_mode=only_full_group_by不兼容报错解决

你改了全局sql_mode依然报错的核心原因很简单：

• SET GLOBAL 配置只对 修改执行完成后新创建的数据库连接 生效，你应用服务里的数据库连接池会提前创建一批长连接常驻，这些连接在你改配置之前就已经建立，会一直沿用旧的sql_mode参数，不会自动同步全局配置的修改。
• Navicat能正常运行是因为你在Navicat里执行SQL时会新建连接，刚好拿到了修改后的配置，和应用侧的报错不冲突。
• 额外提一句：永久关闭ONLY_FULL_GROUP_BY属于典型的治标不治本，这个规则是SQL标准里用来避免分组查询结果不确定的，关掉之后很容易出现聚合值计算错误、返回字段随机的问题，生产环境强烈不推荐这么做。

另外你原本的SQL本身还有逻辑bug：多对一关联了products、reviews、store_product_promotions三张表后，只要一个店铺下有多个商品、多个有效促销，就会产生笛卡尔积，直接导致你算的AVG(reviews.rating)值不准，比实际值偏大或者偏小。

直接把SQL改成符合ONLY_FULL_GROUP_BY规范的写法，不需要修改任何数据库配置，兼容性最好，还能顺便修复上面说的聚合计算错误问题。
核心调整思路：

• 把聚合计算（平均评分）拆成独立子查询提前算好，再关联店铺主表，避免多表JOIN产生重复行。
• 你原来关联store_product_promotions后没有查询任何该表的字段，属于无意义关联，直接删掉即可；如果是要筛选存在有效促销的店铺，改用EXISTS判断，不要用LEFT JOIN避免产生重复行。
• 对于和店铺ID一一对应的经纬度等字段，如果数据库依然提示非聚合字段错误，用ANY_VALUE()包裹即可，不需要把所有字段都塞到GROUP BY里。

修正后的SQL如下：

SELECT 
    stores.*,
    COALESCE(rating_stat.avg_rating, 0) AS rating,
    SQRT(
        POW(69.1 * (ANY_VALUE(store_address.latitude) - 0.0), 2) + 
        POW(69.1 * (0.0 - ANY_VALUE(store_address.longitude)) * COS(ANY_VALUE(store_address.latitude) / 57.3), 2)
    ) AS distance
FROM stores
LEFT JOIN store_address ON store_address.store_id = stores.id
-- 单独聚合计算店铺平均评分，避免笛卡尔积导致计算错误
LEFT JOIN (
    SELECT 
        products.store_id,
        AVG(reviews.rating) AS avg_rating
    FROM products
    LEFT JOIN reviews ON reviews.product_id = products.id
    GROUP BY products.store_id
) AS rating_stat ON rating_stat.store_id = stores.id
-- 如果需要筛选带有效促销的店铺，取消下面这段注释即可，不需要LEFT JOIN促销表
-- WHERE EXISTS (
--     SELECT 1 FROM store_product_promotions
--     WHERE store_product_promotions.store_id = stores.id
--     AND store_product_promotions.start_date <= :startDate
--     AND store_product_promotions.end_date >= :endDate
--     AND store_product_promotions.active = 1
-- )
WHERE stores.site_id = :siteId
GROUP BY stores.id
ORDER BY stores.created_at DESC
LIMIT :limit
OFFSET :offset

如果你只是临时测试需要关闭ONLY_FULL_GROUP_BY，改完全局配置后必须重启你的应用服务，让连接池销毁所有旧连接、重新建立新连接，新的会话才会应用你修改后的sql_mode配置。
也可以直接在应用的数据库连接配置里加会话初始化语句，连接建立时自动执行SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))，不需要修改全局配置，但依然不建议长期使用。

内容的提问来源于stack exchange，提问作者Laggio Vanotto