php MySQL“IN”子句中的逗号分隔值

Comma separated values in MySQL "IN" clause

提问by Sashi Kant

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

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

I need to use 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.

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

Feel free to share your views.

随意分享您的观点。

回答by Bill Karwin

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 everycombination of rows in tableand locations. It cannot make use of an index, and there's no way to improve it.

这是众所周知的非常表现不佳，因为它做表扫描，评估FIND_IN_SET（）函数每一行的组合table和locations。它不能使用索引，也没有办法改进它。

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 LIKEsolution given by @fthiella has a similar problem with regards to performance. It cannot be indexed.

LIKE@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:

假设您有所有可能的城市（不仅仅是 中提到的城市）的查找表，table您可以忍受一次生成映射的低效率：

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:

现在您可以运行更高效的查询来查找您的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 locationsalso inserts the corresponding mapping rows in location2city.

这可以利用索引。现在，您只需要注意行的任何 INSERT/UPDATE/DELETElocations也会插入相应的映射行location2city。

回答by Crozin

From MySQL's point of view you're not storing multiple ids separated by comma - you're storing a textvalue, 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”或“我喜欢蛋糕！”具有完全相同的含义。- 即它没有任何意义。

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-manyor one-to-many(depending on your requirements) relationships in SQL-based databases.

您需要做的是创建一个单独的表，将数据库中的两个对象链接在一起。阅读有关基于 SQL 的数据库中的多对多或一对多（取决于您的要求）关系的更多信息。

回答by Jeremy Smyth

Rather than use INon your query, use FIND_IN_SET(docs):

不要IN在您的查询中使用，而是使用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.

关于第一种形式规范化的常见警告适用（数据库不应在单个列中存储多个值），但如果您坚持使用它，那么上述语句应该会有所帮助。

回答by fthiella

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

这不使用 IN 子句，但它应该做你需要的：

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

but you have to make sure that e_IDdoes 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.

回答by SubRed

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

回答by GKV

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

这个用于 oracle ..这里的字符串连接是由 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.

是的，我同意 raheel shan .. 为了将这个“in”子句放入，我们需要将该列放入代码下方的行中，以完成这项工作。

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
    ) ;

回答by Rinzler

you should use

你应该使用

FIND_IN_SETReturns position of value in string of comma-separated values

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

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

回答by Rachcha

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

您需要“拆分”城市列值。它会像：

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 函数替换它。

回答by Muhammad Raheel

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.

IN 需要行，所以用逗号分隔的列进行搜索不会做你想要的，但如果你提供这样的数据 ('1','2','3') 这会起作用，但你不能在你的字段中保存这样的数据无论您在列中插入什么，它都会将整个内容作为字符串。

回答by Andreas Wederbrand

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;