I have to migrate a classic database to my brand new one. Regrettably the man who authored that old database produced an n,n relation utilizing a area with comma separated foreign secrets.

I must write a mysql query (maybe using place into ... choose) that splits individuals comma seperated foreign secrets to ensure that i'm able to develop a table where each row is really a foreign key.

Is possible?

It isn't straightforward to get this done in pure SQL. It will likely be simplest to retrieve each record consequently utilizing a programming language of your liking and place the numerous-to-many join table records in line with the comma separated area. The next pseudo code indicates a strategy which you may 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 have carried this out, the present column holding the comma separated foreign secrets can be taken off.

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