mysql - 全文搜索结果类似Mysql Like '%%'查询

Question

我们正在寻找 mysql 查询，它提供的结果与 '%%' 查询完全相同，但使用全文搜索 MATCH AGAINST

例如

Select id,name from table where jobtitle like '%java software engineer in google%'

它给出了所有包含 line java software engineer in google 的行

但是如果我们使用下面的 mysql 查询，那么它会给出不同的结果

Select id,name from table
where MATCH(jobtitle) AGAINST ('+java software engineer in google' IN BOOLEAN MODE)

它在 google 中给出包含来自 java software engineer 的任何单词的行

我想要来自使用 like '%java software engineer in google%' 查询的确切输出。

Answer 1

作为 Boolean fulltext search 上的 MySQL 文档表示(粗体是我的):

The boolean full-text search capability supports the following operators: [...]

"

A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

If the phrase contains no words that are in the index, the result is empty. The words might not be in the index because of a combination of factors: if they do not exist in the text, are stopwords, or are shorter than the minimum length of indexed words.

因此，使用 " 运算符:

Select id,name from table
where MATCH(jobtitle) AGAINST ('"java software engineer in google"' IN BOOLEAN MODE)

粗体部分包含警告:由于全文索引的性质，结果可能略有不同。