mysql 列转行

mysql中将有规律的列数据分割开多条数据：

原始数据：

将code按 ， 分割然后转行

 SELECT
    * 
FROM
    `test`;
SELECT
    t1.id,
    substring_index( substring_index( t1.`code`, ',', t2.help_topic_id + 1 ), ',',- 1 ) 
FROM
    test t1
    JOIN mysql.help_topic t2 ON t2.help_topic_id < ( length( t1.`code` ) - length( REPLACE ( t1.`code`, ',', '' ))+ 1 ) 
ORDER BY
    t1.id;

 最后数据展示

最后附上表设计:

/*
 Navicat Premium Data Transfer

 Source Server         : local
 Source Server Type    : MySQL
 Source Server Version : 80025
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80025
 File Encoding         : 65001

 Date: 06/09/2021 11:02:12
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `parent_id` int NOT NULL DEFAULT 0,
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_nam`(`name`(4)) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, '哥哥', 0, '1');
INSERT INTO `test` VALUES (2, '56', 1, '1,2');
INSERT INTO `test` VALUES (3, '6456', 2, '1,2,3');
INSERT INTO `test` VALUES (4, 'ggggg', 1, '1,4');
INSERT INTO `test` VALUES (5, '测试canal', 3, '1,2,3,5');

SET FOREIGN_KEY_CHECKS = 1;