在逗号分隔的字段上查询多个值

As an example I have this as a field in a column:

作为一个例子，我将其作为列中的字段：

3,21,23,41,47,56,57,61,64,74,78,79,82,83,86,90,94

3,21,23,41,47,56,57,61,64,74,78,79,82,83,86,90,94

These are IDs and I'm trying to figure out how I can search for multiple numbers I've tried for example:

这些是ID，我试图弄清楚如何搜索我试过的多个数字，例如：

SELECT * FROM table WHERE status='A' AND (FIND_IN_SET('3,64', myids))

This only seems to work if the 3 and 64 were together and outputting the find_in_set for each value could be a bit of a pain.

如果3和64在一起并且为每个值输出find_in_set可能有点痛苦，这似乎只有效。

Any better solutions or suggestions?

有更好的解决方案或建议吗

4 个解决方案

#1

FIND_IN_SET() only finds the position of the first argument (see MySQL Reference, String Functions).

FIND_IN_SET（）只查找第一个参数的位置（参见MySQL Reference，String Functions）。

You may use a single FIND_IN_SET for each id you are looking for:

您可以为要查找的每个ID使用一个FIND_IN_SET：

SELECT * FROM table WHERE status='A' AND
  FIND_IN_SET('3', myids) AND FIND_IN_SET('64', myids)

#2

SELECT * FROM table WHERE status='A' AND myids in (3, 64)

You can try this one.

你可以尝试这个。

#3

SELECT * FROM table WHERE myids REGEXP REPLACE("3,64", ',', '(\,|$)|');

SELECT * FROM table WHERE myids REGEXP REPLACE（“3,64”，'，'，'（\，| $）|'）;

#4

-1

foreach($ids as $id)
{
  SELECT * FROM table WHERE status='A' AND (FIND_IN_SET($id, myids))
}

that check one by one and getting the result from table.

#1