MySQL:将逗号分隔的列表分成多行

I have an unnormalized table with a column containing a comma separated list that is a foreign key to another table:

我有一个非规范化的表，其中列包含一个逗号分隔的列表，它是另一个表的外键:

+----------+-------------+   +--------------+-------+
| part_id  | material    |   | material_id  | name  |
+----------+-------------+   +--------------+-------+
|      339 | 1.2mm;1.6mm |   |            1 | 1.2mm |
|      970 | 1.6mm       |   |            2 | 1.6mm |
+----------+-------------+   +--------------+-------+

I want to read this data into a search engine that offers no procedural language.

我想把这些数据读到一个不提供过程语言的搜索引擎中。

So is there a way to either make a join on this column or run a query on this data that inserts appropriate entries into a new table? The resulting data should look like this:

那么，是否有一种方法可以在此列上进行连接，或者对该数据运行查询，将适当的条目插入到新表中?得到的数据应该如下所示:

+---------+-------------+
| part_id | material_id |
+---------+-------------+
|     339 |           1 |
|     339 |           2 |
|     970 |           2 |
+---------+-------------+

I could think of a solution if the DBMS supported functions returning a table but MySQL apparently doesn't.

如果DBMS支持返回表的函数，而MySQL显然不支持，我可以想出一个解决方案。

4 个解决方案

#1

In MySQL this can be achieved as below

在MySQL中，这可以实现如下

SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126) 

+---------------+
| id  | length  |
+---------------+
| 117 | 25      |
| 126 | 8       |
| 148 | 10      |
+---------------+

SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42

+---------------------+
| id  | vehicle_ids   |
+---------------------+
| 42  | 117, 148, 126 |
+---------------------+

Now to get the length of comma separated vehicle_ids use below query

现在要获取由逗号分隔的车载_id的长度，请使用下面的查询

Output

SELECT length 
FROM   vehicles, load_plan_configs   
WHERE  load_plan_configs.id = 42 AND FIND_IN_SET(
       vehicles.id, load_plan_configs.vehicle_ids
)

+---------+
| length  |
+---------+
| 25      |
| 8       |
| 10      |
+---------+

For more info visit http://amitbrothers.blogspot.in/2014/03/mysql-split-comma-separated-list-into.html

更多信息请访问http://amitbrothers s.blogspot.in4/03/sqmyl - spli- commatated -list-into.html

#2

I've answered two similar questions in as many days but not had any responses so I guess people are put off by the use of the cursor but as it should be a one off process I personally dont think that matters.

我已经回答了两个类似的问题，但没有任何回应，所以我猜人们会因为使用光标而感到厌烦，但我个人认为这应该是一个一次性的过程。

As you stated MySQL doesnt support table return types yet so you have little option other than to loop the table and parse the material csv string and generate the appropriate rows for part and material.

如您所述，MySQL还不支持表返回类型，因此除了循环表并解析材料csv字符串并为部分和材料生成适当的行之外，您几乎没有其他选择。

The following posts may prove of interest:

下列员额可能会引起注意:

split keywords for post php mysql

分割关键字后php mysql

MySQL procedure to load data from staging table to other tables. Need to split up multivalue field in the process

将数据从staging表加载到其他表的MySQL过程。需要在过程中分割多值字段

Rgds

祝好

#3

MySQL does not have temporary table reuse and functions do not return rows.

MySQL没有临时表重用，函数也不返回行。

I can't find anything in Stack Overflow to convert string of csv integers into rows so I wrote my own in MySQL.

我在Stack Overflow中找不到任何东西可以把csv整数的字符串转换成行，所以我用MySQL写了我自己的。

DELIMITER $$

DROP PROCEDURE IF EXISTS str_split $$
CREATE PROCEDURE str_split(IN str VARCHAR(4000),IN delim varchar(1))
begin
DECLARE delimIdx int default 0;
DECLARE charIdx int default 1;
DECLARE rest_str varchar(4000) default '';
DECLARE store_str varchar(4000) default '';

create TEMPORARY table IF NOT EXISTS ids as (select  parent_item_id from list_field where 1=0);
truncate table ids;
set @rest_str = str;
set  @delimIdx = LOCATE(delim,@rest_str);
set @charIdx = 1;
set @store_str = SUBSTRING(@rest_str,@charIdx,@delimIdx-1);
set @rest_str = SUBSTRING(@rest_str from @delimIdx+1);

if length(trim(@store_str)) = 0   then
    set @store_str = @rest_str;
end if;    


INSERT INTO ids
SELECT (@store_str + 0);

WHILE @delimIdx <> 0 DO
    set  @delimIdx = LOCATE(delim,@rest_str);
    set @charIdx = 1;
    set @store_str = SUBSTRING(@rest_str,@charIdx,@delimIdx-1);
    set @rest_str = SUBSTRING(@rest_str from @delimIdx+1);

select @store_str;
    if length(trim(@store_str)) = 0   then
        set @store_str = @rest_str;
    end if;    
    INSERT INTO ids(parent_item_id)
    SELECT (@store_str + 0);`enter code here`
END WHILE;

select parent_item_id from ids;
end$$
DELIMITER ;

call str_split('1,2,10,13,14',',')

You will also need to cast to different types if you are not using ints.

如果不使用int类型，还需要对不同类型进行强制转换。

#4

You can also use REGEXP

您还可以使用REGEXP。

SET @materialids=SELECT material FROM parttable where part_id=1;
SELECT * FROM material_id WHERE REGEXP CONCAT('^',@materialids,'$');

This will help if you want to get just one part. Not the whole table of course

如果你只想得到一个部分，这将会有所帮助。当然不是整张桌子。

#1