mysql中对某个字段按某个字符分割，并转成行数据

1.背景

        在开发过程中碰到一个项目号包含多个供应商，但其表结构和存储如下：

        表结构 |--------item_num------|------item_name-------|---------relation_supplier---------|

其中relation_suppler 存储的是供应商id，多个用','号隔开。

现在有个需求就是我需要写一个接口在前端做选择，类似一个下面的表格

项目	供应商
编号001-湖南xx项目	供应商1
编号001-湖南xx项目	供应商2
编号001-湖南xx项目	供应商3

即需要将表中的relation_supplier 字段按 ',' 号隔开并与原记录的项目组成一行记录。

SQL 实现1

select
	t.sap_item_num ,
	t.item_name ,
	t.whether_forbidden ,
	replace(
 replace( 
	replace(substring_index(substring_index(t.relation_supplier, ',', a.rownum), ',' ,- 1), '[', '') 
 		, ']', '')
 		, '"', '')
		as relation_supplier
from
	project_information_settings t,
	(
	select
			@rownum := @rownum + 1 as rownum
	from
			project_information_settings m,
			(
		select
			@rownum := 0) n
	) a
where
	t.is_delete = 'undelete'
	and a.rownum <= (
	length(t.relation_supplier) - length(
		replace (t.relation_supplier, ',', '')
	) + 1
)

上面 replace部分只是将原字段中被','号分割后存在的 [ ," ,] 号去掉，在mysql 8中可以用函数 regex_replace, 本人所用mysql版本较低，所以用了多个replace。

另一种写法

select
	pis.sap_item_num ,
	pis.item_name,
	pis.whether_forbidden,
	replace(
 replace(
 replace(
	substring_index(substring_index(pis.relation_supplier , ',', b.help_topic_id + 1), ',',-1)
	,'[','')
	, ']', '')
 		, '"', '')
	as supplier_id
from
	project_information_settings pis
join mysql.help_topic b 
on
	b.help_topic_id <(length(pis.relation_supplier) - length(replace(pis.relation_supplier, ',', '')) + 1)
 where pis.is_delete = 'undelete'
order by
	pis.id_

其中 mysql.help_topic 表示mysql中存在的表 , is_delete 是我业务表的一个是否删除的标志字段

效果