【mysql使用逗号分隔的一行数据转多行数据】

mysql使用逗号分隔的一行数据转多行数据：

方法1：

SELECT
id,
dispatch_number,
SUBSTRING_INDEX( SUBSTRING_INDEX( pipe_batch_no, ‘,’, n ), ‘,’, - 1 ) AS split
FROM
production_dispatch_list,
( SELECT @rownum := @rownum + 1 AS n FROM ( SELECT @rownum := 0 ) r, production_dispatch_list ) x
WHERE
is_deleted=0 and pipe_batch_no is not null and pipe_batch_no != ‘’
AND n <= ( LENGTH( pipe_batch_no ) - LENGTH( REPLACE ( pipe_batch_no, ‘,’, ‘’ ) ) + 1 )

提示：pipe_batch_no 是要拆分的字段

方法2：

SELECT
id,
dispatch_number,
SUBSTRING_INDEX( SUBSTRING_INDEX( u.pipe_batch_no, ‘,’, b.help_topic_id + 1 ), ‘,’, -1 ) AS unicode
FROM
production_dispatch_list u
INNER JOIN mysql.help_topic b ON b.help_topic_id < (
LENGTH( u.pipe_batch_no ) - LENGTH( REPLACE(u.pipe_batch_no,‘,’,‘’) ) + 1
)
where u.pipe_batch_no is not null and u.pipe_batch_no != ‘’

提示：pipe_batch_no 是要拆分的字段