MySQL 5.7 miss matching against two-Chinese-character words

Question

Table tb:

title                       keywords
企业贷款，该如何看懂银行流水     出口退税,贷款,现金流量表,利润表
最高人民法院关于审理民间        企业贷款,合同法,贷款
中国宏观经济数据点评           企业贷款,贷款

ngram_token_size=2
FULLTEXT INDEX `keywords_title`(`keywords`, `title`)

Now I match keywords and title with a four-Chinese-character word '企业贷款'

select * from tb where MATCH (keywords, title) AGAINST ('企业贷款');

|[
title                       keywords
企业贷款，该如何看懂银行流水     出口退税,贷款,现金流量表,利润表
最高人民法院关于审理民间        企业贷款,合同法,贷款
中国宏观经济数据点评           企业贷款,贷款
]|

But When I match them with a two-chinese-character word '贷款', no result matched.

select * from tb where MATCH (keywords, title) AGAINST ('贷款');
|[
]|

Answer 1

ALTER TABLE tb
DROP INDEX `keywords_title ` ,
ADD FULLTEXT INDEX `keywords_title ` (`keywords` ASC, `title` ASC)  /*!70000 WITH PARSER `ngram` */ 

/*!70000 WITH PARSERngram*/ is necessary!

Answer 2

/*!70000 WITH PARSER ngram*/  

It mean the syntax will only be executed when the MySQL version >= 7.0.0, so you didn't use the ngram parser at all.

In my advice, you still need to use the ngram parser, because the default full-text parser can't handle the Chinese string well (This also mentioned in the document).

I ran some tests on MySQL 5.7 & 8.0 server, got same results:

For MyISAM

X: return empty result  O: return rows contain the pattern

built-in parser
  IN NATURAL LANGUAGE MODE (default)
    企业贷款    X
    贷款        X
  IN BOOLEAN MODE
    企业贷款    O
    贷款        X

ngram
  IN NATURAL LANGUAGE MODE (default)
    企业贷款    X
    贷款        X
  IN BOOLEAN MODE
    企业贷款    O
    贷款        O

For InnoDB

built-in parser
  IN NATURAL LANGUAGE MODE (default)
    企业贷款    O
    贷款        X
  IN BOOLEAN MODE
    企业贷款    O
    贷款        X
ngram
  IN NATURAL LANGUAGE MODE (default)
    企业贷款    O
    贷款        O
  IN BOOLEAN MODE
    企业贷款    O
    贷款        O

First, if you search in natural language mode, there is a 50% threshold in MyISAM engine. Both 企业贷款 and 贷款 occur in more than half of the rows, MySQL will treat them as stopwords (doc), so it return nothing.

Next, if you use the built-in parser, it causes strange results, since the parser can't process these ideographic languages well.

There are two reasons for your situation:

1. Not like ngram, the built-in parser only can use some symbols (in this case, is comma) to parse a Chinese sentence. The parser only catches 企业贷款 token and doesn't catch it's sub-string 贷款.
2. The built-in parser treats a Chinese token in the same way as it treats an English token. The length of 贷款 is smaller than the minimum length of a word that considered to index (check ft_min_word_len setting for MyISAM, innodb_ft_min_token_size for InnoDB), so it will not be indexed even though 贷款 occurs in the keywords field many times.

Answer 3

The MYSQL document explains the purpose of ngram Full-Text Parser

The built-in MySQL full-text parser uses the white space between words as a delimiter to determine where words begin and end, which is a limitation when working with ideographic languages that do not use word delimiters. To address this limitation, MySQL provides an ngram full-text parser that supports Chinese, Japanese, and Korean (CJK).

ngram_token_size=2 (the default value) let you search the 2 characters Chinese words in your case.

/*!70000 WITH PARSER ngram */ means if MySQL version >= 7.0.0 executes it. FTS was introduced in mysql 5.6, so /*!50600 WITH PARSER ngram */ should be enough.