mysql查询逗号分隔的数据

表结构

# 用户表
CREATE TABLE `user` (
  `user_id` varchar(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `hobby_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

#爱好表
CREATE TABLE `hobby` (
  `hobby_id` varchar(4) NOT NULL,
  `hobby_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`hobby_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据

insert into user(user_id, name, hobby_id) values(UUID(), "张三", "1,2,3"),(UUID(), "李四", "1,2");

insert into hobby(hobby_id, hobby_name) values("1", "唱"),("2", "跳"),("3","rap");

将一个逗号分隔的字段转成多行记录

SELECT
    u.user_id AS userId,
    u.NAME AS userName,
    h.hobby_id AS hobbyId,
    h.hobby_name as hobbyName,
    u.hobby_id as uHobbyId
FROM
    education.user u
    LEFT JOIN education.hobby h ON FIND_IN_SET( h.hobby_id, u.hobby_id ) != 0
    group by u.user_id, h.hobby_id
    order by u.user_id ASC, h.hobby_id ASC;

• find_in_set函数会根据字段里面的逗号分隔，所以我们存储的字段里面，最好用逗号分隔，如果内容也存在逗号，最好是采用主键关联，避免逗号影响结果。

合并字段

SELECT
    u.user_id AS userId,
    u.NAME AS userName,
    u.hobby_id as hobbyId,
    GROUP_CONCAT(h.hobby_name ORDER BY h.hobby_id ASC SEPARATOR '|') AS hobbyName
    
FROM
    education.user u
    LEFT JOIN education.hobby h ON FIND_IN_SET( h.hobby_id, u.hobby_id ) != 0
    group by u.user_id;