MySQL 全文检索方案 - FULLTEXT search 索引

Dear 丶 2022-11-02 14:29 232阅读 0赞

### 1.产生 ###

1.1.MySQL LIKE语句 ‘%text%’ 是无法使用索引的，Like 能用上索引的唯一可能 `LIKE 'text%'`，大多数需求不能满足

1.2.mysql 5.7.6 之后支持了中日韩文的全文检索

### 2.特点 ###

1.实际上，就是增加了一个新的索引类型 FULLTEXT
    2.FULLTEXT 索引只能用于 【InnoDB】 及 【MyISAM 】表，可以应用于 CHAR，VARCHAR，TEXT 字段
    3.提供了内置的支持 CJK （中文，日文，韩文）的 ngram 分词器
    4.对于大量数据，先插数据，后建 FULLTEXT 索引更快，相对于先建 FULLTEXT 索引，后插数据而言
    5.我使用了mariadb,需要用到的引擎是Mroonga，这个需要去手动下载

### 3.SQL ###

3.1.创建

ALTER TABLE `test` ADD FULLTEXT INDEX `ft_title_body` (`name`, `value`) WITH PARSER ngram;
    
    ## 翻译：
    ## ALTER TABLE `表名` ADD FULLTEXT INDEX `索引名` (`字段1`, `字段2`) WITH PARSER ngram;
    ## 索引那里可以是一个字段也可以是多个，这和原来sql没什么区别，单一索引和复合索引

3.2.测试检索

SELECT * FROM test where match(name, value) against('我也有 原色棉 开衫' IN BOOLEAN MODE);
    
    ## 翻译：
    ## SELECT * FROM 表名 where match(复合索引的字段1, 复合索引的字段2) against('查询的值，多个分词的话只要加空格就好了' IN BOOLEAN MODE);
    ## 有个情况是：中文单个字符及英文单个字母，默认是不需要进行检索的。会返回空。

3.3.开发遇到的问题  
3.3.1.我使用了`IN BOOLEAN MODE`，这个意思是完全匹配，就是比如要查询的值是java，但数据库对应字段存的是Javac，这样就查不出来，有一个好处是精确匹配了，但是好像默认的英文大小写也精确了

3.3.2.有一个场景需要搜索的**每一个条件都匹配**，比如上方sql要查`我也有 原色棉 开衫`这三个条件，如果默认的话，他是匹配度大于50%就会检索出来，不是我想要的，这时候要在每个条件前加`+`号，就变成`+我也有 +原色棉 +开衫`，这样会过滤出来三个条件都符合的数据

类似的还有：  
4.全文布尔操作符  
![布尔][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_16_color_FFFFFF_t_70]

逻辑全文搜索支持下面的操作符： 
    
    + 
    一个领头的加号表示，该词必须出现在每个返回的记录行中。 
    
    - 
    一个领头的减号表示，该词必须不出现在每个返回的记录行中。 
    
    缺省的 (当既没有加号也没有负号被指定时)词是随意的，但是包含它的记录行将被排列地更高一点。这个模仿没有 IN BOOLEAN MODE 修饰词的 MATCH() ... AGAINST() 的行为。 
    
    < > 
    这两个操作符用于改变一个词的相似性值的基值。< 操作符减少基值，> 操作符则增加它。参看下面的示例。 
    
    ( ) 
    圆括号用于对子表达式中的词分组。 
    
    ~ 
    一个领头的否定号的作用象一个否定操作符，引起行相似性的词的基值为负的。它对标记一个噪声词很有用。一个包含这样的词的记录将被排列得低一点，但是不会被完全的排除，因为这样可以使用 - 操作符。 
    
    * 
    一个星号是截断操作符。不想其它的操作符，它应该被追加到一个词后，不加在前面。 
    
    " 短语，被包围在双引号"中，只匹配包含这个短语(字面上的，就好像被键入的)的记录行。

[参考链接][Link 1]

### 4.是否支持长文本 ###

我也不知道，昨天开会时候我说不可以，被一个开发怼的快失忆了，看他那么嚣张的样子应该是可以的吧，有懂的可以告诉我一声，功能都做完了现在还很迷茫

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_16_color_FFFFFF_t_70]: /images/20221024/a6bf63c8b8384cb7992824369a472d94.png
[Link 1]: https://www.cnblogs.com/itxiongwei/p/7064244.html