mysql全文搜索使用和踩坑总结

已于 2023-03-31 19:34:27 修改
阅读量965 收藏 2
点赞数 1
分类专栏： mysql 文章标签： mysql 数据库 sql
于 2023-03-31 18:34:50 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhu2mu/article/details/129885643
版权

mysql全文索引使用方式

使用mysql全文索引首先要确认版本，全文索引时在5.7.x开始支持全文索引，由于我的版本是8.0，因此我下文的例子都是基于8.0版本执行的，其次要确认全文索引的数据表引擎，目前只支持 InnoDB和MyISAM。

搜索模式

全文搜索使用关键词
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
其中col1，col2 是列名，expr 搜索的文本内容，search_modifier 表示搜索模式，目前mysql共有3种搜索模式。

1. Natural Language Full-Text Searches 自然语言全文搜索模式,

该方式也是默认查询方式（即查询不带任何模式时），可以看到任何包含了搜索词database 的条目都出现在了结果里面，看例子：

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> 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 ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
2. Boolean Full-Text Searches 布尔全文搜索模式

布尔通过使用+ 和 -号来匹配包含和不包含对应的关键词，看下面例子就是搜索包含关键词mysql的且不包含关键词YourSQL 的查询结果。

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial, we show ...       |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+
3. Full-Text Searches with Query Expansion 带查询扩展的全文搜索模式

带查询扩展的全文索引是指可以对查询条件进行词义的扩展，例如 mysql、oracle、sqlserver都 database 的一种，我们使用带扩展的查询和自言查询的结果不一样，下面👇的例子对比可以看出扩展的查询会把包含mysql的结果也查询出来。

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' WITH QUERY EXPANSION);
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL      | In this tutorial we show ...             |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

全文索引遇到的问题

👆上面的基本是对官方文档的翻译，因此是以英文为主，而我们的使用场景不仅仅有英文，还包含了中文英文混合的查询场景，我简单描述下我的需求：可以查询出任何包含tag(标签) 和content（内容）的中英文结果。
表结构如下：

