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

Question

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?

Answer 1

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.

Answer 2

Your string is a single value, not multiple values. It happens to contain commas, but it's still a single string.

The value of a string in a numeric context is taken from the leading number digits. In other words, the numeric value of '123,456,789' is 123. MySQL ignores all characters past the first non-digit. (This is the behavior in MySQL, other databases can behave differently).

The IN( ) predicate allows you to compare to multiple values, but you have to make them separate SQL expressions. For instance in the following two queries, only the first one returns anything:

SELECT * FROM partner WHERE 5 IN ( 1, 2, 3, 4, 5 );

SELECT * FROM partner WHERE 5 IN ( '1,2,3,4,5' );

This shows one of the many reasons why you shouldn't use a string with comma-separated elements and expect it to behave like it's really a collection of separate values.

The better design in this case is to store the states for a given partner in another table, where each association between a partner and a state is on a separate row.

create table partner_states (
 id_partner int not null,
 id_state   int not null,
 primary key (id_partner, id_state)
);

Then populate it:

insert into partner_states (id_partner, id_state)
values (1, 8), (1, 9), (1, 10);

Then you can query for partners that match a certain state easily:

select id_partner, name, states 
from partner p join partner_states s on p.id_partner = s.id_partner
where s.id_state = 8

Querying for partners that don't match a state you can do with an outer join:

select id_partner, name, states 
from partner p left outer join partner_states s on p.id_partner = s.id_partner
  and s.id_state = 8
where s.id_state is null