MySQL 针对逗号拼接的数据字段转行思路

一、MySQL 针对逗号拼接的数据字段转行思路

在 MySQL 中我们有可能为了方便操作，有时会将一个字段存储多个信息，使用英文逗号隔开，当然这种情况属于对数据库的设计上有些欠妥。但如果遇到了这种情况又需要对数据进行统计的情况就有点棘手了，例如有下面爱好表：

CREATE TABLE `user_hobby` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `hobby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

其中 hobby 字段用来存储用户的爱好，如果多个采用的逗号拼接的方式，其中表中有如下数据：

INSERT INTO `test`.`user_hobby` (`id`, `name`, `hobby`) VALUES (1, '小明', '打篮球,踢足球');
INSERT INTO `test`.`user_hobby` (`id`, `name`, `hobby`) VALUES (2, '小红', '打乒乓球,打篮球,跑步');
INSERT INTO `test`.`user_hobby` (`id`, `name`, `hobby`) VALUES (3, '张三', '打羽毛球,游泳');
INSERT INTO `test`.`user_hobby` (`id`, `name`, `hobby`) VALUES (4, '李四', '跑步');
INSERT INTO `test`.`user_hobby` (`id`, `name`, `hobby`) VALUES (5, '王五', '踢足球,游泳');

下面需要统计分析每个爱好有多少人。

思路：使用 SUBSTRING_INDEX 和 UNION ALL

首先我们可以借助 UNION ALL 生成固定行数的编号，然后利用 SUBSTRING_INDEX 去除固定编号的内容出来：

SELECT u.id, u.name, SUBSTRING_INDEX(SUBSTRING_INDEX(u.hobby, ',', t.n), ',', -1) as hobby
FROM user_hobby u
JOIN (
    SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t
ON CHAR_LENGTH(u.hobby) - CHAR_LENGTH(REPLACE(u.hobby, ',', '')) >= t.n - 1
ORDER BY u.id, u.hobby;

在这里插入图片描述
下面就可以基于上述的结果进行分组统计了：

SELECT hobby,count(*) as total
FROM (
SELECT u.id, u.name, SUBSTRING_INDEX(SUBSTRING_INDEX(u.hobby, ',', t.n), ',', -1) as hobby
FROM user_hobby u
JOIN (
    SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t
ON CHAR_LENGTH(u.hobby) - CHAR_LENGTH(REPLACE(u.hobby, ',', '')) >= t.n - 1
) f GROUP BY hobby
ORDER BY total DESC