MySQL通过查找将2个逗号分隔的列拆分为多行

Question

我有一个表，其中两个列的值用逗号分隔，还有另一个类别表。组_1可以具有最多30个分离值，组_2可以具有最多5个分离值

myProducts

uk，我们[…][/td>uk，[…][/tr>西班牙、德国、[…]

别名	group_1	group_2
product_a	1,2,3，[…]
product_b	2,4，[…]
product_c	1,4，[…]

是的，但您不会对性能感到满意。

您可以使用MySQL的FIND_IN_SET((函数将逗号分隔的列表与单个值进行匹配。

select p.alias, a.category as group_1, c.country as group_2 
from myProducts p join myCategories a on find_in_set(a.id, p.group_1) 
join countries c on find_in_set(c.country, p.group_2);
+-----------+------------+---------+
| alias     | group_1    | group_2 |
+-----------+------------+---------+
| product_c | category_a | spain   |
| product_c | category_d | spain   |
| product_a | category_a | uk      |
| product_a | category_b | uk      |
| product_b | category_b | uk      |
| product_a | category_c | uk      |
| product_b | category_d | uk      |
+-----------+------------+---------+

我确实创建了另一个查找表countries:

create table countries (country varchar(20) primary key);
insert into countries values ('uk'),('us'),('spain'),('germany');

注意：如果逗号分隔的列表中有空格，它们将被视为列表中每个字符串的一部分，因此您需要删除空格。

select p.alias, a.category as group_1, c.country as group_2 
from myProducts p join myCategories a on find_in_set(a.id, p.group_1) 
join countries c on find_in_set(c.country, replace(p.group_2,' ',''));
+-----------+------------+---------+
| alias     | group_1    | group_2 |
+-----------+------------+---------+
| product_c | category_a | germany |
| product_c | category_d | germany |
| product_c | category_a | spain   |
| product_c | category_d | spain   |
| product_a | category_a | uk      |
| product_a | category_b | uk      |
| product_b | category_b | uk      |
| product_a | category_c | uk      |
| product_b | category_d | uk      |
| product_b | category_b | us      |
| product_b | category_d | us      |
+-----------+------------+---------+

但是，如果这样做，就无法优化使用索引的查找。因此，每次连接都将是一次表扫描。随着表越来越大，您会发现性能下降到无法使用的地步。

对此进行优化的方法是避免使用逗号分隔的列表。将多对多关系规范化为新表。然后，查找可以使用索引，除了使用逗号分隔列表的所有其他问题外，还可以避免性能下降。

---

回复您的评论：

您可以通过明确列出国家/地区来创建派生表：

FROM ...
JOIN (
SELECT 'us' AS country UNION SELECT 'uk' UNION SELECT 'spain' UNION SELECT 'germany'
) AS c

但这越来越荒谬了。您使用SQL没有任何好处。您还可以将整个数据集提取回客户端应用程序，并将其排序为内存中的一些数据结构。