MySQL一行拆分为多行（根据特定符号分割）

---

一、创建临时表tmp_help_topic

临时表tmp_help_topic：

DROP TABLE IF EXISTS tmp_help_topic;
CREATE TABLE IF NOT EXISTS tmp_help_topic (
help_topic_id bigint(20) NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (help_topic_id)
);
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();

二、创建测试表test，插入测试数据

测试数据：

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) DEFAULT NULL,
`num` int(8),
PRIMARY KEY (`id`)
);
INSERT INTO `test`(`name`, `num`) VALUES ('a1,b258,c', 11);
INSERT INTO `test`(`name`, `num`) VALUES ('f,g123456,h,i85,j', 33);
INSERT INTO `test`(`name`, `num`) VALUES ('d,e1234', 22);

三、MySQL一行拆分为多行（根据特定符号分割）

SELECT a.id
      ,a.num
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) 
  FROM test a
  JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );