douxiegan6468
2014-10-16 05:55
浏览 218
已采纳

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

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

图片转代码服务由CSDN问答提供 功能建议

我的字段“locations”包含格式为1,4,7,8等的值。

我想排除包含特定值的行。 例如: $ location = 4;

我试过: SELECT * FROM users WHERE FIND_IN_SET($ location,locations)= 0 < / p>

还尝试了一些与IN ..!IN的组合,但同样没有结果。 有什么建议吗?

谢谢

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doucheng1884 2014-10-16 07:09
    已采纳

    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

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duancaishun4812 2014-10-16 05:58

    SELECT * FROM users WHERE FIND_IN_SET($location, locations) != 0

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题