mysql:拆分varchar值并插入部分 [英] mysql: split varchar value and insert parts

问题描述

我的表中有非规范化记录:

I have a denormalised records in my table:

ID, CODES
1   |1|2|3|4
2   |5|6|7|8

第二列中有int值，保存在varchar字段中，由|分隔.象征. 我想使用链接表将它们转换为普通的Many2Many关系形式. 所以我想创建一个这样的表

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

我知道我可以遍历mysql存储函数中的记录，拆分字符串和插入值.但是我很感兴趣:是否可以通过这种方式不使用存储过程/函数来转换数据，而只使用查询(创建表...选择...)? 谢谢.

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:在不同的行中有可变数量的代码.每行有1到15个代码.

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;

这篇关于mysql:拆分varchar值并插入部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！