第18章全文本搜索 · 波仔的 AI 编程实战课

📄 一句话概括

LIKE 是「有没有这几个字符」，全文本搜索是「有没有这个词、有多相关」。这一章讲清三件事：怎么建索引、为什么它是按词匹配、相关性怎么排。 Demo 里 LIKE '%note%' 只命中一条，而 FTS 里 ship 和 ships 是两个词、各认各的； note* 加星号才做前缀匹配；bm25() 给相关性打分，ORDER BY rank 就排好了。

📝 本章和前面几章的关系

上一章的 UNION 让结果变长，这一章换的是「怎么找」——从逐行扫字符，变成查索引里的词。 这一章是本课唯一讲「索引结构」的地方：FULLTEXT 是倒排索引， 和普通 B 树索引是两套东西（第 8 章提过索引失效，那是 B 树的事）。

一、为什么需要全文本搜索

LIKE 有三个毛病：逐行扫全表（数据一大就慢）、不知道哪条更相关、按字符比对（fuse 会把 confuse 也算命中）。 全文本搜索换了个思路：先把文本切成词、建一张「词到文档」的倒排索引，查的时候直接跳过去，不扫表。 代价是要额外维护索引：建索引占空间，每次插入更新都要同步。 所以不是所有文本列都值得建——只有真的要搜的那几列才建。

二、建索引：FULLTEXT 与 FTS5

MySQL 的写法：建表时声明 FULLTEXT(列)，或事后 CREATE FULLTEXT INDEX，查询用 MATCH(列) AGAINST('词')。教材原码，本沙箱不执行。 SQLite 的写法：建一张 FTS5 虚拟表，用 content= 挂到原表上，再 INSERT ... VALUES ('rebuild') 灌一遍数据。查询用 MATCH '词'。 两边的思路一样：都是「另建一份按词组织的索引」， 只是 MySQL 把索引挂在表上，SQLite 把索引做成一张虚拟表。 MySQL 5.6 之前只有 MyISAM 支持全文本，InnoDB 从 5.6 起才支持，教材那版正好在这个切换期，所以书里专门提了一句。

三、按词不按子串

这是全文本和 LIKE 最根本的差别：ship 只命中含 ship 的那条， ships 另算一个词，各认各的。 要前缀匹配得写 note*（星号表示「以这几个字母开头的词」）。 要词干归并（ships 与 ship 算一个）得开词干分析器， MySQL 用 WITH QUERY EXPANSION 或专门的 parser，SQLite 用 porter 分词器。 这条差异直接影响搜索结果：用户搜 ship 找不到写 ships 的文档， 不是 bug，是「按词」的必然。 要放宽就得显式加通配或换分词器。

四、布尔模式与相关性排序

布尔模式让搜索从「一个词」变成「一组条件」：OR 任一个、AND 都要有、NOT 排除； MySQL 还支持 +（必须有）、-（必须没有）。 相关性排序是全文本的另一半价值：bm25() 给每条命中文档打分，越小越相关， ORDER BY rank 就按相关度排。 LIKE 永远做不到这件事——它只知道「命中没命中」， 不知道哪条更该排前面。 搜索框里的「最相关」全靠这一列。

五、MySQL 侧的写法与本沙箱的差异

教材的查询长这样：WHERE MATCH(note_text) AGAINST('fuse')； 布尔模式加 IN BOOLEAN MODE，能写 + - * "" 这些符号。 本沙箱没有 MATCH...AGAINST，用 FTS5 的 MATCH '词' 顶上， 布尔语法略有差别（SQLite 用 AND／OR／NOT 大写，MySQL 用符号）。 要记住的是共同点：按词建索引、按词查、能算相关性。具体符号是方言，换库时查一下就行。

六、小结

教材小结：全文本搜索按词建索引，MATCH...AGAINST 查询，支持布尔模式，能按相关性排序。 本课补三条：一是按词不按子串，前缀要加星号；二是相关性排序是 LIKE 给不了的； 三是索引要维护成本，只为真要搜的列建。

七、动手 Demo：从 LIKE 到倒排索引

Demo 六段：先看 LIKE 能做什么、做不到什么，再建 FTS5 索引，然后验证「按词」这条规则，最后用 bm25 排序、对照 MySQL 的写法。 注意 ship 与 ships 各命中一条——这一条就是全章的核心。

