[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'); ``` ### 使用外部內容表 ### 支持中文索引 > [https://github.com/wangfenjin/simple?tab=readme-ov-file](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. ```