全文搜索结果与Mysql类似于'%%'查询 [英] Full Text Search result similar to Mysql Like '%%' query

问题描述

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

 选择id，name from table where jobtitle like'％java software engineer in google％'
  

它给出了所有包含行java软件工程师的行在谷歌

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

 从表
中选择id，名称MATCH（jobtitle）AGAINST（'+ google在'BOOLEAN MODE中的java软件工程师'）
  

它给出了包含来自java软件工程师的任何单词的行

我想要使用来自使用像'％java软件工程师在google％'查询。

解决方案

dev.mysql.com/doc/refman/5.7/en/fulltext-bo olean.htmlrel =nofollow>布尔全文搜索表示（粗体是我的）：

文本搜索功能支持以下
操作符：[...]

短语包含在双引号（）字符中的字符与仅包含短语的行相匹配
，因为它是键入的。
全文引擎将短语拆分为单词，并在
中搜索单词的FULLTEXT索引。 非单词字符不必完全匹配
：短语搜索仅需要匹配包含
的字词与短语完全相同，并且顺序相同。对于
例如，测试短语匹配测试，短语。 b
$ b

如果短语不包含任何索引，结果是
空。这些单词可能不在索引中，因为bb $ b因素的组合：如果它们不存在于文本中，是停用词，或者比索引单词的最小长度短
。

因此，使用运算符：

 选择id，名称来自表
 MATCH（jobtitle）AGAINST（''google中的java软件工程师''BOOLEAN模式）
  

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

We are looking for mysql query which provide result exactly like '%%' query but using full text search MATCH AGAINST

For example

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

It gives all rows which contain line java software engineer in google

But if we use following mysql query then it gives a different result

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

It gives rows which contain any word of from java software engineer in google

I want the exact output which comes from using like '%java software engineer in google%' query.

解决方案

As MySQL documentation on Boolean fulltext search indicates (bolding is mine):

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.

So, use the " operator:

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

The bolded section contains the caveat: the results may be slightly different because of the nature of the fulltext index.

这篇关于全文搜索结果与Mysql类似于'%%'查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！