mysql列转行

需求：将表

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

mysql列转行

推荐阅读更多精彩内容