MySQL CSV Row to multiple Rows

I need to migrate an old database to my new one. Unfor开发者_JAVA技巧tunately the guy who wrote the old database created an n,n relation using a field with comma separated foreign keys.

I would like to write a mysql query (maybe using insert into ... select) that splits those comma seperated foreign keys so that i can build a table where each row is a foreign key.

Is this possible?

---

It's not straightforward to do this in pure SQL. It will be easiest to retrieve each record in turn using a programming language of your choice and insert the many-to-many join table records based on the comma separated field. The following pseudo code suggests an approach that you might use:

for each (id, csv_foreign_keys) in source_rows do
    foreign_keys = split ',', csv_foreign_keys

    for each fk in foreign_keys do
        insert (id, fk) into many-to-many link table

Once you've done this, the existing column holding the comma separated foreign keys can be removed.

---

My solution

DELIMITER $$

DROP FUNCTION IF EXISTS SPLITCVS $$
DROP PROCEDURE IF EXISTS MIGRATE $$

CREATE FUNCTION SPLITCVS (
 x VARCHAR(255),
 delim VARCHAR(12),
 pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '') $$

CREATE PROCEDURE MIGRATE () 
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE id INT(11);
    DECLARE csv BLOB;
    DECLARE cur CURSOR FOR SELECT uid,foreigns FROM old;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO id, csv;
        IF done THEN
          LEAVE read_loop;
        END IF;

        IF LENGTH(csv) <> 0 THEN
            SET @i = 0;
            SET @seps = LENGTH(csv) - LENGTH(REPLACE(csv, ',', ''));

            IF RIGHT(csv,1) <> ',' THEN
                SET @seps = @seps + 1;
            END IF;

            WHILE @i < @seps DO
                SET @i = @i + 1; 
                INSERT INTO db.newtable(uid_local,uid_foreign)
                VALUES (id,SPLITCVS(csv,',',@i));
            END WHILE;
        END IF;
    END LOOP;

    CLOSE cur;
END $$

CALL MIGRATE() $$

DROP FUNCTION SPLITCVS $$
DROP PROCEDURE MIGRATE $$