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 和 中的每个 行组合评估 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)
); 

假设你有一个所有可能城市的查找表(不仅仅是表中提到的那些)，你可以忍受一次生成映射的低效率:

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屋！