将该表的name字段按照"/"拆分成多行,执行SQL(注意每个数据的最后一个字符必须是当前分割的"/"):
WITH recursive CTE_recursive AS(
SELECT
REPLACE(SUBSTRING(`name`,1,INStr(`name`,'/')),'/','') AS dp,
SUBSTRING(`name`,INStr(`name`,'/')+1,LENGTH(`name`)) AS `name`,
`id`
FROM my_temp
UNION ALL
SELECT
REPLACE(SUBSTRING(cte.`name`,1,INStr(cte.`name`,'/')),'/','') AS dp,
SUBSTRING(cte.`name`,INStr(cte.`name`,'/')+1,LENGTH(cte.`name`)) AS `name`,
cte.`id`
FROM CTE_recursive cte
INNER JOIN my_temp t1 ON cte.id = t1.id
WHERE INStr(cte.`name`,'/') > 0
)
SELECT * FROM CTE_recursive;
结果如图所示: