Why Doesn't MySQL IN() Query Return Rows with Multiple Matching IDs?

MySQL IN () Query Issue for Multi-ID Records

When executing the query:

SELECT * FROM table WHERE id IN (1,2,3,4);

to retrieve data based on multiple IDs, you may encounter an issue where rows containing multiple matching IDs are not returned. This query translates to:

SELECT * FROM table WHERE id='1' or id='2' or id='3' or id='4';

which retrieves only rows that match any single ID in the set.

Solution: Using SET Datatype

To address this limitation, you can consider altering the id column's datatype to SET. This allows you to store multiple values in a single column. After making this change, you can use the FIND_IN_SET function in your query:

SELECT * FROM table WHERE FIND_IN_SET('1', id);

This query will return all rows that contain '1' as one of the values in the id column, regardless of any other values present. This provides a more flexible way of matching records with multiple IDs.

The above is the detailed content of Why Doesn't MySQL IN() Query Return Rows with Multiple Matching IDs?. For more information, please follow other related articles on the PHP Chinese website!