Python项目使用group_by distinct()去重，mysql报1055

 参考文章（站在巨人的肩膀上）

MySQL 错误代码:1055 解决方案（推荐！！）http://t.csdnimg.cn/KRToo

1.错误提示

sqlalchemy.exc.InternalError: (cymysql.err.InternalError) (1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fisher.gift.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
[SQL: SELECT DISTINCT gift.id AS gift_id, gift.uid AS gift_uid, gift.isbn AS gift_isbn, gift.launched AS gift_launched, gift.create_time AS gift_create_time, gift.status AS gift_status 
FROM gift 
WHERE gift.launched = false AND gift.status = %s GROUP BY gift.isbn ORDER BY gift.create_time]

 2. 原因

在MySQL5.7之后sql_mode中默认存在ONLY_FULL_GROUP_BY，SQL语句未通过ONLY_FULL_GROUP_BY语义检查所以报错

3. 解决方法

我是用的是Ubuntu，这里以Ubuntu系统做参考，window系统可以参考开头的那篇文章

1. 找到mysqld.cnf文件，我的是在/etc/mysql/mysql.conf.d/下

cd /etc/mysql/mysql.conf.d/

2. 使用vim编辑

sudo vim mysqld.cnf

3. 添加文件下面内容

# 设置sql_mode,关闭ONLY_FULL_GROUP_BY,避免使用group by函数导致1055错误
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

4. 重启MySQL

sudo systemctl restart mysql.service