mysql一条记录split拆多条,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

9

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 |

+---------+

更多信息請訪問http://amitbrothers s.blogspot.in4/03/sqmyl - spli- commatated -list-into.html

#2

5

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:

下列員額可能會引起注意:

分割關鍵字后php mysql

將數據從staging表加載到其他表的MySQL過程。需要在過程中分割多值字段

Rgds

祝好

#3

1

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

0

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

如果你只想得到一個部分，這將會有所幫助。當然不是整張桌子。