Possible with one MySQL query? “column contains an

问题:

Basically I want to check whether a mysql text column, which contains comma-separated values, contains any of the values contained in an array. I know this can be done with a loop and multiple queries, but I was hoping for a single query. Is this possible? Thank you.

回答1:

I would use a solution like this:

SELECT
  *
FROM
  yourtable
WHERE
  str RLIKE
  CONCAT('[[:<:]]', REPLACE('values,in,the,array', ',', '[[:>:]]|[[:<:]]'), '[[:>:]]')

this will make the following string:

'values,in,the,array'

like this:

[[:<:]]values[[:>:]]|[[:<:]]in[[:>:]]|[[:<:]]the[[:>:]]|[[:<:]]array[[:>:]]

[[:<:]] and [[:>:]] are word boundaries so it will match only whole words, and | is an OR so it will match any of the words. Please see fiddle here.

回答2:

SELECT COUNT(*) FROM my_table WHERE (my_column LIKE '%,0,%' OR my_column LIKE '%,1,%');

or

SELECT COUNT(*) FROM my_table WHERE (my_column REGEXP LIKE '.*,[0-1],.*');

回答3:

Do you mean

... WHERE my_column LIKE '%first_array_value%' OR my_column LIKE '%second_array_value%'

It will work but very bad because of performance. There is also another way

... WHERE MATCH (my_column) AGAINST ('first_array_value second_array_value')

but the best way is to change data structure if possible.