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

Question

我有一张未经规范化的表格，其中一个列包含一个逗号分隔的列表，这个列表是指向另一个表的外键：

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

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

那么有没有一种方法可以在此列上进行连接，或者运行一个查询，在新表中插入适当的条目？ 最终的数据应该如下所示：

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

如果DBMS支持返回表的函数，我可以想出一个解决方案，但是MySQL似乎不支持。

- AndreKR

---

这是我应该做的方式，评论在这里是因为我看到了对此帖子的编辑，然后你可以简单地加入之后。 - Raymond Nijland

Answer 1

在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 |
+---------------------+

现在要获取逗号分隔的车辆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      |
+---------+

欲了解更多信息请访问http://amitbrothers.blogspot.in/2014/03/mysql-split-comma-separated-list-into.html

- Amit

---

非常有帮助，我正在处理一个包含CSV数据的数据库，其中某些列也是如此。 - lintuxvi

Answer 2

我在两天内回答了两个类似的问题，但没有得到任何回应，所以我猜人们可能会因为使用游标而感到困惑，但由于它应该是一个一次性的过程，我个人认为这并不重要。

正如你所说，MySQL目前不支持表返回类型，因此您别无选择，只能循环表并解析材料csv字符串，并为零件和材料生成适当的行。

以下帖子可能会引起兴趣：

分割关键字以进行php mysql发布

MySQL过程从暂存表加载数据到其他表。需要在此过程中拆分多值字段

Rgds

- Jon Black

---

不确定是否可以在没有循环的情况下完成，除非有预设的逗号分隔值中可以存在的最大数量...否则你被迫使用循环。 - Twelfth

是的，你必须循环 - 如所述。 - Jon Black

这并不是最好的解决方案，因为它将问题从集合理论级别转化为过程级别，因此只能用作准备工作，而不能实时使用（回答了我的“或”问题，但不是我的“任一”问题），但至少它可以工作。谢谢 :) - AndreKR

这就是因果关系啊 - 糟糕的数据库设计导致了糟糕的过程性修复 :P - Jon Black

嘿，说得好f00。@AndreKR - 它是单列中的csv字段，不幸的是你提出的问题从未在集合理论层面上存在过。你得到了一个程序性问题的程序性答案。我想如果那个csv列中的值有限制（最多10个？），你可能可以采用基于集合的解决方案。也许需要规范化您的数据库... - Twelfth

Answer 3

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);
END WHILE;

select parent_item_id from ids;
end$$
DELIMITER ;

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

如果您没有使用整数，您还需要进行类型转换。

- shrikeac

Answer 4

你也可以使用正则表达式(RE的缩写)。

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

如果您只想获取其中的一部分，这将会有所帮助。当然，并非整个表格。

- Matthias

---

谢谢你的回答，但我认为你的SQL毫无意义。 :) - AndreKR