mysql单字段包含多值列转行问题

有些时候因为表设计问题，会存在多个值以逗号隔开存在一个字段里，例如：

这样在一些特定的场合会造成查询困难，我们需要把他列转行，主要利用substring_index函数进行分割，以下列子借助mysql自带的表help_topic 的id加以辅助查询，其中content_blog_article 和content_blog_note 为业务数据表：

SELECT
	*
from (

		(
			-- 文章博客
				SELECT * from
				(
						select a.id,substring_index(substring_index(a.tag,',',b.help_topic_id+1),',',-1) as tag, a.create_time as createTime
						from 
						content_blog_article a
						join
						mysql.help_topic b
						on b.help_topic_id < (length(a.tag) - length(replace(a.tag,',',''))+1)
				) as result
				where tag in (select tag from content_tag where type = "WHDK")
		)
		UNION		
		(
			-- 相册博客
				SELECT * from
				(
						select a.id,substring_index(substring_index(a.tag,',',b.help_topic_id+1),',',-1) as tag, a.create_time as createTime
						from 
						content_blog_note a
						join
						mysql.help_topic b
						on b.help_topic_id < (length(a.tag) - length(replace(a.tag,',',''))+1)
				) as result
				where tag in (select tag from content_tag where type = "WHDK")
		)
) as result
order by createTime desc

最终效果，把id为“03dfe53867b91b6bef83b7f2bdb95e4a”的记录的tag字段以逗号隔开的值转成列了：