MySQL Select：查找数组字段中是否存在单个值

Question

I've a field "locations" containing values in format 1,4,7,8 etc.

I want to exclude rows that contain a specific value. eg: $location = 4;

I tried: SELECT * FROM users WHERE FIND_IN_SET($location, locations) = 0

Also tried some combinations with IN .. !IN but again no results. Any suggestion?

Thank you

Answer 1

As pointed by John Woo your query must work. If not then may be data in the locations contains values with spaces before commas, for example: 1,4 ,7,8. In this case FIND_IN_SET isn't working as expected. So you can try:

SELECT * FROM users WHERE NOT(locations REGEXP '[[:<:]]$location[[:>:]]')

[[:<:]], [[:>:]] stand for word boundaries, so [[:<:]]4[[:>:]] will match 4 in list, but not 14.

Test and see difference on SQL Fiddle