参数相反的MySQL MATCH()AGAINST() [英] MySQL MATCH() AGAINST() with reversed parameters
问题描述
我有一个看起来像这样的数据库表:
I have a database table that looks a bit like this:
id|words|url
1|+Word +Matching -Goodbye|/url-1
2|+Redirect +Me|/url-2
3|+Goodbye +Word|/url-3
当用户键入搜索内容:你好我正在匹配一个单词"时，我希望对table.words字段赋予与该字符串相关性"的分数，就像MATCH()AGAINST()一样功能，但参数相反.
When a user types a search for: "Hello I am matching a word", I would like the table.words field to be given a 'relevance' score against that string, a lot like the MATCH() AGAINST() function, but with the parameters reversed.
有效地，我要运行的查询大致如下:
Effectively, the query I am looking to run would be along the lines of:
SELECT id, words, url,
MATCH ("Hello I am matching a word") AGAINST (words IN BOOLEAN MODE) AS relevance
FROM table
ORDER BY relevance DESC
但是，不幸的是，这行不通.我可以在PHP中做一些事情，在其中创建一个函数来遍历每个包含/不包含的单词，但是我担心，随着表大小的增加，这确实会很慢.
But this does not work, unfortunately. I could do something in PHP where I create a function to loop through each inclusive/exclusive word, but I fear that this will be really slow when the table size grows.
为了解决这个问题，我希望查询返回id:1，因为它包含"Word"和"Matching"，但不包含"Goodbye".我应该指出，这些单词可以在字符串中以任意顺序排列，所以我认为我不能真正使用LIKE.
Just to tie it up, I would expect the query to return id: 1 in that instance, as it includes "Word" and 'Matching", and does not include "Goodbye". I should point out that these words could be in any order within the string, so I couldn't really use LIKE, I don't think.
如果不存在这样的功能，有没有更好的方法呢?
If such a function does not exist, is there a better way I could approach this?
谢谢！
