sqlite数据库FTS扩展概述：

FTS扩展版本：
├── FTS1：早期版本，已废弃
├── FTS2：改进版本，已废弃
├── FTS3：稳定版本，广泛使用
└── FTS5：最新版本，功能最强

FTS核心特性：
├── 全文索引：自动建立倒排索引
├── 快速搜索：毫秒级响应
├── 模糊匹配：支持模糊查询
├── 相关性排序：按匹配度排序
└── 分词支持：支持多种分词器

FTS应用场景：
├── 文档管理系统
├── 内容管理系统
├── 商品搜索
├── 日志分析
├── 知识库搜索
└── 消息检索

FTS工作原理：
┌─────────────────────────────────────────┐
│ 文档内容 → 分词 → 倒排索引 → 快速检索 │
└─────────────────────────────────────────┘

倒排索引结构：
├── 词项（Term）：分词后的单词
├── 文档列表：包含该词的文档ID
├── 位置信息：词在文档中的位置
└── 频率统计：词在文档中的出现次数

FTS3与FTS5对比：

┌─────────────────┬────────────────┬────────────────┐
│ 特性 │ FTS3 │ FTS5 │
├─────────────────┼────────────────┼────────────────┤
│ 发布版本 │ 3.5.0+ │ 3.9.0+ │
│ 性能 │ 较快 │ 更快 │
│ 内存使用 │ 较高 │ 较低 │
│ 分词器 │ 内置+自定义 │ 内置+自定义 │
│ 相关性排序 │ matchinfo() │ bm25() │
│ 布尔查询 │ 支持 │ 增强 │
│ NEAR查询 │ 支持 │ 增强 │
│ 短语查询 │ 支持 │ 支持 │
│ 列过滤器 │ 支持 │ 增强 │
│ Unicode支持 │ 基础 │ 增强 │
│ 外部内容表 │ 不支持 │ 支持 │
│ 无内容表 │ 不支持 │ 支持 │
│ 删除优化 │ 手动 │ 自动 │
└─────────────────┴────────────────┴────────────────┘

FTS5新增特性：
├── bm25()排序函数
├── 外部内容表
├── 无内容表
├── 更好的Unicode支持
├── 更高效的存储
└── 更灵活的查询语法

sqlite数据库全文检索语法：

1. 基本查询
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite’;
— 搜索包含”sqlite”的文档

2. 多词查询
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite database’;
— 搜索包含”sqlite”或”database”的文档

3. 短语查询
SELECT * FROM fgedu_docs WHERE docs MATCH ‘”sqlite database”‘;
— 搜索包含”sqlite database”短语的文档

4. 布尔查询
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite AND database’;
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite OR mysql’;
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite NOT mysql’;

5. 前缀查询
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sql*’;
— 搜索以”sql”开头的词

6. NEAR查询
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite NEAR database’;
— 搜索”sqlite”和”database”相邻的文档

7. 列过滤
SELECT * FROM fgedu_docs WHERE docs MATCH ‘title:sqlite’;
— 只在title列搜索

8. 相关性排序
— FTS3
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite’
ORDER BY matchinfo(docs) DESC;

— FTS5
SELECT * FROM fgedu_docs WHERE docs MATCH ‘sqlite’
ORDER BY bm25(docs) DESC;

sqlite数据库FTS应用规划：

1. 版本选择
├── 新项目：优先选择FTS5
├── 兼容需求：选择FTS3
└── 性能优先：选择FTS5

2. 分词器选择
├── 简单分词器：英文文本
├── Porter分词器：英文词干提取
├── Unicode分词器：多语言支持
└── 自定义分词器：中文分词

3. 表结构设计
├── 主表存储原始数据
├── FTS表存储索引
├── 定期同步
└── 考虑存储开销

4. 查询优化
├── 使用相关性排序
├── 限制返回结果
├── 使用列过滤
└── 缓存热门查询

存储开销估算：
├── FTS索引约为原始文本的1.5-2倍
├── 需要额外的磁盘空间
├── 写入性能下降约20-30%
└── 查询性能提升显著

