【mysql】5.7以上的mysql使用group by 报错 (pymysql.err.OperationalError) (1055, “Expression #1 of SELECT list

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yushu_book1.gift.create_time' 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.create_time AS gift_create_time, gift.status AS gift_status, gift.id AS gift_id, gift.uid AS gift_uid, gift.isbn AS gift_isbn, gift.launched AS gift_launched, max(gift.create_time) AS max_1 
FROM gift 
WHERE gift.launched = false GROUP BY gift.isbn ORDER BY max(gift.create_time) DESC 
 LIMIT %(param_1)s]
[parameters: {'param_1': 30}]

原因：
在把MySQL升级到5.7或者更高的版本，一些以前看上去不会出错的group by 操作在这个版本以后就会出现语法报错的情况
解决：

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

事实上MySQL更新这种模式不是没有意义的：对于语义限制都比较严谨的多家数据库，如SQLServer、Oracled等数据库都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是所说的ONLY_FULL_GROUP_BY语义。

    # 必须要推入上下文才能使用
    with app.app_context():
        db.drop_all()
        db.create_all()
        # sql = "set @@GLOBAL.sql_mode =’’";
        # db.session.execute(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'"
        db.session.execute(sql)
        db.session.commit()

还有就是改mysql.ini文件，我没有这么做
还可以指定查询的字段，将create_time 放到 select中，让他含义明确。

by the way 查询的原始sql：

SELECT DISTINCT gift.create_time AS gift_create_time, gift.status AS gift_status, gift.id AS gift_id, gift.uid AS gift_uid, gift.isbn AS gift_isbn, gift.launched AS gift_launched, max(gift.create_time) AS max_1 
FROM gift 
WHERE gift.launched = false GROUP BY gift.isbn ORDER BY max(gift.create_time) DESC 
 LIMIT %(param_1)s

2024-02-29 14:18:23,975 INFO sqlalchemy.engine.Engine SELECT DISTINCT gift.create_time AS gift_create_time, gift.status AS gift_status, gift.id AS gift_id, gift.uid AS gift_uid, gift.isbn AS gift_isbn, gift.launched AS gift_launched 
FROM gift 
WHERE gift.launched = false GROUP BY gift.isbn ORDER BY gift.create_time DESC 
 LIMIT %(param_1)s

去掉distinct 一样报错

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

那么 group by 和 distinct 连用的作用是什么

distinct 是把distinct 后面的字段都去重了，而group by 仅仅是就create_time分的组，除非是 create_time 不同，反正这个sql放在这里怪怪的，我认为，是要distinct isbn 也就是 按时间倒叙排列的不一样的图书！！！！

情况有变

因为sqlalchemy使用的是连接池，有的执行了，有的没执行，sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’"
所以，还是需要在配置文件侧改。

②修改mysql配置my.cnf(windows下面是my.ini)，删除only_full_group_by属性，通过命令行进行修改