MySQL 在逗号列表中搜索

2021-11-20 00:00:00 mysql

我有一个 MySQL 字段,其中引用了另一个表,其中 id 保存为逗号分隔列表,例如:

I have a MySQL field with a reference to another table where ids are saved as comma seperated list, eg:

12,13,14,16

代表另一个表中的值.我知道这是非常糟糕和错误的,但这是来自上面的,我对此无能为力.现在的问题是我想用这样的查询在该字段中进行搜索:

which stand for values in another table. I know this is very bad and wrong, but this comes from above and I cant do anything about that. The problem now is that i want to search in that field with a query like this:

SELECT ... WHERE field LIKE '%1%'

现在的问题显然是几乎所有条目都可以在这个示例查询中找到,因为最常见的 ID 位于 10-20 范围内.我的想法是搜索 %,1,% ,但这不适用于该字段中的第一个和最后一个 id.是否有内部替换之类的东西,或者我如何以最好的方式解决这个问题?

The Problem now is obviously that almost all entries can be found with this example Query, because the most common IDs are in Range 10-20. My Idea is to search for %,1,% instead, but this does not work for the first and last id in the field. Ist there something like an internal replace or how do i fix this the best way?

推荐答案

您需要 FIND_IN_SET 函数:

You need the FIND_IN_SET function:

SELECT ... WHERE FIND_IN_SET('1', field)

相关文章