import sqlite3
con = sqlite3.connect(":memory:")

# LIKE：按字符比对，逐行扫
con.execute("SELECT note_id, note_text FROM productnotes "
            "WHERE note_text LIKE '%fuse%'")
# 建 FTS5 虚拟表，内容和 productnotes 挂钩
con.execute("CREATE VIRTUAL TABLE notes_fts USING fts5("
            "prod_id, note_text, content='productnotes', "
            "content_rowid='note_id')")
con.execute("INSERT INTO notes_fts (notes_fts) VALUES ('rebuild')")
# 按词检索
con.execute("SELECT rowid, note_text FROM notes_fts WHERE notes_fts MATCH 'fuse'")
# 按词不按子串：ship 与 ships 各认各的
con.execute("SELECT rowid FROM notes_fts WHERE notes_fts MATCH 'ship'")
con.execute("SELECT rowid FROM notes_fts WHERE notes_fts MATCH 'ships'")
# 前缀匹配要加星号
con.execute("SELECT rowid FROM notes_fts WHERE notes_fts MATCH 'note*'")
# 布尔组合与相关性排序
con.execute("SELECT rowid FROM notes_fts WHERE notes_fts MATCH 'fuse OR ship'")
con.execute("SELECT rowid, bm25(notes_fts) AS rank FROM notes_fts "
            "WHERE notes_fts MATCH 'fuse OR ship OR notes' ORDER BY rank")

实跑输出：

--------------------------------------------------------------------
第十八课：全文本搜索
--------------------------------------------------------------------

  【一】LIKE 的局限：它是子串匹配
  SQL> SELECT note_id, SUBSTR(note_text, 1,
       34) AS txt FROM productnotes WHERE note_text LIKE '%fuse%'
  note_id     txt
  -----------------------------------------------
  103         Customer complaint: fuse too short
  （1 行）
  **LIKE 在这里能用，但它有三个毛病**：逐行扫全表、不知道哪个更相关、
  **而且按字符比对，`fuse` 和 `confuse` 也算命中。**
  SQL> SELECT note_id, SUBSTR(note_text, 1,
       34) AS txt FROM productnotes WHERE note_text LIKE '%note%'
  note_id     txt
  -----------------------------------------------
  101         Replaces unused product notes, shi
  （1 行）
  **这一条只命中 101**，靠的是 notes 里那四个字母，**全文本搜索则是按「词」算的。**

  【二】建索引：MySQL 是 FULLTEXT，SQLite 是 FTS5 虚拟表
  **MySQL**：先 `CREATE FULLTEXT INDEX` 或在建表时声明，之后用 `MATCH(列) AGAINST('词')` 查。
  **教材原码，本沙箱不执行。**
  **SQLite**：建一张 FTS5 虚拟表，内容和 productnotes 挂钩：
  `CREATE VIRTUAL TABLE notes_fts USING fts5(prod_id, note_text, content='productnotes', content_rowid='note_id')`
  **然后 `INSERT INTO notes_fts (notes_fts) VALUES ('rebuild')` 灌一遍数据。** 之后查它，
  语法是 `WHERE notes_fts MATCH '词'`。
  SQL> SELECT rowid, prod_id, SUBSTR(note_text, 1,
       34) AS txt FROM notes_fts WHERE notes_fts MATCH 'fuse'
  rowid     prod_id     txt
  ---------------------------------------------------------
  103       TNT2        Customer complaint: fuse too short
  （1 行）
  **一行，就是那条投诉。** 全表四行，引擎只碰了索引里命中的那一块。

  【三】按词匹配，不是按子串
  SQL> SELECT rowid, SUBSTR(note_text, 1, 34) AS txt FROM notes_fts WHERE notes_fts MATCH 'ship'
  rowid     txt
  ---------------------------------------------
  104       JetPack 2000 requires a licence to
  （1 行）
  SQL> SELECT rowid, SUBSTR(note_text, 1,
       34) AS txt FROM notes_fts WHERE notes_fts MATCH 'ships'
  rowid     txt
  ---------------------------------------------
  101       Replaces unused product notes, shi
  （1 行）
  **`ship` 只命中 104，`ships` 只命中 101**——它们是两个词，各认各的。
  **这就是「按词」的意思**，也是它比 LIKE 准的地方。
  SQL> SELECT rowid, SUBSTR(note_text, 1,
       34) AS txt FROM notes_fts WHERE notes_fts MATCH 'note*'
  rowid     txt
  ---------------------------------------------
  101       Replaces unused product notes, shi
  （1 行）
  **加星号是前缀匹配**，`note*` 命中 notes。**不加星号就只能整词相等。**

  【四】布尔组合与相关性排序
  SQL> SELECT rowid, SUBSTR(note_text, 1,
       34) AS txt FROM notes_fts WHERE notes_fts MATCH 'fuse OR ship'
  rowid     txt
  ---------------------------------------------
  103       Customer complaint: fuse too short
  104       JetPack 2000 requires a licence to
  （2 行）
  **OR 是「任一个词命中」**，两行。**还能写 AND（都要有）、NOT（排除）。**
  SQL> SELECT rowid, bm25(notes_fts) AS rank, SUBSTR(note_text, 1,
       30) AS txt FROM notes_fts WHERE notes_fts MATCH 'fuse OR ship OR notes' ORDER BY rank
  rowid     rank      txt
  ---------------------------------------------------
  103       -0.94     Customer complaint: fuse too s
  104       -0.85     JetPack 2000 requires a licenc
  101       -0.81     Replaces unused product notes,
  （3 行）
  **`bm25()` 给相关性打分，越小越相关**，`ORDER BY rank` 就按相关度排。
  **这是 LIKE 永远做不到的事**——它只知道「命中没命中」。

  【五】MySQL 侧长什么样（教材原码，不执行）
  `SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('fuse')`
  **布尔模式**（能写 + 必须、- 排除、* 前缀）：
  `... AGAINST('+fuse -ship' IN BOOLEAN MODE)`
  **MySQL 5.6 之前只有 MyISAM 支持全文本**，InnoDB 从 5.6 起才支持，**教材那版正是这个切换期**，
  所以书里专门提醒了一句。

  【六】该怎么选
  需求                          用哪个
  --------------------------------------------
  找包含某几个字的行            LIKE
  找含某个词的文档、要排序      全文本
  几百万行里搜关键词            全文本
  查一列里有没有固定串          LIKE
  **一句话：LIKE 是「有没有这几个字符」，全文本是「有没有这个词、有多相关」。**

