MYSQL-全文搜索功能（全文索引FULLTEXT的使用）

客官°小女子只卖身不卖艺 2022-10-30 12:28 525阅读 0赞

### 文章目录 ###

*  MYSQL-全文搜索功能（全文索引FULLTEXT的使用）
 *   *  1. 全文搜索功能简介
     *  2. 自然语言全文搜索
     *  2. 布尔全文搜索
     *  4. 具有查询扩展的全文本查询

# MYSQL-全文搜索功能（全文索引FULLTEXT的使用） #

## 1. 全文搜索功能简介 ##

**fulltext-search官方资料**：`https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html`

**搜索函数：**  
`match(col1,col2,...) against(expr [search_modifier])`

> search\_modifier:  
> \{  
> IN NATURAL LANGUAGE MODE  
> | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION  
> | IN BOOLEAN MODE  
> | WITH QUERY EXPANSION  
> \}  
> **全文搜索类型：**  
> **①自然语言模式（IN NATURAL LANGUAGE MODE）**：将搜索字符串解释为自然人类语言中的短语，除双引号外无特殊运算符。如果指定了IN NATURAL LANGUAGE MODE修饰符或未给出修饰符，则全文搜索是自然语言搜索 。  
> **②布尔模式（IN BOOLEAN MODE）**：使用特殊查询语言的规则解释搜索字符串。该字符串包含要搜索的单词。它还可以包含指定要求的运算符，以使匹配行中的单词必须存在或不存在，或者其权重应高于或低于平常。  
> **③查询扩展模式（WITH QUERY EXPANSION）**：是对自然语言搜索的修改。搜索字符串用于执行自然语言搜索。然后，将搜索返回的最相关行中的单词添加到搜索字符串中，然后再次执行搜索。该查询返回第二个搜索中的行。

使用`match() against()`语法执行全文搜索。`match()`匹配**要搜索的列**，**所有列必须都有全文索引**。`against()`接收要搜索的**字符串**和**可选修饰符**。

全文索引只用于`INNODB`和`MyISAM`存储引擎的表，且只用于`char、varchar、text`数据类型的列。

mysql提供一个内置的全文解析器ngram，支持中文、日文、韩文。

## 2. 自然语言全文搜索 ##

默认情况下或使用`IN NATURAL LANGUAGE MODE`修饰符。

`MATCH()`函数将根据**全文搜索列**执行自然语言搜索字符串 。

**全文搜索列**：拥有全文索引的一个或多个列。

`AGAINST()`函数接收搜索字符串和可选修饰词。

对于表中的每一行，都会**计算搜索字符串和match()中的全文搜索列的行之间的相似性度量**。

**实例：**

-- 建表
    CREATE TABLE articles (
              id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
              title VARCHAR(200),
              body TEXT,
              FULLTEXT (title,body)
            ) ENGINE=InnoDB;
    
    -- 插入测试数据
    INSERT INTO articles (title,body) VALUES
            ('MySQL Tutorial','DBMS stands for DataBase ...'),
            ('How To Use MySQL Well','After you went through a ...'),
            ('Optimizing MySQL','In this tutorial we show ...'),
            ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
            ('MySQL vs. YourSQL','In the following database comparison ...'),
            ('MySQL Security','When configured properly, MySQL ...');
    
    -- 匹配搜索包含database的行
    SELECT * FROM articles
            WHERE MATCH (title,body)
            AGAINST ('database' IN NATURAL LANGUAGE MODE);

1.  自然语言全文搜索**不区分大小写**
2.  相关性是**非负浮点数**
3.  **相关性**是根据`行（文档）中单词的数量`、`行中唯一单词的数量`、`集合中单词的总数`以及`包含特定单词的行数`来计算的。

