Comma Separated Values in MySQL "In" Clause

Comma separated values in MySQL IN clause

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

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.

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.

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;

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.

MySql select IN clause string comma delimited

You can use the MySQL FIND_IN_SET function:

SELECT *
FROM my_table
WHERE FIND_IN_SET(id, comma_delimited_string) = 0

---

Addendum: Note that the query above is not optimizable, so if you have an index on id MySQL won't use it. You'll have to decide if the relative simplicity of using FIND_IN_SET is worth taking a potential performance hit (I say potential because I don't know if id is indexed or if your table is large enough for this to be a concern).

How to set the 'where' clause on a field with comma-seperated values?

use FIND_IN_SET()

SELECT  *
FROM    tableName
WHERE   FIND_IN_SET('value3', 'comma separated value here') > 0

• SQLFiddle Demo

SOURCE

• MySQL FIND_IN_SET

Description from MySQL Docs:

Returns a value in the range of 1 to N if the string str is in the
string list strlist consisting of N substrings. A string list is a
string composed of substrings separated by “,” characters. If the
first argument is a constant string and the second is a column of type
SET, the FIND_IN_SET() function is optimized to use bit arithmetic.
Returns 0 if str is not in strlist or if strlist is the empty string.
Returns NULL if either argument is NULL.

SQL IN Clause only returning rows with first match in comma separated list of IDs

A portable solution is to use like:

where concat(',', shop, ',') like '%,2,%'

Or if the value to search for is given as a parameter:

where concat(',', shop, ',') like concat('%,', ?, ',%')

Depending on your database, there may be neater options available. In MuSQL:

where find_in_set('2', shop)

That said, I would highly recommend fixing your data model. Storing CSV data in a database defeats the purpose of a relational database in many ways. You should have a separate table to store the user/shop relations, which each tuple on a separate row. Recommended reading: Is storing a delimited list in a database column really that bad?.

array of comma separated string IN in clause mysql

You need to explode() your string and then implode() it -

$myarray = 'zzz,aaa,bbb'; 
$realArray = explode(',', $myarray);
$stringForIn = "'" . implode("','", $realArray) . "'";
echo "WHERE ids IN ($stringForIn)";

---

Related Topics