mysql搜索结果,全文搜索结果与Mysql类似于'%%'查询

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.