从 MySQL 中的字符分隔文本字段搜索项目
我正在构建一个基于 MySQL 的应用程序，允许用户搜索一些设施。这些设施存储在一个 TEXT 列中，并表示为“|”分隔的字段。例子如下：
1|2|3|5|6|10|11|12|13|15|17|18|20|21|27|29|30|31|3...
你知道如何在 MySQL 中实现高效的设施搜索吗？假设我们需要检索“包含”设施 1、2 和 15 的所有行，查询/方法是什么？我试图使用全文索引，但没有任何运气。
I am building an application based on MySQL that allows users to search for some facilities. The facilities are stored in one TEXT column and they are represented as "|"-separated field. Here is the example:
1|2|3|5|6|10|11|12|13|15|17|18|20|21|27|29|30|31|3...
Do you have any idea how I can implement efficient searching for facilities in MySQL? Let's assume that we need to retrieve all rows that "contain" facilities 1, 2 and 15, what would be the query/method? I was trying to use Full-Text indexes but without any luck.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
无法有效地在符号分隔的字符串中搜索给定值。。
在SQL中提高效率的最好方法是使用索引，但是不能使用索引来帮助查找子字符串。
优化场景的最佳方法是创建一个具有单个索引 INT 列的子表，并将每个数值存储在单独的行中。然后您可以借助索引进行高效搜索。
另请参阅我对 在数据库列中存储逗号分隔的列表真的那么糟糕吗？
关于当多个设施存储在单独的行上时匹配多个设施，我添加了Stack Overflow 上用于解决此类问题的
sql-match-all标记。它经常出现，并且已经有很多好的答案。单击标签即可找到它们。There is no way to search a symbol-separated string for a given value efficiently.
The best way to improve efficiency in SQL is to use indexes, but you can't use indexes to help find substrings.
The best way to optimize your scenario is to create a child table with a single indexed INT column, and store each of your numeric values on a separate row. Then you can search it efficiently with the aid of the index.
See also my answer to Is storing a comma separated list in a database column really that bad?
Regarding matching multiple facilities when they're stored on separate rows, I've added the
sql-match-alltag which is used on Stack Overflow for that type of problem. It comes up frequently, and there are lots of good answers already. Click the tag to find them.您可以使用 FIND_IN_SET() 函数。但是，这仅适用于逗号分隔的列表，因此您必须预处理字段：
性能可能会非常糟糕，因为您将在派生字段上进行搜索，这妨碍了使用索引。您最好规范化设计，并将这些值拆分到子表中。
You could use the FIND_IN_SET() function. However, this only works on comma-separated lists, so you'd have to pre-process your fields:
Performance will probably be pretty crappy, since you'll be doing your searching on derived fields, which precludes using indexes. You'd be better off normalizing the design, and splitting those values into a child table.