CREATE TABLE `fulltext_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text COLLATE utf8mb4_general_ci NOT NULL,
  `tag` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `content_tag_fulltext` (`content`,`tag`) WITH PARSER `ngram` 
  ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

遇到的问题

如何根据搜索相关度排序

可以使用 match(content, tag) against(‘xxxx’) as score 来进行相关度评分，然后根据评分的结果排序,如下：

mysql> select content, tag, match(content, tag) against('公网') as score from fulltext_test where match(content, tag) against('公网') order by score desc;
+--------------------------+------+----------------------+
| content                  | tag  | score                |
+--------------------------+------+----------------------+
| 公网 ipai you too        | NULL | 0.010539556853473186 |
| 公网 cvm tke waf         | NULL | 0.010539556853473186 |
| 公网 ip cvm tke waf      | NULL | 0.010539556853473186 |
| 公网 ipb cvm tke waf     | NULL | 0.010539556853473186 |
| 公网 ipb cvm tke waf     | NULL | 0.010539556853473186 |
| 公网 ipb cvm tke waf !   | NULL | 0.010539556853473186 |
| 公网IP                   | cvm  | 0.010539556853473186 |
| 公网IP1                  | cvm  | 0.010539556853473186 |
| 公网IP2                  | cvm  | 0.010539556853473186 |
| 公网IP3                  | cvm  | 0.010539556853473186 |
| 公网IP4                  | cvm  | 0.010539556853473186 |
| 公网 IP                  | cvm  | 0.010539556853473186 |
| 公网 IPa                 | cvm  | 0.010539556853473186 |
| 公网 I love you          | cvm  | 0.010539556853473186 |
| 公网 ipai                | cvm  | 0.010539556853473186 |
+--------------------------+------+----------------------+

搜索词无结果排查

content因为包含中文和英文，不知为何部分关键词可以检索出来，部分关键词就是无法检索出来.

初始化后的数据表

mysql> select * from fulltext_test;
+----+--------------------------+------+
| id | content                  | tag  |
+----+--------------------------+------+
|  1 | 公网IP                   | cvm  |
|  2 | 公网IP1                  | cvm  |
|  3 | 公网IP2                  | cvm  |
|  4 | 公网IP3                  | cvm  |
|  5 | 公网IP4                  | cvm  |
|  6 | I love you               | cvm  |
|  7 | 公网 IP                  | cvm  |
|  8 | 公网 IPa                 | cvm  |
|  9 | 公网 I love you          | cvm  |
| 10 | 公网 ipai                | cvm  |
| 11 | 公网 ipai you too        | NULL |
| 12 | 公网 cvm tke waf         | NULL |
| 13 | 公网 ip cvm tke waf      | NULL |
| 14 | 公网 ipb cvm tke waf     | NULL |
| 15 | ipb cvm tke waf          | NULL |
| 16 | 公网 ipb cvm tke waf     | NULL |
| 17 | 公网 ipb cvm tke waf !   | NULL |
| 18 |  ipb cvm tke waf !       | NULL |
| 19 | waf ipb cvm tke          | NULL |
+----+--------------------------+------+

尝试了几个搜索词进行搜索公网,cvm,waf,tke,ipai 等进行搜索，发现部分英文词出不来, 如waf 和 ipai， 搜索结果如下：

mysql> select * from fulltext_test where match(content, tag) against('公网');
+----+--------------------------+------+
| id | content                  | tag  |
+----+--------------------------+------+
|  1 | 公网IP                   | NULL |
|  2 | 公网IP1                  | NULL |
|  3 | 公网IP2                  | NULL |
|  4 | 公网IP3                  | NULL |
|  5 | 公网IP4                  | NULL |
|  7 | 公网 IP                  | NULL |
|  8 | 公网 IPa                 | NULL |
|  9 | 公网 I love you          | NULL |
| 10 | 公网 ipai                | NULL |
| 11 | 公网 ipai you too        | NULL |
| 12 | 公网 cvm tke waf         | NULL |
| 13 | 公网 ip cvm tke waf      | NULL |
| 14 | 公网 ipb cvm tke waf     | NULL |
| 16 | 公网 ipb cvm tke waf     | NULL |
| 17 | 公网 ipb cvm tke waf !   | NULL |
+----+--------------------------+------+

mysql> select * from fulltext_test where match(content, tag) against('cvm');
+----+--------------------------+------+
| id | content                  | tag  |
+----+--------------------------+------+
| 12 | 公网 cvm tke waf         | NULL |
| 13 | 公网 ip cvm tke waf      | NULL |
| 14 | 公网 ipb cvm tke waf     | NULL |
| 15 | ipb cvm tke waf          | NULL |
| 16 | 公网 ipb cvm tke waf     | NULL |
| 17 | 公网 ipb cvm tke waf !   | NULL |
| 18 |  ipb cvm tke waf !       | NULL |
| 19 | waf ipb cvm tke          | NULL |
+----+--------------------------+------+
8 rows in set (0.00 sec)

mysql> select * from fulltext_test where match(content, tag) against('waf');
Empty set (0.00 sec)

mysql> select * from fulltext_test where match(content, tag) against('tke');
+----+--------------------------+------+
| id | content                  | tag  |
+----+--------------------------+------+
| 12 | 公网 cvm tke waf         | NULL |
| 13 | 公网 ip cvm tke waf      | NULL |
| 14 | 公网 ipb cvm tke waf     | NULL |
| 15 | ipb cvm tke waf          | NULL |
| 16 | 公网 ipb cvm tke waf     | NULL |
| 17 | 公网 ipb cvm tke waf !   | NULL |
| 18 |  ipb cvm tke waf !       | NULL |
| 19 | waf ipb cvm tke          | NULL |
+----+--------------------------+------+
8 rows in set (0.01 sec)
mysql> select * from fulltext_test where match(content, tag) against('ipai');
Empty set (0.00 sec)

这个问题仔细查了下官方文档，我大概估计原因如下，如果我分析有不对还请大家指出

FULLTEXT KEY `content_tag_fulltext` (`content`,`tag`)  WITH PARSER `ngram`

我使用的索引因为想要支持中文索引因此需要使用分词器，mysql提供了 ngram 来进行分词，mysql官网文档fulltext-search-ngram, 分词用的方法非常简单，就是根据全局参数ngram_token_size 来进行分割的，系统默认是2，例如有字符串’abcd’，分词后：‘ab’, ‘bc’, ‘cd’。
其中分词器处理停用词介绍了这么一段
`
ngram Parser Stopword Handling
The built-in MySQL full-text parser compares words to entries in the stopword list. If a word is equal to an entry in the stopword list, the word is excluded from the index. For the ngram parser, stopword handling is performed differently. Instead of excluding tokens that are equal to entries in the stopword list, the ngram parser excludes tokens that contain stopwords. For example, assuming ngram_token_size=2, a document that contains “a,b” is parsed to “a,” and “,b”. If a comma (“,”) is defined as a stopword, both “a,” and “,b” are excluded from the index because they contain a comma.

