MySQL CSV string for multiple rows

Question

I need to transfer the old database to the new one. Unfortunately, the guy who wrote the old database created an n, n relationship using a field with foreign keys separated by a comma.

I would like to write a mysql query (possibly using insert into ... select) that separates these comma-separated foreign keys so that I can build a table in which each row is a foreign key.

Is it possible?

Answer 1

This is not so easy to do in pure SQL. The easiest way is to get each record, in turn, using the programming language of your choice and insert many-for-many table entries based on a comma-separated field. The following pseudo code offers an approach you can 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 do this, the existing column containing the comma-separated foreign keys can be deleted.

Answer 2

My decision

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 $$