MySQL:将逗号分隔的列表分成多行 [英] MySQL: Split comma separated list into multiple rows
问题描述
我有一个未规范化的表,该表的列包含一个用逗号分隔的列表,该列表是另一个表的外键:
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 |
+---------+-------------+
我想出了一个解决方案,如果DBMS支持的函数返回一个表,但是MySQL显然没有.
I could think of a solution if the DBMS supported functions returning a table but MySQL apparently doesn't.
推荐答案
在MySQL中,可以通过以下方式实现
In MySQL this can be achieved as below
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 |
+---------------------+
现在要获取逗号分隔的vehicle_ids的长度,请在下面的查询中使用
Now to get the length of comma separated vehicle_ids use below query
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
这篇关于MySQL:将逗号分隔的列表分成多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!