mysql执行select group_by语句sql_mode=only_full_group_by报错现象

环境

安装完特定版本mysql的任何环境 本次使用test mysql版本mysql5.7

参考

https://blog.csdn.net/qq118640594X/article/details/128024350

image-20230719094741996

配置文件位置

ubuntu:/etc/mysql/mysql.conf.d/mysqld.cnf
centos: vim /etc/my.cnf

现象

sql_mode=only_full_group_by

image-20230719091503156

解决

修改全局mode参数时只对执行完此条语句后创建的数据库生效 若想对旧的数据库生效则需要 在旧数据下面执行语句 建议 安装完成数据库后便在mysql全局配置文件下进行配置此时即可全局永久生效

方式一

1. 对旧库执行修改mode_sql参数 （此处假设旧库为test）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 列出全部数据库
show databases;  
# 选择test数据库
use test; 
# 查看test数据库的表
show tables;
# 查询sql_mode参数
mysql> select @@global.sql_mode;                                                                                                                 
+-------------------------------------------------------------------------------------------------------------------------------------------+    
| @@global.sql_mode                                                                                                                         |    
+-------------------------------------------------------------------------------------------------------------------------------------------+    
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |    
+-------------------------------------------------------------------------------------------------------------------------------------------+    
1 row in set (0.00 sec) 
# 取消ONLY_FULL_GROUP_BY参数 sql_mode根据上面查询出的实际更改
mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';                                                                                                                                               
Query OK, 0 rows affected (0.00 sec)
2.设置全局生效 (建议数据库部署完毕就配置此全局配置)

在[mysqld]模块下新增一行配置 (sql_mode根据实际查询出来的结果填写) 修改完成后重启数据库（systemctl restart mysqld）

1
2
3
4
5
# 编辑配置文件设置全局生效
[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 保存后重启数据库
systemctl restart mysqld

image-20230719093717423

验证成功

image-20230719093555460

方式二

使用 any_value() 或 group_concat()

1. any_value()：将分到同一组的数据里第一条数据的指定列值作为返回数据。 （any_value()函数就是MySQL提供的用来抑制ONLY_FULL_GROUP_BY值被拒绝的）
1
2
# sql 语句
select Beijing,any_value(Shanghai) from city group by Beijing
2. group_concat()：将分到同一组的数据默认用逗号隔开作为返回数据
1
2
# sql 语句
select Beijing,group_concat(Shanghai) from city group by Beijing

image-20230719093322644

扩展

1. 现象还原 可用下面的sql语句创库实验

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/*
 Navicat Premium Data Transfer
 
 Source Server         : test
 Source Server Type    : MySQL
 Source Server Version : 50743
 Source Host           : 172.100.70.179:3306
 Source Schema         : test
 
 Target Server Type    : MySQL
 Target Server Version : 50743
 File Encoding         : 65001
 
 Date: 18/07/2023 16:49:21
*/
 
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for city
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city`  (
  `id` int(11) NOT NULL,
  `Beijing` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `Shanghai` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of city
-- ----------------------------
INSERT INTO `city` VALUES (1, '北京', '上海');
INSERT INTO `city` VALUES (2, '武汉', '昆明');
 
SET FOREIGN_KEY_CHECKS = 1;

2. 验证sql语句

1
2
3
4
5
6
SELECT * FROM `city`;
select Beijing,Shanghai from `city`; 
select Beijing,Shanghai from `city` group by Beijing
select Beijing,any_value(Shanghai) from city group by Beijing
select Beijing,group_concat(Shanghai) from city group by Beijing
select Beijing,Shanghai from city group by Beijing,Shanghai