开发者

MySQL - Where - search string - MATCH

Quick question. I'm in a bit of a rush but if someone could quickly point me in the right direction I would be very very happy.

I have a field in the db, let's call it field_a which returns a string in the format "20,50,60,80" etc.

I wish to do a query which will search in this field to see if 开发者_开发问答20 exists.

Could I use MySQL MATCH or is there a better way?

Thank you!


The better way would be to save the data differently. With WHERE a LIKE '...' and MATCH/AGAINST (besides being fairly slow) you can't easily search for just "20"... If you search for "20" you'll get "200" too; if you search for ",20," you won't get "20, 50"


Use FIND_IN_SET:

WHERE FIND_IN_SET(20, field_a) != 0


Just be careful you don't get a substring of what you actually want - for example LIKE '%20%' would also match '50,120,70'. If you're using MySQL, you might want to use REGEXP '[[:<:]]20[[:>:]]' - where the funny faces are word boundary markers that will respect break on beginning / end of string or commas so you shouldn't get any false positives.

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