mysql - 为什么这 2 个 MySQL 查询返回不同的结果

Question

我正在运行一个 MySQL 查询来搜索工作数据库。我正在使用 MATCH 和 AGAINST 来使结果具有相关性。但是，我对这两个查询返回不同结果的原因感到困惑:

SELECT SQL_CALC_FOUND_ROWS *, MATCH (`title`) 
            AGAINST ("assistant") 
            AS Relevance 
            FROM jobs2 
            WHERE MATCH (`title`) 
            AGAINST ("assistant") AND date  >= CURDATE() - INTERVAL 28 DAY GROUP BY jobref ORDER BY Relevance DESC LIMIT 0,50

据我所知，这将默认为自然语言模式，但返回 0 个结果，而:

SELECT SQL_CALC_FOUND_ROWS *, MATCH (`title`) 
            AGAINST ("assistant" IN BOOLEAN MODE) 
            AS Relevance 
            FROM jobs2 
            WHERE MATCH (`title`) 
            AGAINST ("assistant" IN BOOLEAN MODE) AND date  >= CURDATE() - INTERVAL 28 DAY GROUP BY jobref ORDER BY Relevance DESC LIMIT 0,50

按预期返回 4 个结果，但相关性始终为 1，因此实际上不可能按相关性排序？

我的数据库有这些测试工作:

|date               |title                                                          |jobref|

|2016-04-08 07:21:19|Assistant Management Accountant                                |12345 |
|2016-04-08 07:21:19|Assistant Accountant                                           |12346 |
|2016-04-08 07:19:15|Assistant Finance Manager                                      |12347 |
|2016-04-08 07:20:38|Accounts Assistant / Purchase Ledger Clerk / Accounts Payable  |12348 |

基本上，为什么自然语言模式返回 0 个结果？

Answer 1

来自自然语言搜索的 MySQL 文档:

In addition, words that are present in 50% or more of the rows are considered common and do not match.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

看起来您的示例数据超过了 50% 的匹配率。