需求：将表
| id | column |
|---|---|
| 1 | A,B,C |
| 2 | D,E |
转为表
| id | column |
|---|---|
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | D |
| 2 | E |
新建表：test
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`c` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test` VALUES ('1', 'A,B,C');
INSERT INTO `test` VALUES ('2', 'D,E');
新建表：seq
DROP TABLE IF EXISTS `seq`;
CREATE TABLE `seq` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `seq` VALUES (1);
INSERT INTO `seq` VALUES (2);
INSERT INTO `seq` VALUES (3);
INSERT INTO `seq` VALUES (4);
如图所示：
seq.png
test.png
运行sql
SELECT
t.id,
SUBSTRING_INDEX( SUBSTRING_INDEX( t.c, ',', s.id ), ',', - 1 )
FROM
test t
JOIN seq s ON s.id <= ( LENGTH( t.c ) - LENGTH( REPLACE ( t.c, ',', '' ) ) + 1 )
可得
结果.png