FTS5表创建示例：
— 创建文档表
CREATE TABLE fgedu_documents (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT,
author TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

— 创建FTS5虚拟表
CREATE VIRTUAL TABLE fgedu_docs_fts USING fts5(
title,
content,
author,
content=’fgedu_documents’,
content_rowid=’id’,
tokenize=’unicode61′
);

— 创建触发器自动同步
CREATE TRIGGER fgedu_docs_ai AFTER INSERT ON fgedu_documents BEGIN
INSERT INTO fgedu_docs_fts(rowid, title, content, author)
VALUES (new.id, new.title, new.content, new.author);
END;

CREATE TRIGGER fgedu_docs_ad AFTER DELETE ON fgedu_documents BEGIN
INSERT INTO fgedu_docs_fts(fgedu_docs_fts, rowid, title, content, author)
VALUES (‘delete’, old.id, old.title, old.content, old.author);
END;

CREATE TRIGGER fgedu_docs_au AFTER UPDATE ON fgedu_documents BEGIN
INSERT INTO fgedu_docs_fts(fgedu_docs_fts, rowid, title, content, author)
VALUES (‘delete’, old.id, old.title, old.content, old.author);
INSERT INTO fgedu_docs_fts(rowid, title, content, author)
VALUES (new.id, new.title, new.content, new.author);
END;

— 插入测试数据
INSERT INTO fgedu_documents (title, content, author) VALUES
(‘sqlite数据库入门教程’, ‘sqlite是一个轻量级的嵌入式数据库，无需服务器配置…’, ‘风哥’),
(‘sqlite性能优化指南’, ‘本文介绍sqlite数据库的性能优化技巧，包括索引优化…’, ‘风哥’),
(‘sqlite全文检索实战’, ‘FTS5是sqlite的全文检索扩展，支持高效的文本搜索…’, ‘风哥’);

执行结果：
sqlite> SELECT * FROM fgedu_documents;
1|sqlite数据库入门教程|sqlite是一个轻量级的嵌入式数据库…|风哥|2024-04-08 12:00:00
2|sqlite性能优化指南|本文介绍sqlite数据库的性能优化技巧…|风哥|2024-04-08 12:00:01
3|sqlite全文检索实战|FTS5是sqlite的全文检索扩展…|风哥|2024-04-08 12:00:02

全文检索示例：
— 基本搜索
sqlite> SELECT d.id, d.title, bm25(fgedu_docs_fts) as score
…> FROM fgedu_documents d, fgedu_docs_fts f
…> WHERE fgedu_docs_fts MATCH ‘sqlite’
…> AND d.id = f.rowid
…> ORDER BY score;
1|sqlite数据库入门教程|-2.234
2|sqlite性能优化指南|-2.567
3|sqlite全文检索实战|-2.891

— 多词搜索
sqlite> SELECT d.id, d.title
…> FROM fgedu_documents d, fgedu_docs_fts f
…> WHERE fgedu_docs_fts MATCH ‘sqlite 优化’
…> AND d.id = f.rowid;
1|sqlite数据库入门教程
2|sqlite性能优化指南
3|sqlite全文检索实战

— 短语搜索
sqlite> SELECT d.id, d.title
…> FROM fgedu_documents d, fgedu_docs_fts f
…> WHERE fgedu_docs_fts MATCH ‘”性能优化”‘
…> AND d.id = f.rowid;
2|sqlite性能优化指南

— 布尔搜索
sqlite> SELECT d.id, d.title
…> FROM fgedu_documents d, fgedu_docs_fts f
…> WHERE fgedu_docs_fts MATCH ‘sqlite AND 优化’
…> AND d.id = f.rowid;
2|sqlite性能优化指南

— 列过滤搜索
sqlite> SELECT d.id, d.title
…> FROM fgedu_documents d, fgedu_docs_fts f
…> WHERE fgedu_docs_fts MATCH ‘title:sqlite’
…> AND d.id = f.rowid;
1|sqlite数据库入门教程
2|sqlite性能优化指南
3|sqlite全文检索实战

— 高亮显示
sqlite> SELECT highlight(fgedu_docs_fts, 1, ‘[‘, ‘]’) as content
…> FROM fgedu_docs_fts
…> WHERE fgedu_docs_fts MATCH ‘sqlite’;
[sqlite]是一个轻量级的嵌入式数据库…
本文介绍[sqlite]数据库的性能优化技巧…
FTS5是[sqlite]的全文检索扩展…

文档搜索系统实现：
/*
* doc_search.c
* from:www.itpux.com.qq113257174.wx:itpux-com
* web: http://www.fgedu.net.cn
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

int search_documents(sqlite3 *db, const char *keyword) {
sqlite3_stmt *stmt;
char sql[512];

snprintf(sql, sizeof(sql),
“SELECT d.id, d.title, d.author, ”
“highlight(fgedu_docs_fts, 1, ‘‘, ‘‘) as content, ”
“bm25(fgedu_docs_fts) as score ”
“FROM fgedu_documents d, fgedu_docs_fts f ”
“WHERE fgedu_docs_fts MATCH ? AND d.id = f.rowid ”
“ORDER BY score LIMIT 20”);

int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
printf(“Error: %s\n”, sqlite3_errmsg(db));
return rc;
}

sqlite3_bind_text(stmt, 1, keyword, -1, SQLITE_STATIC);

printf(“\n=== Search Results for ‘%s’ ===\n”, keyword);
printf(“%-5s %-30s %-15s %s\n”, “ID”, “Title”, “Author”, “Score”);
printf(“——————————————————–\n”);

int count = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
int id = sqlite3_column_int(stmt, 0);
const char *title = (const char*)sqlite3_column_text(stmt, 1);
const char *author = (const char*)sqlite3_column_text(stmt, 2);
double score = sqlite3_column_double(stmt, 4);

printf(“%-5d %-30s %-15s %.3f\n”, id, title, author, score);
count++;
}

printf(“——————————————————–\n”);
printf(“Found %d documents\n”, count);

sqlite3_finalize(stmt);
return SQLITE_OK;
}

int main() {
sqlite3 *db;
sqlite3_open(“/sqlite/fgdata/fgedudb.db”, &db);

search_documents(db, “sqlite”);
search_documents(db, “优化”);
search_documents(db, “\”性能优化\””);

sqlite3_close(db);
return 0;
}

执行结果：
$ ./doc_search

=== Search Results for ‘sqlite’ ===
ID Title Author Score
——————————————————–
1 sqlite数据库入门教程 风哥 -2.234
2 sqlite性能优化指南 风哥 -2.567
3 sqlite全文检索实战 风哥 -2.891
——————————————————–
Found 3 documents

sqlite数据库FTS最佳实践：

1. 版本选择
├── 新项目使用FTS5
├── 需要bm25排序选FTS5
└── 兼容性要求选FTS3

2. 分词器配置
├── 英文：porter分词器
├── 中文：自定义分词器或unicode61
└── 多语言：unicode61

3. 性能优化
├── 使用外部内容表
├── 定期优化索引
├── 限制返回结果
└── 缓存热门查询

4. 维护管理
├── 定期rebuild索引
├── 监控索引大小
├── 清理无效数据
└── 备份FTS表