MySQL中的逗号分隔值"IN"条款 [英] Comma separated values in MySQL "IN" clause

问题描述

我的一个表中有一列，其中存储了多个用逗号分隔的ID. 有没有一种方法可以在查询的"IN"子句中使用此列的值.

I have a column in one of my table where I store multiple ids seperated by comma's. Is there a way in which I can use this column's value in the "IN" clause of a query.

列(city)的值类似于6,7,8,16,21,2

我需要用作

select * from table where e_ID in (Select city from locations where e_Id=?)

我对Crozin的回​​答很满意，但是我愿意接受建议，观点和选择.

I am satisfied with Crozin's answer, but I am open to suggestions, views and options.

随时分享您的观点.

推荐答案

以@Jeremy Smith的FIND_IN_SET()示例为基础，您可以使用联接来实现，因此不必运行子查询.

Building on the FIND_IN_SET() example from @Jeremy Smith, you can do it with a join so you don't have to run a subquery.

SELECT * FROM table t
JOIN locations l ON FIND_IN_SET(t.e_ID, l.city) > 0
WHERE l.e_ID = ?

由于执行表扫描，对于table和locations中的每个行组合，需要评估FIND_IN_SET()函数，因此已知执行效果非常差.它无法利用索引，也无法对其进行改进.

This is known to perform very poorly, since it has to do table-scans, evaluating the FIND_IN_SET() function for every combination of rows in table and locations. It cannot make use of an index, and there's no way to improve it.

我知道您说过您想尽最大努力利用不良的数据库设计，但是您必须了解这有多么严重.

I know you said you are trying to make the best of a bad database design, but you must understand just how drastically bad this is.

说明:假设我要您查找电话簿中名字开头，中间或最后一个字母为"J"的所有人.在这种情况下，这本书的排序顺序没有任何帮助，因为无论如何您都必须扫描每一页.

Explanation: Suppose I were to ask you to look up everyone in a telephone book whose first, middle, or last initial is "J." There's no way the sorted order of the book helps in this case, since you have to scan every single page anyway.

@fthiella提供的LIKE解决方案在性能方面也存在类似的问题.无法索引.

The LIKE solution given by @fthiella has a similar problem with regards to performance. It cannot be indexed.

另请参阅我对

Also see my answer to Is storing a delimited list in a database column really that bad? for other pitfalls of this way of storing denormalized data.

如果您可以创建一个补充表来存储索引，则可以将位置映射到城市列表中的每个条目:

If you can create a supplementary table to store an index, you can map the locations to each entry in the city list:

CREATE TABLE location2city (
 location INT,
 city INT,
 PRIMARY KEY (location, city)
); 

假设您有一个针对所有可能城市的查找表(不仅仅是在table中提到的城市)，那么您可以忍受一次效率低下以生成映射的情况:

Assuming you have a lookup table for all possible cities (not just those mentioned in the table) you can bear the inefficiency one time to produce the mapping:

INSERT INTO location2city (location, city)
  SELECT l.e_ID, c.e_ID FROM cities c JOIN locations l
  ON FIND_IN_SET(c.e_ID, l.city) > 0;

现在，您可以运行效率更高的查询来查找table中的条目:

Now you can run a much more efficient query to find entries in your table:

SELECT * FROM location2city l
JOIN table t ON t.e_ID = l.city
WHERE l.e_ID = ?;

这可以利用索引.现在，您只需要注意locations中任何对行的INSERT/UPDATE/DELETE都会在location2city中插入相应的映射行.

This can make use of an index. Now you just need to take care that any INSERT/UPDATE/DELETE of rows in locations also inserts the corresponding mapping rows in location2city.

这篇关于MySQL中的逗号分隔值"IN"条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！