This question already has an answer here:
这个问题在这里已有答案：
- MySQL query finding values in a comma separated string 10 answers
- MySQL查询以逗号分隔的字符串10回答查找值
I have a MySQL field with a reference to another table where ids are saved as comma seperated list, eg:
我有一个MySQL字段，引用另一个表，其中id被保存为逗号分隔列表，例如：
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%'
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?
问题现在很明显，几乎所有条目都可以通过此示例查询找到，因为最常见的ID位于范围10-20中。我的想法是搜索％，1，％，但这不适用于该字段中的第一个和最后一个ID。我有类似内部替换的东西或我如何解决这个最好的方法？
5 个解决方案
#2
15
Be aware that plain FIND_IN_SET is case-insensitive,
请注意，明文FIND_IN_SET不区分大小写，
i.e. FIND_IN_SET('b3','a1,a2,B3,b3') and FIND_IN_SET('B3','a1,a2,B3,b3') both return 3.
即FIND_IN_SET（'b3'，'a1，a2，B3，b3'）和FIND_IN_SET（'B3'，'a1，a2，B3，b3'）都返回3。
To be case sensitive, add 'binary' modifier to the 1st argument, e.g. FIND_IN_SET (binary 'b3', 'a1,a2,B3,b3') returns 4.
为区分大小写，将'binary'修饰符添加到第一个参数，例如FIND_IN_SET（二进制'b3'，'a1，a2，B3，b3'）返回4。
#3
7
As others have said, Find_In_Set will let you write the query, but you really need to look at your database design (and I know you know this...)
正如其他人所说，Find_In_Set会让你编写查询，但你真的需要看看你的数据库设计（我知道你知道这个...）
The trouble with including Foreign Keys in a delimited list like this is that whole point of a foreign key is to enable you to locate the information in the other table quickly, using Indexes. By implementing a database as it sounds you have, you have all sorts of issues to resolve:
在这样的分隔列表中包含外键的问题在于，外键的重点是使您能够使用索引快速定位另一个表中的信息。通过实现您拥有的数据库，您可以解决各种问题：
- How do I prevent duplicates (which would waste space)
- 如何防止重复（会浪费空间）
- How do I remove a given value (Requires custom function, leading to possibility of errors?
- 如何删除给定值（需要自定义函数，导致错误的可能性？
- How do I respond to performance issues as the size of my tables increase?
- 随着表格大小的增加，我如何回应性能问题？
There's only one truly acceptable way to address this - which is not to face the problem in the first place.
只有一种真正可以接受的解决方法 - 首先不要面对问题。
Have a sit down chat with those on high, and explain the problems with their solution - then explain the advantages of doing the job properly.
与高层人员聊天，并用他们的解决方案解释问题 - 然后解释正确完成工作的好处。
If they won't even discuss the point, look for a job with a decent employer who values your contributions.
如果他们甚至不讨论这一点，那就找一份合适的雇主来评估你的贡献。
Martin.
马丁。
#5
2
After reading this question Id like to add that if your comma delimited list has spaces i.e. (1, 2,3 ,4) you will need to remove the leading/trailing spaces or use WHERE FIND_IN_SET(X,field) OR FIND_IN_SET(' X',field) OR FIND_IN_SET('X ',field)..... Just thought i'd share that since i came across that problem..... just gotta create those databases right the first time or they will give you all kinds of trouble.
读完这个问题之后我想补充一点，如果你的逗号分隔列表有空格，即（1,2,3,4），你需要删除前导/尾随空格或使用WHERE FIND_IN_SET（X，field）或FIND_IN_SET（'X '，field）或FIND_IN_SET（'X'，字段）.....我想分享一下，因为我遇到了这个问题.....只需要在第一时间创建这些数据库，否则他们会给你各种麻烦。
#1
#2
15
Be aware that plain FIND_IN_SET is case-insensitive,
请注意，明文FIND_IN_SET不区分大小写，
i.e. FIND_IN_SET('b3','a1,a2,B3,b3') and FIND_IN_SET('B3','a1,a2,B3,b3') both return 3.
即FIND_IN_SET（'b3'，'a1，a2，B3，b3'）和FIND_IN_SET（'B3'，'a1，a2，B3，b3'）都返回3。
To be case sensitive, add 'binary' modifier to the 1st argument, e.g. FIND_IN_SET (binary 'b3', 'a1,a2,B3,b3') returns 4.
为区分大小写，将'binary'修饰符添加到第一个参数，例如FIND_IN_SET（二进制'b3'，'a1，a2，B3，b3'）返回4。
#3
7
As others have said, Find_In_Set will let you write the query, but you really need to look at your database design (and I know you know this...)
正如其他人所说，Find_In_Set会让你编写查询，但你真的需要看看你的数据库设计（我知道你知道这个...）
The trouble with including Foreign Keys in a delimited list like this is that whole point of a foreign key is to enable you to locate the information in the other table quickly, using Indexes. By implementing a database as it sounds you have, you have all sorts of issues to resolve:
在这样的分隔列表中包含外键的问题在于，外键的重点是使您能够使用索引快速定位另一个表中的信息。通过实现您拥有的数据库，您可以解决各种问题：
- How do I prevent duplicates (which would waste space)
- 如何防止重复（会浪费空间）
- How do I remove a given value (Requires custom function, leading to possibility of errors?
- 如何删除给定值（需要自定义函数，导致错误的可能性？
- How do I respond to performance issues as the size of my tables increase?
- 随着表格大小的增加，我如何回应性能问题？
There's only one truly acceptable way to address this - which is not to face the problem in the first place.
只有一种真正可以接受的解决方法 - 首先不要面对问题。
Have a sit down chat with those on high, and explain the problems with their solution - then explain the advantages of doing the job properly.
与高层人员聊天，并用他们的解决方案解释问题 - 然后解释正确完成工作的好处。
If they won't even discuss the point, look for a job with a decent employer who values your contributions.
如果他们甚至不讨论这一点，那就找一份合适的雇主来评估你的贡献。
Martin.
马丁。
#4
#5
2
After reading this question Id like to add that if your comma delimited list has spaces i.e. (1, 2,3 ,4) you will need to remove the leading/trailing spaces or use WHERE FIND_IN_SET(X,field) OR FIND_IN_SET(' X',field) OR FIND_IN_SET('X ',field)..... Just thought i'd share that since i came across that problem..... just gotta create those databases right the first time or they will give you all kinds of trouble.
读完这个问题之后我想补充一点，如果你的逗号分隔列表有空格，即（1,2,3,4），你需要删除前导/尾随空格或使用WHERE FIND_IN_SET（X，field）或FIND_IN_SET（'X '，field）或FIND_IN_SET（'X'，字段）.....我想分享一下，因为我遇到了这个问题.....只需要在第一时间创建这些数据库，否则他们会给你各种麻烦。