问题产生场景:今天使用Python3+PyMYSQL + SQLAlchemy 执行分组查询提示如下错误信息:
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hotel.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
[SQL: SELECT users.id AS users_id, users.name AS users_name, users.age AS users_age, users.email AS users_email, users.ctime AS users_ctime, users.extra AS users_extra
FROM users GROUP BY users.extra]
解决办法:
1.
在不删除现有模式的情况下,只向sql_mode添加一种模式:
SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));
2.
仅从sql_mode中删除特定模式而不删除其他模式:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));
3.
在您的情况下,如果您只想删除ONLY_FULL_GROUP_BY模式,请使用以下命令:
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
我选择的是第3 个方案,直接删除 only_full_group_by
# 查询当前MySQL支持模式
select version(),@@sql_mode;
# 移除ONLY_FULL_GROUP_BY 模式
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));