mysql group_by报错

报错信息

pymysql.err.InternalError: (1055, "Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'contract.user_2_max_transfer_num.max_transfer_num' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

解决

使用group_by不兼容

# 查询 sql_mode 
show variables like '%sql_mode%'

结果出现 only_full_group_by

# 去掉 ONLY_FULL_GROUP_BY 
set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# 删除当前会话ONLY_FULL_GROUP_BY 
set session sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

造成这个情况的原因

我配置文件也有，但每次重启服务后，查询 sql_mode ：show variables like ‘%sql_mode%’

结果都会重新出现only_full_group_by

原因：文件权限不对！777的情况下会这样。改成755后 重启后就不再出现only_full_group_by了