[TOC] > [fts5 home](https://www.sqlite.org/fts5.html) ## 概述 SQLite 的 FTS5 (Full-Text Search 5) 是一个扩展模块，允许你在 SQLite 数据库中执行全文搜索。它支持文本数据的索引和高效检索，非常适合用来实现文档搜索、消息搜索等功能。 ## 语法 1. 三个查询的语法是等价的 ``` SLECT * FROM email WHERE email MATCH 'fts5'; SELECT * FROM email WHERE email = 'fts5'; SELECT * FROM email('fts5'); ``` 2. 可以试用 `order by`对结果进行排序 3. 可以通过`highlight` 函数对结果进行高亮 ``` highlight(email, 2, '<b>', '</b>') FROM email('fts5'); ``` 4. 使用词干搜索 (Stemming),如搜索 run 可以找到 running、ran 等形式 ``` CREATE VIRTUAL TABLE documents USING fts5(title, content, tokenize = 'porter'); ``` ### 使用外部内容表 ### 支持中文索引 > [github] (https://github.com/wangfenjin/simple?tab=readme-ov-file) 教程 ``` > ./sqlite3 SQLite version 3.32.3 2020-06-18 14:00:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .load libsimple sqlite> CREATE VIRTUAL TABLE t1 USING fts5(text, tokenize = 'simple'); sqlite> INSERT INTO t1 VALUES ('中华人民共和国国歌'); sqlite> select simple_highlight(t1, 0, '[', ']') as text from t1 where text match simple_query('中华国歌'); [中华]人民共和[国国歌] sqlite> select simple_highlight(t1, 0, '[', ']') as text from t1 where text match jieba_query('中华国歌'); [中华]人民共和国[国歌] sqlite> select simple_highlight(t1, 0, '[', ']') as text from t1 where text match simple_query('中华人民共和国'); [中华人民共和国国]歌 sqlite> select simple_highlight(t1, 0, '[', ']') as text from t1 where text match jieba_query('中华人民共和国'); [中华人民共和国]国歌 > select simple_highlight(t1, 0, '[', ']') as text from t1 where text match jieba_query('zhonghua guoge'); [中华]人民共和国[国歌] ``` ## 示例 1. 创建全文索引表 ``` CREATE VIRTUAL TABLE documents USING fts5(title, content); ``` 执行后,会同时创建 `documents`,`documents_config`,`documents_content`,`documents_data`,`documents_docsize`,`documents_idx` 2. 插入测试数据 ``` INSERT INTO documents (title, content) VALUES ('Document 1', 'This is the content of the first document.'); INSERT INTO documents (title, content) VALUES ('Document 2', 'This document talks about SQLite and FTS5.'); INSERT INTO documents (title, content) VALUES ('Document 3', 'FTS5 is a powerful tool for full-text search.'); ``` 3. 使用布尔操作符进行高级查询 ``` -- 查找包含 'SQLite' 和 'FTS5' 的文档 SELECT title, content FROM documents WHERE documents MATCH 'SQLite AND FTS5'; -- 查找包含 'SQLite' 但不包含 'FTS5' 的文档 SELECT title, content FROM documents WHERE documents MATCH 'SQLite NOT FTS5'; ``` 5. 查询片段 (Snippet), snippet 函数，允许你提取与搜索词匹配的文本片段，而不是返回整个字段内容： ``` SELECT snippet(documents, 1, '[', ']', '...', 10) AS snippet FROM documents WHERE documents MATCH 'FTS5'; // output snippet This document talks about SQLite and <b>FTS5</b>. <b>FTS5</b> is a powerful tool for full-text search. ```