-- 计算有相关性的行
    SELECT count(*) FROM articles
    WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
    
    -- 效率更高
    SELECT count(if(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE)>0,1,null)) num FROM articles; -- count函数剔除值为null的行
    
    -- 计算每一行的相关性，不排序
    SELECT id, MATCH (title,body) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
    FROM articles;
    
    -- 计算每一行的相关性，并从高到低排序
    -- 要求select 和 where 子句都使用match函数
    SELECT id, body, MATCH (title,body) AGAINST ('Security implications of running MySQL as root' IN NATURAL LANGUAGE MODE) AS score
    FROM articles
    WHERE MATCH (title,body) AGAINST ('Security implications of running MySQL as root' IN NATURAL LANGUAGE MODE);
    
    -- 任何太短的搜索字符串将被忽略。
    SHOW VARIABLES LIKE'%ft%';   
    -- 要修改以下变量值，必须修改my.cnf配置文件，在[mysqld]后追加，重启mysql服务器才会生效。
    -- ft_min_word_len myisam表中的全文索引的最小搜索长度 4
    -- ft_max_word_len myisam表中的全文索引的最大搜索长度 84
    -- innodb_ft_min_token_size innodb表中的全文索引的最小搜索长度 3
    -- innodb_ft_max_token_size innodb表中的全文索引的最大搜索长度 84
    
    -- 停用词列表的单词将被忽略
    -- innodb表停用词
    innodb_ft_enable_stopword
    innodb_ft_server_stopword_table
    innodb_ft_user_stopword_table
    -- myisam表停用词
    ft_stopword_file

**在INNODB表和MyISAM表测试搜索频繁出现的字符串：**

*  INNODB表：把符合查询的都筛选出来。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70]
 *  MyISAM表：查询频繁出现的次，不会返回结果。![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70 1]  
    **原因在于：MyISAM表会将相关性大于50%的词视为停用词。该过滤技术适用于大型数据集，在小型数据集中，该技术可能会导致流行术语的查询结果不佳。**

## 2. 布尔全文搜索 ##

1. 包含database的行
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('+database' IN BOOLEAN MODE);
    
    2. 在包含database的行中搜索不包含show的行
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('+database -show' IN BOOLEAN MODE);
    
    3. 包含database或show的行
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('database show' IN BOOLEAN MODE);
    
    4. 在包含database的行中找包含show的词
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('+database show' IN BOOLEAN MODE);
    
    5. 在包含database的行中找不包含show的行
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('database -show' IN BOOLEAN MODE);
    
    6. 搜索包含data的行
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('data' IN BOOLEAN MODE);
    7. 搜索包含data开头的词的行
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('data*' IN BOOLEAN MODE);
    
    查询结果按相关性从高到低排序
    8. 降低包含show的行的相关性，包含show的行排在后面
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('database <show' IN BOOLEAN MODE);
    9. 提高包含show的行的相关性，包含show的行排在前面
    SELECT * FROM articles 
    WHERE MATCH (title,body) AGAINST ('database >show' IN BOOLEAN MODE);
    
    /* 加号+：必须包含，前缀 减号-：必须不包含，前缀 无符号：或 >：提高相关性，查询结果靠前，前缀 <：降低相关性，查询结果靠后，前缀 *：前缀查询 */
    1. INNODB表需要所有列都有全文索引才能执行布尔全文搜索。MyISAM表不需要，就是比较慢。
    2. 搜索关键字有最小和最大长度。
    3. 停用词也适用。

## 4. 具有查询扩展的全文本查询 ##

**全文搜索支持查询扩展**，即**盲查询扩展**。第一次搜索某一单词，会将其匹配的隐含含义也添加到搜索字符串中，再次执行搜索。

如**第一次搜索**database，搜索到如mysql、oracle等单词，会将这类单词也放入到搜索词中，**第二次搜索**会将包含这些词但不包含database的词也筛选出来。

通过添加`WITH QUERY EXPANSION`或`IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION`跟随搜索短语，可以启用盲查询扩展（也称为自动相关性反馈）

-- 查询包含database的行
    SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('database' IN NATURAL LANGUAGE MODE);
    -- 查询包含与database相关的词的行
    -- 与database相关的词：mysql、oracle、db2
    SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('database' WITH QUERY EXPANSION);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70]: /images/20221024/0b0c460be9c14ef5a7ad4befd016295d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70 1]: /images/20221024/766b55844f224c8d91417764233fcd36.png