使用 PHP 和 Mysql 进行相关性搜索
这样做的最佳方法是什么？
我有列：track_name、artist_name、album_name
我希望所有列都与搜索查询匹配。以及匹配时的一些灵活性。
mysql之类的太严格了，即使是%XXX%。它匹配整个字符串，而不是各个部分。
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的 MySQL 查询可能有多个
OR子句，搜索用户输入的每个空格分隔的单词。例如，用户搜索“Queens of the Stoneage”可能会在 SQL 中表示为 SELECT * FROMongs WHERE Artist_name LIKE "%Queens%" OR Artist_name LIKE "Stoneage"。然而，这可能是不可取的，因为以
%开头的LIKE搜索效率低下，并且在大型数据库上可能会非常慢。虽然我无法谈论性能影响，但您应该看看 自然语言全文搜索。这可能是您会发现的最有效的解决方案：
SELECT * FROMongs WHERE MATCH(track_name, Artist_name, album_name) AGAINST('Queens of the Stoneage' IN NATURAL LANGUAGE MODE);一些 PHP 函数确实存在用于确定文本字符串的相似性，但将这项工作保留在数据库中可能是最有效的（并且不那么令人沮丧）：
Your MySQL query could have several
ORclauses, searching for each space-delimited word entered by the user. For example, a user search for "Queens of the Stoneage" may be represented in SQL asSELECT * FROM songs WHERE artist_name LIKE "%Queens%" OR artist_name LIKE "Stoneage".However, that could be undesirable because
LIKEsearches which start with an%are inefficient and could be terribly slow on a large database.Though I can't speak to the performance implications, you should have a look at natural language full-text searches. It's probably the most effective solution you'll find:
SELECT * FROM songs WHERE MATCH(track_name, artist_name, album_name) AGAINST('Queens of the Stoneage' IN NATURAL LANGUAGE MODE);Some PHP functions do exist for determining the similarity of strings of text, but keeping this work in the database will probably be most efficient (and less frustrating):
我认为你需要重新考虑你的申请。我从您的评论中了解到，您需要在程序中实现一些逻辑运算符，例如“and”、“or”和“not”。它不仅仅涉及像全文索引这样的奇特算法或像这个 mysql 匹配查询这样的最长公共子字符串。但我可能是错的。
I think you need to rethink your application. What I understood from your comment is that you need to implement some logic operator like "and", "or" and "not" in your program. It's not only about fancy algorithm like fulltext index or longest common substring like this mysql match query. But I can be wrong.