mysql 字符 拆分,Mysql字符串拆分

I have one string column and one comma separated column as below

Example,

I have the table,

Col1 | col2

1 | a,b,c

2 | d,e

From above, I want

Col1,Col2

1 ,a

1 ,b

1 ,c

2 ,d

2 ,e

How to achieve this

It is not the Exact Duplicate.

解决方案

You can do this using a stored procedure

DELIMITER $$

CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER)

RETURNS VARCHAR(65000)

BEGIN

DECLARE output VARCHAR(65000);

SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)

, LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)

, delim

, '');

IF output = '' THEN SET output = null; END IF;

RETURN output;

END $$

CREATE PROCEDURE BadTableToGoodTable()

BEGIN

DECLARE i INTEGER;

SET i = 1;

REPEAT

INSERT INTO GoodTable (col1, col2)

SELECT col1, strSplit(col2, ',', i) FROM BadTable

WHERE strSplit(col2, ',', i) IS NOT NULL;

SET i = i + 1;

UNTIL ROW_COUNT() = 0

END REPEAT;

END $$

DELIMITER ;