在 MySQL 表中搜索包含 CSV 数据的列是否存在输入值

问题描述

我在 MySQL 中有一个表 ITEM，它存储数据如下:

I have a table say, ITEM, in MySQL that stores data as follows:

ID    FEATURES
--------------------
1     AB,CD,EF,XY
2     PQ,AC,A3,B3
3     AB,CDE
4     AB1,BC3
--------------------

作为输入，我会得到一个 CSV 字符串，类似于AB,PQ".我想获取包含 AB 或 PQ 的记录.我意识到我们必须编写一个 MySQL 函数来实现这一点.因此，如果我们在 MySQL 中定义了这个神奇的函数 MATCH_ANY 来执行此操作，那么我将简单地执行如下 SQL:

As an input, I will get a CSV string, something like "AB,PQ". I want to get the records that contain AB or PQ. I realized that we've to write a MySQL function to achieve this. So, if we have this magical function MATCH_ANY defined in MySQL that does this, I would then simply execute an SQL as follows:

select * from ITEM where MATCH_ANY(FEAURES, "AB,PQ") = 0

上述查询将返回记录 1、2 和 3.

The above query would return the records 1, 2 and 3.

但是我在实现这个函数时遇到了各种各样的问题，因为我意识到 MySQL 不支持数组并且没有简单的方法可以根据分隔符分割字符串.

But I'm running into all sorts of problems while implementing this function as I realized that MySQL doesn't support arrays and there's no simple way to split strings based on a delimiter.

改造桌子是我最后的选择，因为它涉及很多问题.

Remodeling the table is the last option for me as it involves lot of issues.

我可能还想执行包含多个 MATCH_ANY 函数的查询，例如:

I might also want to execute queries containing multiple MATCH_ANY functions such as:

select * from ITEM where MATCH_ANY(FEATURES, "AB,PQ") = 0 and MATCH_ANY(FEATURES, "CDE")

在上面的例子中，我们将得到记录 (1, 2, 3) 和 (3) 的交集，这将是 3.

In the above case, we would get an intersection of records (1, 2, 3) and (3) which would be just 3.

非常感谢任何帮助.

谢谢

推荐答案

首先，数据库当然不应该包含逗号分隔值，但希望您已经意识到这一点.如果表格已规范化，您可以使用如下查询轻松获取项目:

First of all, the database should of course not contain comma separated values, but you are hopefully aware of this already. If the table was normalised, you could easily get the items using a query like:

select distinct i.Itemid
from Item i
inner join ItemFeature f on f.ItemId = i.ItemId
where f.Feature in ('AB', 'PQ')

可以匹配逗号分隔值中的字符串，但效率不高:

You can match the strings in the comma separated values, but it's not very efficient:

select Id
from Item
where
  instr(concat(',', Features, ','), ',AB,') <> 0 or
  instr(concat(',', Features, ','), ',PQ,') <> 0

这篇关于在 MySQL 表中搜索包含 CSV 数据的列是否存在输入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！