MySQL必知必会—文本搜索篇

方旭

MySQL必知必会—概念篇

MySQL必知必会—安装应用篇

MySQL必知必会—检索数据篇

MySQL必知必会—联结表和高级查询篇

下面用到的数据库文件可在 mysql_scripts 找到。


全文本搜索

并非所有引擎都支持全文本搜索,例如 MyISAM 支持全文本搜索,InnoDB 不支持。

在创建表时启用全文本搜索, CREATE TABLE 语句接受 FULLTEXT 子句,它可以对后面的一个或多个表建立索引,MySQL自动维护该索引,在增加、更新或删除行时,索引随之自动更新。FULLTEXT 也可以在表创建之后再指定。

### 看一下 productnotes 表的创建描述
CREATE TABLE `productnotes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text,
  PRIMARY KEY (`note_id`),
  FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

创建索引后就可以用 Match() 和 Against() 执行全文本搜索,其中 Match() 指定被搜索的列, Against() 指定要使用的搜索表达式。

### 搜索出 node_text 中包含 rabbit 的。

### 先用 LIKE 实现
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';
+-----------------------------------------------+
| Quantity varies, …… for use as rabbit bait. |
| Customer complaint: rabbit has …… |
+-----------------------------------------------+

### 使用文本搜索实现,可以看到 rabbit 排在第三个词的文本比排在第20个词的文本排序高。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
+-----------------------------------------------+
| Customer complaint: rabbit has …… |
| Quantity varies, …… for use as rabbit bait. |
+-----------------------------------------------+

### 可以使用下面的语句查看排序的等级
SELECT note_text, Match(note_text) Against('rabbit') AS rank1 FROM productnotes;
### 除了查出来的两个有数据,其他的等级都是0
| Customer complaint: rabbit has …… | 的等级是 1.6408053636550903
| Quantity varies, …… for use as rabbit bait. | 的等级是 1.5905543565750122


### 使用查询扩展 WITH QUERY EXPANSION ,找出所有提到 anvils 的注释,还要找出与当前搜索有关的所有其他行,即使它们不包含 anvils
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
### 会返回7行,但是只有第一行有 anvils ,第二行虽然与 anvils 无关,但因为它包含了第一行的两个词,所以也被检索出来。

注意

  • 传递给 Match() 的值必须与 FULLTEXT() 定义中的相同。如果指定多个列,必须列出他们,而且次序正确。
  • 经过比较可以发现,文本搜索是默认带有排序的,LIKE 搜索出来的结果是随意的,按照查询的先后输出。
  • 文本搜索的等级根据行中词的数目,唯一词的数目,整个索引中词的总数,以及包含该词的行的书目计算出来。
  • 在索引全文本数据时,短词被忽略且从索引中排除。短词的定义时那些具有3个或3个一下字符的词(如果需要,这个数目可以改)
  • 许多词出现的频率很高,搜索他们没用,MySQL 规定了一条 50% 规则,如果一个词出现50%以上的行中,则将它作为一个非用词忽略。50%规则不用于 IN BOOLEAN MODE。
  • 如果表中的行数少于3行,则全文本搜索不返回结果
  • 忽略词中的单引号。例如,don't 索引为 dont
  • 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果

布尔文本查询

IN BOOLEAN MODE,即使没有 FULLTEXT 索引,也可以使用

### 匹配包含 heavy 但不包含任意以 rope 开始的词的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

### 匹配词 safe 和 combination, 降低后者的等级。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

全文本布尔操作符

布尔操作符 说 明
+ 包含,词必须存在
- 排除,词必须不出现
> 包含,而且增加等级值
< 包含,且减少等级值
() 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、 排列等)
~ 取消一个词的排序值
* 词尾的通配符
"" 定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)

一个持续更新的github笔记,链接地址:Front-End-Basics,可以watch,也可以star。

此篇文章的地址:MySql必知必会

阅读 698

陈工移山
移山之志,一点一滴。

移山之志,一点一滴。

3.2k 声望
435 粉丝
0 条评论
你知道吗?

移山之志,一点一滴。

3.2k 声望
435 粉丝
宣传栏