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