mysql fulltext 没有结果_MySql 全文检索两个字符的内容无法得到结果

问题描述

数据库中有如下的地址信息表，需要实现一个更具用户输入的任何内容进行搜索可能匹配的地址信息。

-- MySQL版本: 5.7.25

CREATE TABLE Address

(

id BIGINT NOT NULL AUTO_INCREMENT,

address VARCHAR(100) NOT NULL DEFAULT '',

city VARCHAR(50) NOT NULL DEFAULT '',

state VARCHAR(50) NOT NULL DEFAULT '',

country VARCHAR(50) NOT NULL DEFAULT '',

zip_code VARCHAR(10) NOT NULL DEFAULT '',

FULLTEXT ftidx_location(address, city, state, country, zip_code)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into Address(city, state) values ('Irving', 'TX');

容易想到利用如下的sql进行检索。

-- 这里的 ${input} 为用户输入的内容

select * from Address where match(address, city, state, country, zip_code) against (${input});

然而对于太短的输入，如 "TX"，即使数据库中存在 state = TX 的数据，该SQL也是无法检索到任何结果。或者输入 "Irvin" 也是无法查找到内容的。下面将对该问题进行分析和解决，使用"Irvin,TX"作为用户输入进行分析(不含双引号)。

原因分析

实现使用的是MySQL的FULLTEXT INDEX对(address, city, state, country, zip_code)进行了索引。FULLTEXT INDEX的配置保留了MySQL的默认配置，如下：

mysql> SHOW VARIABLES LIKE '%ft%';

+---------------------------------+----------------+

| Variable_name | Value |

+---------------------------------+----------------+

| ft_boolean_syntax | + ->

| ft_max_word_len | 84 |

| ft_min_word_len | 4 |

| ft_query_expansion_limit | 20 |

| ft_stopword_file | (built-in) |

| innodb_ft_aux_table | |

| innodb_ft_cache_size | 8000000 |

| innodb_ft_enable_diag_print | OFF |

| innodb_ft_enable_stopword | ON |

| innodb_ft_max_token_size | 84 |

| innodb_ft_min_token_size | 3 |

| innodb_ft_num_word_optimize | 2000 |

| innodb_ft_result_cache_limit | 2000000000 |

| innodb_ft_server_stopword_table | |

| innodb_ft_sort_pll_degree | 2 |

| innodb_ft_total_cache_size | 640000000 |

| innodb_ft_user_stopword_table | |

+---------------------------------+----------------+

FULLTEXT索引是按照“词”进行的索引，MySQL默认的分词方法是所有非字母和数字的特殊符号都是分词符(如果希望对中文进行分词，则可以使用MySQL内置的ngram全文检索插件)。按照分词方法，"Irving,TX" 将被划分为 "Irving" 和 "TX" 两个词。

再看下配置的内容，其中 innodb_ft_min_token_size 表示最短的索引词项，也就是只会对3个英文字符或者3个英文字符以上的关键字进行建立索引操作。MySQL不会对"TX"创建索引，这也就是没法搜索到"TX"的数据的原因。而之所以没法搜索到"Irvin"，是因为Fulltext是对“词”进行构建索引，也就是索引文件中只有”Irving“的索引，没有“Irvin”的索引。

解决方法

• 修改FULLTEXT INDEX配置

修改最小词项长度为2，允许对长度为2的词进行索引。并使用IN BOOLEAN MODE匹配不完整单词。觉得1太小了，一般的单词都不会是一个字母的，而且如果这个数值太小，会导致索引文件过大，不利于索引的更新。因而修改为2就行了。在MyISAM数据库引擎中使用的是ft_min_word_len，而InnoDB中使用的是innodb_ft_min_token_size。在修改之前执行，即使数据库中含有state=TX的数据，查询的结果还是会为空。

select * from Address where match(address, city, state, country, zip_code) against ('TX');

• 修改 my.cnf，在 [mysqld] 后面加入配置项。

sudo vim /etc/mysql/my.cnf

• 配置内容

innodb_ft_min_token_size=2

ft_min_word_len=2

• 重启mysql服务。

sudo service mysql restart

• 重新构建索引文件。

对于myisam，使用如下指令。

REPAIR TABLE Address QUICK;

而对于使用InnoDB的表，可以使用如下指令对表进行索引的重新构建。该操作会获取到表的读锁。

ALTER TABLE Address ENGINE=INNODB;

使用优化指令也可以起到同样的作用，同时这个指令会完成更多的优化作用。OPTIMIZE TABLE运行过程中，MySQL会锁定表。

OPTIMIZE TABLE Project;

-- 执行之后会返回如下信息，但实际上是执行成功的

-- Table does not support optimize, doing recreate + analyze instead

• 查看是否生效。

show variables like 'innodb_ft_min_token_size';

show variables like 'ft_min_word_len';

在修改之后执行，如果数据库中含有state=TX的数据都会被查询出来。

select * from Address where match(address, city, state, country, zip_code) against ('TX');