MySQL 在逗号列表中搜索 [英] MySQL search in comma list

问题描述

我有一个 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)

这篇关于MySQL 在逗号列表中搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！