mysql插入部分值,mysql：拆分varchar值并插入部分

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;