Problem with MySQL Select query with "IN" condition

I found a weird problem with MySQL select statement having "IN" in where clause:

I am trying this query:

SELECT ads.* 
  FROM advertisement_urls ads 
 WHERE ad_pool_id = 5 
   AND status = 1 
   AND ads.id = 23 
   AND 3 NOT IN (hide_from_publishers) 
ORDER BY rank desc

In above SQL hide_from_publishers is a column of advertisement_urls table, with values as comma separated integers, e.g. 4,2 or 2,7,3 etc.

As a result, if hide_from_publishers contains same above two values, it should return only record for "4,2"开发者_运维百科 but it returns both records

Now, if I change the value of hide_for_columns for second set to 3,2,7 and run the query again, it will return single record which is correct output.

Instead of hide_from_publishers if I use direct values there, i.e. (2,7,3) it does recognize and returns single record.

Any thoughts about this strange problem or am I doing something wrong?

There is a difference between the tuple (1, 2, 3) and the string "1, 2, 3". The former is three values, the latter is a single string value that just happens to look like three values to human eyes. As far as the DBMS is concerned, it's still a single value.

If you want more than one value associated with a record, you shouldn't be storing it as a comma-separated value within a single field, you should store it in another table and join it. That way the data remains structured and you can use it as part of a query.

You need to treat the comma-delimited hide_from_publishers column as a string. You can use the LOCATE function to determine if your value exists in the string.

Note that I've added leading and trailing commas to both strings so that a search for "3" doesn't accidentally match "13".

select ads.* 
    from advertisement_urls ads 
    where ad_pool_id = 5 
        and status = 1 
        and ads.id = 23 
        and locate(',3,', ','+hide_from_publishers+',') = 0
    order by rank desc

You need to split the string of values into separate values. See this SO question...

Can Mysql Split a column?

As well as the supplied example...

http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

Here is another SO question:

MySQL query finding values in a comma separated string

And the suggested solution:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set