MySQL FullText搜索结果相关性

Question

我有以下'银行'表

ID  | name
1   | JPMorgan Chase bank  
2   | Bank of south  
3   | Citigroup bank 
4   | Wells Fargo bank
5   | Bank of New York 

因此，当搜索'''''''''我希望搜索结果如下：

：

Bank of New York
Bank of south
Citigroup bank
JPMorgan Chase bank
Wells Fargo bank

但在下面的代码下，它返回：

JPMorgan Chase bank
Bank of America
Citigroup bank
Wells Fargo bank
Bank of New York 

这是代码：

$search_term = 'bank of';
$sql2 = "SELECT *, 
         MATCH(name) AGAINST ('.$search_term.' IN BOOLEAN MODE)
         AS relevance FROM banks
         HAVING relevance > 0.8 
         ORDER BY relevance DESC";  

谢谢！

Answer 1

...对但是我的选择是什么？

您可以尝试这样的东西

SELECT *, 
       MATCH(name) AGAINST ('bank of' IN BOOLEAN MODE) AS relevance,
       name LIKE '%bank of%' AS full_match
  FROM banks
HAVING relevance > 0.8 
 ORDER BY full_match DESC, relevance DESC, name

结果：

|id |名称|相关性|full_match || ---- | --------------------------------------------------------------------------- -  ||5 |纽约银行|1 |1 ||2 |南方银行|1 |1 ||3 |花旗银行|1 |0 ||1 |摩根大通银行|1 |0 ||4 |富国银行|1 |0 |

这是 sqlfiddle demo