Why doesn't this mysql IN (1,2,3,4...) condition work if there are many values in the parenthesis?

I am storing in a column a list of states that are separated by commas:

Like this: 1,2,3,4,5,6,7,8.. and so on, just their IDs.

Then I am doing a query to get all the rows that have the state with the ID 8, and it works when the list of states has few items.

This is the query and the table:

mysql> select id_partner, name, states from partner where 8 IN (states);
+------------+----------------+--------------------+
| id_partner | name           | states             |
+------------+----------------+--------------------+
|          1 | Inmo Inmo      | 8,9,10             |
|          2 | Foto Piso      | 8,9,10,11,12,13,14 |
|          4 | PARTNER 001-A  | 8                  |
|          6 | EnAlquiler     | 8                  |
|          7 | Habitaclia.com | 8,43,50            |
+------------+----------------+--------------------+
5 rows in set (0.00 sec)

If the column states contains 10 IDs separated by comma it will work, but if it has 50 or more it will not work anymore. In the above result it will not show the row that has many states, including the one with ID 8.

Any idea? I am using this approach to not having to create another table to save the relation between the partner and the states, or should I do that better?

Solution

That's not how the IN clause works--you need to use the FIND_IN_SET function to search comma separated values in a single column:

SELECT *  
  FROM partner 
 WHERE FIND_IN_SET(8, states) > 0

Realistically, you should not be storing comma delimited data. It's known as denormalized data, and can be difficult to get information on specific values within the commas.