首页 > 数据库 >阿里云RDS的mysql的sql_mode=only_full_group_by问题

阿里云RDS的mysql的sql_mode=only_full_group_by问题

时间:2022-10-21 15:12:36浏览次数:41  
标签:full group RDS NO ZERO mode sql DATE

使用group by分组时,查询字段没有分组的字段就会报错

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.id' 
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

  

原因:

mysql5.7版本和以上的版本都默认开启了only_full_group_by 模式

 

我这以MySQL8.0.25版本为例

解决方法:

1、查看sql_mode

SELECT @@sql_mode;  

查询结果

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

  

2、将查询的结果去掉ONLY_FULL_GROUP_BY,就成了:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

  

3、去阿里云控制台,找到云数据库RDS→参数设置

 

 

 

 

将STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION设置进去

 

 

 

 

 

 重新启动云数据库RDS,运行参数变成了:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 

 

 

 

在使用group by分组时,查询字段没有分组的字段就不会报错

 

标签:full,group,RDS,NO,ZERO,mode,sql,DATE
From: https://www.cnblogs.com/Amywangqing/p/16813522.html

相关文章