MySQL 在逗号列表中搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5458703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL search in comma list
提问by Flo
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。是否有内部替换之类的东西，或者我如何以最佳方式解决这个问题？
回答by awm
回答by Scott Chu
Be aware that plain FIND_IN_SETis case-insensitive,
请注意，plainFIND_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 (binary 'b3', 'a1,a2,B3,b3')返回 4。
回答by Martin Milan
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.
马丁。
回答by Shakti Singh
回答by almcaffee
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) OR FIND_IN_SET(' X ',field) OR FIND_IN_SET('X ',field)..... 只是想我会分享，因为我遇到了这个问题..... 第一次就创建这些数据库，否则他们会给你各种烦恼。
