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
精彩评论