第一段 LIKE。 %fuse% 命中那条投诉，%note% 只命中 101，靠的是字符子串，不是词。 第二段建索引。 FTS5 虚拟表挂到 productnotes 上，四行数据全进了索引。 第三段按词查。 MATCH 'fuse' 一行，引擎没扫表，直接查索引。 第四段核心对比。 ship 命中 104、ships 命中 101， 同根不同词，各认各的；note* 加星号才做前缀。 第五段布尔与排序。 fuse OR ship 两行； bm25 打分 -0.94／-0.85／-0.81，越小越相关，排出来第一条就是那条投诉。 第六段对照。 MySQL 用 MATCH...AGAINST，SQLite 用 MATCH，思路一样、符号不同。

30 秒实验

把 MATCH 'fuse' 换成 MATCH 'fus'，还能命中吗？为什么？
用 MATCH 'note OR seed' 查一次，看命中几条。
把 bm25 那句的 ORDER BY rank 去掉，行序是随机的还是固定的？

常见误区

以为全文本和 LIKE 一样能匹配子串。它按词匹配，fus 匹配不到 fuse。
以为不用维护索引。每次增删改都要同步，代价在这里。
用全文本去查短字符串。只查一个字符或几个字母的固定串，LIKE 更直接。

自测三问（点开对答案）

1. 全文本搜索和 LIKE 最根本的区别是什么？ —— 全文本按「词」建索引查询，能算相关性、能布尔组合；LIKE 按字符子串逐行扫。 2. 为什么搜 `ship` 找不到写了 `ships` 的文档？ —— 因为它们是两个不同的词，全文本按整词匹配；要前缀匹配得写 `ship*`，要词干归并得分词器。 3. `bm25()` 的分数越小表示什么？ —— 越小越相关，所以按相关度排序就写 `ORDER BY rank`（升序）。

取数这一大块到此结束：选列、筛行、分组、子查询、联结、组合、全文本。 下一章开始改数据——INSERT，第一次往表里写东西。