mySql将逗号分隔字段分隔为多行、mysql将某字符串分隔为多行数据

一、MySql将逗号分隔字段分隔为多行

分隔前数据样式

 

 语句实现：

SELECT a.ControlKey,
substring_index( substring_index( a.serialport, '|', b.help_topic_id + 1 ), '|',- 1 ) serialport
FROM tb_syschannel a
JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.serialport ) - length( REPLACE ( a.serialport, '|', '' ) ) + 1 ) 
ORDER BY a.ControlKey

实现效果：

 

 二、mysql将某字符串分隔为多行数据

SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM 
mysql.help_topic 
WHERE 
help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

实现效果：