mysql group_by报错

Davide~苏

已于 2022-06-24 12:02:09 修改

阅读量1.6w

点赞数

分类专栏：开发工具的安装及使用文章标签： mysql 数据库 mysql目录权限

于 2022-06-24 09:51:14 首次发布

本文链接：https://blog.csdn.net/GodDavide/article/details/125439779

版权

开发工具的安装及使用专栏收录该内容

13 篇文章 0 订阅

订阅专栏

报错信息

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