MySql 全文检索两个字符的内容无法得到结果

在IT行业这个发展更新速度很快的行业，只有不停止的学习，才不会被行业所淘汰。如果你是数据库学习者，那么本文《MySql 全文检索两个字符的内容无法得到结果》就很适合你！本篇内容主要包括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的

mysql> SHOW VARIABLES LIKE '%ft%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| ft_boolean_syntax               | + ->

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

再看下配置的内容，其中

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

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

   sudo vim /etc/mysql/my.cnf

   配置内容

   innodb_ft_min_token_size=2
   ft_min_word_len=2

2. 重启mysql服务。

   sudo service mysql restart

3. 重新构建索引文件。

   对于使用MyISAM的表需要手动修复。详细见：what to do when ' repair table ' query won't work in mysql? 以及 Fine-Tuning MySQL Full-Text Search或者其中文翻译微调MySQL全文搜索。

   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

4. 查看是否生效。

   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'); 

使用

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

该方法依赖于MySQL自身的配置，如果确定要使用

select * from Address where concat(address, ',', city, ',', state, ' ', zip_code, ' ', country) like '%Irvin%TX%';

使用该方法，将会失去对地址信息的索引，并因为使用了

select * from Address where full_address like '%Irvin%TX%';

该方法主要是利用空间换时间，解决了方法二中每次查询都需要拼接字符串的耗时操作。

方法四: 使用专业的搜索引擎

使用更为专业的搜索引擎，如 Elasticsearch 或者 Solr。

参考

• 为什么要设置 Mysql 的 ft_min_word_len=1 @csdn
• MySQL使用全文索引(fulltext index) @cnblogs
• what to do when ' repair table ' query won't work in mysql? @stackoverflow
• Fine-Tuning MySQL Full-Text Search @mysql
• 微调MySQL全文搜索 @docs4dev
• FullText Search Innodb Fails, MyIsam Returns Results @stackoverflow
• 实例说明optimize table在优化mysql时很重要 @51yip
• InnoDB/MyisAM存储引擎支持中文全文索引 @51cto

今天关于《MySql 全文检索两个字符的内容无法得到结果》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！