I have a denormalised records in my table:
ID, CODES
1 |1|2|3|4
2 |5|6|7|8
In second column there are int values, saved in varchar field separated by | symbol.
I want to convert them to normal Many2Many relational form, using link table.
So I want to create a table like this
ID CODE
1 1
1 2
1 3
1 4
....
2 8
I understand that I can iterate through the records in mysql stored function, split string and insert value. But I am interested: is it possible to convert data this way without stored procedure/function, but using only query (create table ... select ...)?
Thanks.
UPD: There is variable number of codes in different rows. Each line has from 1 to 15 codes.
解决方案
Here's how it works, inclusive test data and so on.
But consider that this is just a fun answer. The way to go is clearly a stored procedure or a function or whatever.
drop table testvar;
create table testvar (id int, codes varchar(20));
insert into testvar values (1, '|1|2|3|4'), (2, '|5|6|7|8');
drop table if exists inserttest;
create table inserttest (id int, code int);
select @sql:=left(concat('insert into inserttest values ', group_concat( '(', id, ',', replace(right(codes, length(codes) - 1), '|', concat( '),(', id, ',' )), '),' separator '')), length(concat('insert into inserttest values ', group_concat( '(', id, ',', replace(right(codes, length(codes) - 1), '|', concat( '),(', id, ',' )), '),' separator ''))) -1)
from testvar;
prepare stmt1 from @sql;
execute stmt1;
select * from inserttest;