By default, the ngram parser uses the default stopword list, which contains a list of English stopwords. For a stopword list applicable to Chinese, Japanese, or Korean, you must create your own. For information about creating a stopword list, see Section 12.10.4, “Full-Text Stopwords”.

Stopwords greater in length than ngram_token_size are ignored.
`
大致意思就是说，包含“a,b”的文档被解析为“a,”和“,b”。如果将逗号 (“,”) 定义为停用词，则“a”和“,b”都会从索引中排除，因为它们包含逗号。

那么我们之前我们有3个搜索词出不来结果是不是也因为命中了停用词规则呢？先看下停用词列表

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+

ipai 分词后变成：ip,pa,ai, 每个词都包含了停用词‘i’ 或者‘a’，因此被排除了
waf 和上面同理包含了停用词 ‘a’ 索引不能搜索出来。
我验证了我的猜想，删掉分词的全文索引，就能搜索出来了。

mysql> select * from fulltext_test where match(content, tag) against('ipai');
+----+---------------------+------+
| id | content             | tag  |
+----+---------------------+------+
| 10 | 公网 ipai           | NULL |
| 11 | 公网 ipai you too   | NULL |
+----+---------------------+------+
2 rows in set (0.01 sec)

mysql> select * from fulltext_test where match(content, tag) against('waf');
+----+--------------------------+------+
| id | content                  | tag  |
+----+--------------------------+------+
| 12 | 公网 cvm tke waf         | NULL |
| 13 | 公网 ip cvm tke waf      | NULL |
| 14 | 公网 ipb cvm tke waf     | NULL |
| 15 | ipb cvm tke waf          | NULL |
| 16 | 公网 ipb cvm tke waf     | NULL |
| 17 | 公网 ipb cvm tke waf !   | NULL |
| 18 |  ipb cvm tke waf !       | NULL |
| 19 | waf ipb cvm tke          | NULL |
+----+--------------------------+------+
8 rows in set (0.00 sec)
竹二木
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
专栏目录
MySQL存储过程和函数使用总结
12-14
　存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，那么可能因此需要将函数改造成...
mysql 全文搜索 技巧
10-30
mysql 全文搜索 技巧
MySQL全文索引使用注意事项
davidwkx的博客
02-17 477
Mysql全文索引使用注意事项
Mysql全文索引
青青子衿的博客
01-03 1万+
Mysql 全文检索
MySql全文索引+Ngram
最新发布
liwan09的博客
05-31 942
MySQL 内置的全文解析器使用单词之间的空格作为分隔符，这对于不使用空格做分隔符的语言是一种限制。为了解决这一限制，MySQL提供了一个支持中文、日文和韩文（CJK）的ngram全文解析器。ngram 全文解析器支持InnoDB和MyISAM的全文索引。ngram解析器将文本序列分词为连续的n个字符。例如，你可以使用ngram全文解析器为“早上好呀”进行不同值的n分词。n=1: '早', '上', '好', '呀'n=2: '早上', '上好', '好呀'n=3: '早上好', '上好呀'
MySQL 全文检索的实现
古时的风筝
11-21 6291
这有朋友聊到他们的系统中要接入全文检索，这让我想起了很久以前为一个很古老的项目添加搜索功能的事儿。一提到全文检索，我们首先就会想到搜索引擎。也就是用一个词、一段文本搜索出匹配的内容。一般这种技术都有对应的实现方式，ES（ElasticSearch）就是专门干这个的，如果你们的业务中明确需要全文检索，或者简单一点说，需要根据关键词搜索出匹配的内容，那就直接用 ES 就好了。无论你怎么调研，都不推荐使用 MySQL 实现这种需求，显而易见，MySQL 作为关系型数据库，本身就不适合做搜索这种需求。
聊聊Mysql中的全文检索
爱琴孩的博客
09-03 1874
我们都知道 InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效，但有时需求就是如此，类似这样的需求还有很多，例如，搜索引擎需要根据用户数据的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是B+树索引能很好完成的工作。通过数值比较，范围过滤等就可以完成绝大多数我们需要的查询了。但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较，全文索引就是为这种场景设计的。
MySQL 之全文索引
热门推荐
潜心做事的博客
04-14 10万+
最近在复习数据库索引部分，看到了 fulltext，也即全文索引，虽然全文索引在平时的业务中用到的不多，但是感觉它有点儿意思，所以花了点时间研究一下，特此记录。 引入 概念 通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。 你可能会说，用 like...
全文检索-MYSQL
Ryon_zhan的博客
07-05 404
可扩展性好：MySQL 是一个成熟稳定的数据库系统，支持分布式和集群部署，可以根据需要进行水平和垂直扩展，满足不同规模和需求的应用场景。：MySQL 的全文搜索不支持复杂的高级查询语法，例如通配符、正则表达式、近义词和近似搜索等，需要在应用程序中进行额外处理。简单易用：使用 MySQL 进行全文搜索不需要学习额外的查询语言或 API，只需要掌握 SQL 语言即可，易于上手和使用。搜索能力有限：相比于一些专业的搜索引擎软件，MySQL 的全文搜索能力有一定限制，例如无法进行复杂的语义分析和推荐。
mysql全文模糊搜索MATCH AGAINST方法示例
09-09
MySQL还提供了一种布尔模式的全文检索，通过使用`+`、`-`、空格和`~`等符号来控制查询的逻辑： - `+`表示AND，即必须包含。 - `-`表示NOT，即不包含。 - 空格表示OR，即至少包含两者中的一个。 - `~`表示异或，返回...
mysql全文搜索 sql命令的写法
09-11
总的来说，MySQL全文搜索在英文环境下使用相对简单，但在处理中文时需要额外的分词步骤。如果你打算在MySQL中实现中文全文搜索，你需要考虑使用第三方分词库，并将分词结果适当地整合到SQL查询中。这个过程可能会...
mysql全文索引
weixin_41864357的博客
10-23 1167
mysql全文索引
两万字详解如何使用MySQL的全文本搜索功能进行高级的数据查询和选择
白话机器学习
08-21 973
邻近搜索是许多全文本搜索支持的一个特性,它能搜索相邻的词(在相同的句子中、相同的段落中或者在特定数目的词的部分中,等等)。在索引之后,使用两个函数 Match() 和 Against() 执行全文本搜索,其中 Match() 指定被搜索的列, Against() 指定要使用的搜索表达式。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。),所以也被检索出来。如果指定多个搜索项,则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。
Mysql 简单实现全文检索（FULLTEXT）
my476530的博客
12-02 1981
Mysql 简单实现全文检索（FULLTEXT） MyISAM 和 InnoDB 存储引擎均支持;
MySQL中文全文检索
Java牛马的博客
04-17 1373
常规数据库搜索都是用 like 语句，但是like 语句是不能利用索引的，查询效率极其低下。这也就是为什么很多功能都只提供标题搜索的原因，因为如果搜索内容，几万数据就跑不动了。Mysql 全文索引是专门为了解决模糊查询提供的，可以对整篇文章预先按照词进行索引，搜索效率高，能够支持百万级的数据检索。
MySQL(InnoDB剖析):29---全文检索（倒排索引、全文索引/全文检索）
董哥的黑板报
02-13 5133
一、引言 演示说明 前面介绍过了，B+树索引的特点是可以通过索引字段的前缀进行查找。例如对于下面的查询B+树是支持的 select * from blog where content like 'xxx%'; 但是有时候我们要查询的是博客的内容中含有“xxx”，而不是以“xxx”开头，所以应该是下面的SQL语句 select * from blog where content like......
MySQL索引之全文索引（FULLTEXT）
coco3600的博客
09-08 1549
MySQL索引之全文索引（FULLTEXT） MySQL创建全文索引 使用索引时数据库性能优化的必备技能之一。在MySql数据库中，有四种索引：聚焦...
【MySQL】全文索引详解
qq_54169998的博客
01-13 4283
全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。MySQL从3.23.23版本开始支持全文索引，MySQL5.6以前只能在存储引擎为MyISAM的数据表上创建全文索引，5.6之后InnoDB开始支持全文索引(5.7之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

竹二木

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值