关于 group_concat() 的问题

Question

我在使用 group_concat() 的时候遇到了问题，我的需求是，有一部电影，该电影关联了两个分类，那么左关联查询的时候，会出现两条记录。
我使用 group_concat() 想让两行分类数据合并成一行数据。# 一、首先上测试的表结构和数据的 sql 吧 Navicat Premium Data Transfer Source Server         : 本机
 Source Server Type    : MySQL
 Source Server Version : 80012
 Source Host           : localhost:3306
 Source Schema         : test Target Server Type    : MySQL
 Target Server Version : 80012
 File Encoding         : 65001 Date: 11/03/2019 23:26:12
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for actor
-- ----------------------------
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `actor_id` int(11) NOT NULL AUTO_INCREMENT,
  `actor_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '演员名称',
  PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COMMENT='演员表';-- ----------------------------
-- Records of actor
-- ----------------------------
BEGIN;
INSERT INTO `actor` VALUES (4, '古月方源');
INSERT INTO `actor` VALUES (5, '凤九歌');
INSERT INTO `actor` VALUES (6, '吴帅');
INSERT INTO `actor` VALUES (8, '龙公');
INSERT INTO `actor` VALUES (9, '星宿仙尊');
COMMIT;-- ----------------------------
-- Table structure for genre
-- ----------------------------
DROP TABLE IF EXISTS `genre`;
CREATE TABLE `genre` (
  `genre_id` int(11) NOT NULL AUTO_INCREMENT,
  `genre_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`genre_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;-- ----------------------------
-- Records of genre
-- ----------------------------
BEGIN;
INSERT INTO `genre` VALUES (1, '科幻');
INSERT INTO `genre` VALUES (2, '剧情');
INSERT INTO `genre` VALUES (3, '喜剧');
INSERT INTO `genre` VALUES (4, '恐怖');
COMMIT;-- ----------------------------
-- Table structure for movie
-- ----------------------------
DROP TABLE IF EXISTS `movie`;
CREATE TABLE `movie` (
  `movie_id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`movie_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=156 DEFAULT CHARSET=utf8 COMMENT='电影表';-- ----------------------------
-- Records of movie
-- ----------------------------
BEGIN;
INSERT INTO `movie` VALUES (1, '蛊真人');
INSERT INTO `movie` VALUES (2, '教父');
INSERT INTO `movie` VALUES (3, '七武士');
INSERT INTO `movie` VALUES (4, '天堂电影院');
COMMIT;-- ----------------------------
-- Table structure for movie_actor
-- ----------------------------
DROP TABLE IF EXISTS `movie_actor`;
CREATE TABLE `movie_actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_id` int(11) NOT NULL DEFAULT '0' COMMENT '电影id',
  `actor_id` int(11) NOT NULL COMMENT '演员id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='电影、演员中间表';-- ----------------------------
-- Records of movie_actor
-- ----------------------------
BEGIN;
INSERT INTO `movie_actor` VALUES (1, 1, 4);
INSERT INTO `movie_actor` VALUES (2, 1, 5);
INSERT INTO `movie_actor` VALUES (3, 1, 5);
INSERT INTO `movie_actor` VALUES (4, 1, 8);
INSERT INTO `movie_actor` VALUES (5, 1, 9);
INSERT INTO `movie_actor` VALUES (6, 2, 3);
INSERT INTO `movie_actor` VALUES (7, 3, 5);
INSERT INTO `movie_actor` VALUES (8, 2, 9);
INSERT INTO `movie_actor` VALUES (9, 3, 8);
INSERT INTO `movie_actor` VALUES (10, 3, 9);
INSERT INTO `movie_actor` VALUES (11, 4, 8);
INSERT INTO `movie_actor` VALUES (12, 5, 4);
COMMIT;-- ----------------------------
-- Table structure for movie_genre
-- ----------------------------
DROP TABLE IF EXISTS `movie_genre`;
CREATE TABLE `movie_genre` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_id` int(11) NOT NULL DEFAULT '0' COMMENT '电影id',
  `genre_id` int(11) NOT NULL DEFAULT '0' COMMENT '分类id',
  PRIMARY KEY (`id`),
  KEY `asset_id` (`movie_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='电影、分类中间表';-- ----------------------------
-- Records of movie_genre
-- ----------------------------
BEGIN;
INSERT INTO `movie_genre` VALUES (1, 1, 2);
INSERT INTO `movie_genre` VALUES (2, 1, 4);
INSERT INTO `movie_genre` VALUES (3, 2, 2);
INSERT INTO `movie_genre` VALUES (4, 3, 2);
INSERT INTO `movie_genre` VALUES (5, 3, 4);
INSERT INTO `movie_genre` VALUES (6, 3, 3);
INSERT INTO `movie_genre` VALUES (7, 4, 2);
INSERT INTO `movie_genre` VALUES (8, 4, 3);
COMMIT;SET FOREIGN_KEY_CHECKS = 1;# 二、重现一下我的操作## 1、查询电影和分类
## 2、将分类合并成一行这一步应该也没什么问题。## 3、再加入一列这里就出问题了。求大神帮忙解答一下。
# 三、还有一个问题
还有一个问题就是，如上面的 sql, 在新版的 MySQL 中可能会出现如下错误：
是因为我的 SQL 写的有问题吗？

Answer 1

你这中连接肯定会出现这个问题的，两个结果集的记录数不一样，他会已最多的来匹配，所以出现上面的结果， 你需要用两个查询分别   group_concat()  , 然后按movie_id来合并

Answer 2

-- 你试试这个语句
select * 
from (
select 
m.movid_id , 
m.movid_name , 
group_concat(g.genre_name) genre_name
FROM 
movie m
left join movie_genre mg on mg.movie_id = m.movie_id 
left join genre g on g.genre_id = mg.genre_id 
group by m.movie_id
) a join 
(
select 
m.movid_id , 
m.movid_name , 
group_concat(g.actor_name) actor_name
FROM 
movie m
left join movie_actor ma on mg.movie_id = m.movie_id 
left join actor a on g.actor_id = mg.actor_id 
group by m.movie_id
) b  on a.movid_id = b.movid_id

Answer 3

“MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。”
这个是mysql的配置文件的一个参数（sql_mode）控制的，在mysql 5.7以上的版本做过修改，默认是（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
），如果出现group by， 则分组的字段必须出现在查询的显示字段里面（也就是select 的后面的字段），需要把 sql_mode参数重新赋值；
操作如下：
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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2，去掉ONLY_FULL_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';
3，第二步是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：
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';执行完成之后，重启数据库即可；