sql拆分字符串实现一行变多行的实例代码

	WITH RECURSIVE m(id,name,hobbies,str) AS (
	
		SELECT id,name,substr(hobbies,1,instr(hobbies,'、')-1),suubstr(concat(class,'、'),instr(class,'、')+1) 
		FROM movies m
		UNION ALL
		SELECT id,name,substr(str,1,instr(str,'、')-1),suubstr(str,instr(class,'、')+1) 
		FROM m
		WHERE instr(str,'、')>0
	)
	SELECT * FROM m ORDER BY id;

2.3 方式二  

 1 实现思路：我们利用substring_index函数按照指定分隔符截取字符串。

2 substring_index(str,delimiter,number) 返回从字符串 str 的第 number 个出现的分隔符 delimiter 之前的子串。

3 如果 number 是正数，那么就是从左往右，返回第 number 个分隔符的左边的全部内容；number为负，则相反。例如 substring_index(hobbies,'、',2)，获得的结果为：

4 问题：hobbies 中每个字符串的长度是未知的，因此我们如何判断要截取几次？我们应该正向截取还是反向截取？ 

以 id=1 为例：

（1）经过分析可知，反向截取会更容易。

第一次：substring_index('动画','、',-1) --- 得到：动画

第二次：substring_index('动画、剧情','、',-1) --- 得到：剧情

第三次：substring_index('动画、剧情、奇幻','、',-1) --- 得到：奇幻

结束分割。

（2）若想实现上述步骤，需要动态的自动改变字段hobbies的值，将本次得到的数据给截取掉。问题：① 如何动态截取hobbies。② 截取到什么时候结束。

5 解决：

问题一：仍然利用string_index 函数进行截取

substring_index(hobbies, '、', 1) --- '动画'

substring_index(hobbies, '、', 2) --- '动画、剧情'

substring_index(hobbies, '、', 3) --- '动画、剧情、奇幻'

第一次：substring_index(substring_index(hobbies, '、', 1), '、', -1) --- 得到：动画

第二次：substring_index(substring_index(hobbies, '、', 2), '、' ,-1) --- 得到：剧情

第三次：substring_index(substring_index(hobbies, '、', 3), '、' ,-1) --- 得到：奇幻

这里的1、2、3 可以换成临时表（tmp）中的自增id 即： substring_index(substring_index(hobbies, '、', id), '、' ,-1)。

问题二：截取多少次为止？

（1）我们可以看到有几个分隔符，就截取 分隔符数+1次。比如：'动画、剧情、奇幻' 有 2 个分隔符，根据上述描述可以看出需要分割3次。同理 '剧情、爱情' ，有 1 个分隔符，但是需要分割2次。

（2）因此需要截取多少次为止的问题，就转化为了求字符串中有几个分隔符的问题。

（3）解决：利用原始字符串的长度 - 除去分隔符的字符串的长度 = 所有分隔符的长度 = 分隔符个数 * 分隔符所占字节【LENGTH(hobbies)-LENGTH(REPLACE(hobbies, '、', ''))】。注意：中文分隔符占两个字节，因此一般我们可以现将分隔符转为英文字符，之后在进行计算，这样直接得到分隔符个数。即：LENGTH(REPLACE(hobbies, '、', ','))-LENGTH(REPLACE(hobbies, '、', '')) = n。

（4）因此每一行拆分的结束条件：temp.id <LENGTH(hobbies)-LENGTH(REPLACE(hobbies, '、', ''))

6 代码

SELECT 
	m.id,m.name,m.hobbies,
	SUBSTRING_INDEX(SUBSTRING_INDEX(m.hobbies,'、',b.help_topic_id+1),'、',-1) as tmp
		FROM `movies`m
	 inner join mysql.help_topic b
	 on b.help_topic_id<LENGTH(REPLACE(m.hobbies,'、',',')) -LENGTH(REPLACE(m.hobbies,'、','')) +1