在MySQL“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.

我的一个表中有一列，其中存储了用逗号分隔的多个id。是否有一种方法可以在查询的“in”子句中使用该列的值。

The column(city) has values like 6,7,8,16,21,2

列(城市)有6、7、8、16、21、2的值。

I need to use as

我需要用as

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

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

我对克罗津的回答感到满意，但我愿意接受建议、观点和选择。

Feel free to share your views.

请随意分享你的观点。

10 个解决方案

#1

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.

在@Jeremy Smith的FIND_IN_SET()示例的基础上，可以使用一个连接来完成，这样就不必运行子查询。

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

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.

这是众所周知的，因为它必须做表扫描，对表和位置的每一行的组合进行FIND_IN_SET()函数的评估。它不能使用索引，也没有办法改进它。

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.

我知道您曾说过您正在试图充分利用一个糟糕的数据库设计，但是您必须了解这有多么糟糕。

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.

解释:假设我让你在电话本中查找每个人的名字，他们的第一、中间或最后一个字母是“j”。这本书的排序顺序在这种情况下是没有帮助的，因为你必须扫描每一页。

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

@fthiella给出的类似解决方案在性能方面也有类似的问题。它不能被索引。

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;

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 = ?;

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.

这可以利用索引。现在您只需要注意，在位置上的任何插入/更新/删除行也会在location2city中插入相应的映射行。

#2

From MySQL's point of view you're not storing multiple ids separated by comma - you're storing a text value, which has the exact same meaing as "Hello World" or "I like cakes!" - i.e. it doesn't have any meaing.

从MySQL的角度来看，您并没有存储用逗号分隔的多个id，而是存储一个文本值，这个值与“Hello World”或“I like cake”的发音完全相同。-也就是说它没有任何叫声。

What you have to do is to create a separated table that will link two objects from the database together. Read more about many-to-many or one-to-many (depending on your requirements) relationships in SQL-based databases.

您需要做的是创建一个分隔的表，将两个对象从数据库连接起来。阅读更多关于基于sql的数据库中的多对多关系或一对多关系(取决于您的需求)的信息。

#3

Rather than use IN on your query, use FIND_IN_SET (docs):

不要在查询中使用FIND_IN_SET (docs):

SELECT * FROM table 
WHERE 0 < FIND_IN_SET(e_ID, (
             SELECT city FROM locations WHERE e_ID=?))

The usual caveats about first form normalization apply (the database shouldn't store multiple values in a single column), but if you're stuck with it, then the above statement should help.

关于第一种形式规范化的常见注意事项(数据库不应该在一个列中存储多个值)，但是如果您遇到这种情况，那么上面的语句应该会有所帮助。

#4

This does not use IN clause, but it should do what you need:

这在子句中不使用，但它应该做您需要的:

Select *
from table
where
  CONCAT(',', (Select city from locations where e_Id=?), ',')
  LIKE
  CONCAT('%,', e_ID, ',%')

but you have to make sure that e_ID does not contain any commas or any jolly character.

但是您必须确保e_ID不包含任何逗号或任何有趣的字符。

e.g.

如。

CONCAT(',', '6,7,8,16,21,2', ',') returns ',6,7,8,16,21,2,'

e_ID=1  --> ',6,7,8,16,21,2,' LIKE '%,1,%'  ? FALSE
e_ID=6  --> ',6,7,8,16,21,2,' LIKE '%,6,%'  ? TRUE
e_ID=21 --> ',6,7,8,16,21,2,' LIKE '%,21,%' ? TRUE
e_ID=2  --> ',6,7,8,16,21,2,' LIKE '%,2,%'  ? TRUE
e_ID=3  --> ',6,7,8,16,21,2,' LIKE '%,3,%'  ? FALSE
etc.

#5

this one in for oracle ..here string concatenation is done by wm_concat

这是甲骨文公司的。这里的字符串连接由wm_concat完成。

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

yes i agree with raheel shan .. in order put this "in" clause we need to make that column into row below code one do that job.

是的，我同意她的观点。为了将这个“in”子句放入代码1下面的行中，我们需要使该列完成该工作。

select * from table  where to_char(e_ID) 
in (
  select substr(city,instr(city,',',1,rownum)+1,instr(city,',',1,rownum+1)-instr(city,',',1,rownum)-1) from 
  (
  select ','||WM_CONCAT(city)||',' city,length(WM_CONCAT(city))-length(replace(WM_CONCAT(city),','))+1 CNT from locations where e_Id=? ) TST 
  ,ALL_OBJECTS OBJ where TST.CNT>=rownum
    ) ;

#6

you should use

你应该使用

FIND_IN_SET Returns position of value in string of comma-separated values

FIND_IN_SET返回值在逗号分隔值字符串中的位置

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
    -> 2

#7

Don't know if this is what you want to accomplish. With MySQL there is feature to concatenate values from a group GROUP_CONCAT

不知道这是不是你想要完成的。使用MySQL，可以将GROUP_CONCAT中的值串联起来

You can try something like this:

你可以试试这样的方法:

select * from table where e_ID in (Select GROUP_CONCAT(city SEPARATOR ',') from locations where e_Id=?)

#8

IN takes rows so taking comma seperated column for search will not do what you want but if you provide data like this ('1','2','3') this will work but you can not save data like this in your field whatever you insert in the column it will take the whole thing as a string.

在需要行所以逗号分离列搜索不会做你想做的事,但如果你这样提供数据(' 1 ',' 2 ',' 3 ')这将工作,但你不能这样的数据保存在你的领域你插入的列将整件事作为字符串。

#9

You need to "SPLIT" the city column values. It will be like:

您需要“拆分”city列的值。这将是:

SELECT *
  FROM table
 WHERE e_ID IN (SELECT TO_NUMBER(
                                 SPLIT_STR(city /*string*/
                                           , ',' /*delimiter*/
                                           , 1 /*start_position*/
                                           )
                                 )
                  FROM locations);

You can read more about the MySQL split_str function here: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

您可以在这里阅读更多关于MySQL split_str函数的信息:http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

Also, I have used the TO_NUMBER function of Oracle here. Please replace it with a proper MySQL function.

此外，我在这里使用了Oracle的TO_NUMBER函数。请用合适的MySQL函数替换。

#10

You can create a prepared statement dynamically like this

您可以像这样动态地创建一个预先准备好的语句。

set @sql = concat('select * from city where city_id in (',
                  (select cities from location where location_id = 3),
                  ')');
prepare in_stmt from @sql;
execute in_stmt;
deallocate prepare in_stmt;

#1