MySQL：将逗号分隔的列表拆分为多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3936088/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Split comma separated list into multiple rows
提问by AndreKR
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 eithermake a join on this column orrun 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 显然不支持，我可以想到一个解决方案。
采纳答案by Amit
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
现在要获取逗号分隔的 Vehicle_ids 的长度，请使用以下查询
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
回答by Jon Black
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
将数据从临时表加载到其他表的 MySQL 过程。过程中需要拆分多值字段
Rgds
Rgds
回答by shrikeac
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);
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.
如果您不使用整数，您还需要转换为不同的类型。
回答by Matthias
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
如果您只想获得一份，这将有所帮助。当然不是整张桌子
