MySQL LIKE limiting

问题:

Basicly I have a row in my table, that has the following format for it's value - 1,2,3,4,10,11,21,34 etc..

I'm retrieving values with LIKE statement, but since I'm using it with %<value>%, when searching for 1 it returns 11, 21, 1 and so on.

How can I limit it, to return values based on one?

回答1:

Instead of col LIKE '%<value>%' do

col LIKE '%,<value>,%' OR col LIKE '<value>,%' OR col LIKE '%,<value>' OR col LIKE '<value>'

or better yet

col REGEXP '(.+,|^)<val>(,.+|$)'

But the best solution is to change your data structure! Either use the SET type, or several fields, or a join table.

回答2:

Try the find_in_set() string function:

SELECT ...
FROM ...
WHERE FIND_IN_SET(somevalue, '1,2,3,4,10');

Note that this is a purely MySQL function and isn't available anywhere else without jumping through huge hoops.

回答3:

If you want to return only a specific match, then use = instead of LIKE. Like is meant to return partial matches, unlike = which only returns specific matches. If you just want to match the first part, use %, but 11 would still return 111, but not 211. A common mistake is to use LIKE for everything, OR remove the %% around the like. Also, do you really mean that the VALUE for a row in your MYSQL database is 1,2,3,4,11,12 etc? And if so, why are you trying to search for that value based on a number that is in it? I would think you would be searching for a subset of the whole, such as %3,4,11% etc...

回答4:

While I agree with the other answers regarding changing your data structure, the problem you are encountering can easily be fixed by storing every value in your serial data field encapsulated on both sides with a delimiter.

I don't recommend it, but if you changed your data from 1,2,3,4,10,11,21,34 to ,1,2,3,4,10,11,21,34,, you will note that each value is marked on both sides with a comma, so you can now search for %,<value>,%