一个MySQL查询可能吗? “列包含任何数组的值" [英] Possible with one MySQL query? "column contains any of the array's values"

问题描述

基本上，我想检查包含逗号分隔值的mysql文本列是否包含数组中包含的任何值.我知道可以通过循环和多个查询来完成，但是我希望有一个查询.这可能吗?谢谢.

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.

推荐答案

我会使用这样的解决方案:

I would use a solution like this:

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

这将产生以下字符串:

'values,in,the,array'

像这样:

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

[[:<:]]和[[:>:]]是单词边界，因此它将仅匹配整个单词，并且|是一个OR，因此它将与任何单词匹配.请在此处看到小提琴.

[[:<:]] 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.

这篇关于一个MySQL查询可能吗? “列包含任何数组的值"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！