I have a MySQL table column rubrics which contains string value '61,80,112,256'. So I try execute that query:
select * from table where 256 in (rubrics) and 61 in (rubrics)
And no result returns. Any suggestions?
Add a comment
|
4 Answers
Since your rubrics column is a comma separated list the IN operator will not work.
MySQL does have a function that can find a value in a string list so you should be able to use FIND_IN_SET():
select *
from yourtable
where find_in_set(61, rubrics)
or find_in_set(256, rubrics)
Something like WHERE rubrics LIKE '%,256,%' OR rubrics LIKE '256,%' OR rubrics LIKE '%,256'. Using parenthesis you can also filter on the 61, but the resulting query will be come messy. You'd better normalize your data, so you can use subqueries and joins using keys (the real deal).
(see also bluefeet's answer as FIND_IN_SET is a better approach)
2 Comments
mogilka
bouke, using like and % is less efficient. Performance is important for me. For this reason, I also do not use the benefit of JOIN string field that contains an array of integers
Bouke
FWIW I have no experience with
FIND_IN_SET, but my guess is that it will be much slower than using joins and correct use of indexes.Try this
select * from table where rubrics like '%'+'256,'+'%' and rubrics like '%'+'61,'+'%'
1 Comment
Bouke
This also matches '1256', as the
% will eat everything. See my answer for how LIKE should be used in this case.IN operator does not work with strings
use correct substring matching operator like LIKE or LOCATE
one advice - update your rubics column to begin and end with , character, that will make your LOCATE(",62,", rubics) operations unambiguous as opposed to LOCATE("62", rubics) which will match also 622 and 262 and other combinations. Locating ,62, wil fail if your rubics has value of 62,blah,foo,bar because it doesn't start with ,
1 Comment
mogilka
Thank you, I tryed it now but using find_in_set() is more fast: find_in_set 0.006 sec locate 0.010 sec