SQLite全文搜索 #

-- 全文搜索（Full-Text Search, FTS）
-- 用于在大量文本中快速搜索关键词

-- SQLite 提供 FTS 扩展：
-- FTS3 - 基础版本
-- FTS4 - 增强版本
-- FTS5 - 最新版本（推荐）

FTS版本对比：
├── FTS3
│   ├── 基础全文搜索
│   └── 所有SQLite版本支持
├── FTS4
│   ├── 增强功能
│   ├── 支持短语查询
│   └── 支持前缀查询
└── FTS5
    ├── 最新版本
    ├── 更好的性能
    ├── 更多功能
    └── SQLite 3.9.0+

-- 创建FTS5虚拟表
CREATE VIRTUAL TABLE articles USING fts5(
    title,
    content,
    author
);

-- 插入数据
INSERT INTO articles (title, content, author) VALUES
    ('SQLite Tutorial', 'SQLite is a lightweight database', 'John'),
    ('Python Guide', 'Python is a programming language', 'Jane'),
    ('Database Design', 'Learn database design principles', 'Bob');

-- 全文搜索
SELECT * FROM articles WHERE articles MATCH 'SQLite';

-- 创建FTS4虚拟表
CREATE VIRTUAL TABLE articles_fts4 USING fts4(
    title,
    content,
    author
);

-- 插入数据
INSERT INTO articles_fts4 (title, content, author) VALUES
    ('SQLite Tutorial', 'SQLite is a lightweight database', 'John');

-- 搜索
SELECT * FROM articles_fts4 WHERE content MATCH 'database';

-- 创建FTS3虚拟表
CREATE VIRTUAL TABLE articles_fts3 USING fts3(
    title,
    content
);

-- 基本搜索
SELECT * FROM articles WHERE articles MATCH 'SQLite';

-- 指定列搜索
SELECT * FROM articles WHERE title MATCH 'SQLite';

-- 多个关键词（AND）
SELECT * FROM articles WHERE articles MATCH 'SQLite database';

-- 多个关键词（OR）
SELECT * FROM articles WHERE articles MATCH 'SQLite OR database';

-- 排除关键词
SELECT * FROM articles WHERE articles MATCH 'database NOT SQLite';

-- 精确短语搜索（使用引号）
SELECT * FROM articles WHERE articles MATCH '"lightweight database"';

-- FTS5短语搜索
SELECT * FROM articles WHERE articles MATCH '"database design"';

-- 前缀搜索（使用*）
SELECT * FROM articles WHERE articles MATCH 'data*';

-- 匹配：data, database, design 等

-- NEAR搜索（关键词在指定距离内）
SELECT * FROM articles WHERE articles MATCH 'SQLite NEAR database';

-- 指定距离
SELECT * FROM articles WHERE articles MATCH 'SQLite NEAR/3 database';
-- SQLite和database之间最多3个词

-- FTS5列限定语法
SELECT * FROM articles WHERE articles MATCH 'title:SQLite';

-- 多列搜索
SELECT * FROM articles WHERE articles MATCH 'title:SQLite OR content:database';

-- AND（默认）
SELECT * FROM articles WHERE articles MATCH 'SQLite AND database';

-- OR
SELECT * FROM articles WHERE articles MATCH 'SQLite OR MySQL';

-- NOT
SELECT * FROM articles WHERE articles MATCH 'database NOT MySQL';

-- 使用括号分组
SELECT * FROM articles 
WHERE articles MATCH '(SQLite OR MySQL) AND database';

-- FTS5使用bm25排名函数
SELECT 
    title,
    bm25(articles) AS rank
FROM articles
WHERE articles MATCH 'database'
ORDER BY rank;

-- 更简单的写法
SELECT * FROM articles 
WHERE articles MATCH 'database'
ORDER BY rank;

-- FTS5高亮函数
SELECT 
    highlight(articles, 0, '<b>', '</b>') AS title_highlight,
    snippet(articles, 1, '<b>', '</b>', '...', 20) AS content_snippet
FROM articles
WHERE articles MATCH 'database';

-- 默认分词器
CREATE VIRTUAL TABLE docs USING fts5(content);

-- Unicode分词器
CREATE VIRTUAL TABLE docs USING fts5(content, tokenize='unicode61');

-- Porter分词器（英文词干提取）
CREATE VIRTUAL TABLE docs USING fts5(content, tokenize='porter unicode61');

-- 中文分词（需要扩展）
-- 使用jieba等分词器

-- 外部内容表
CREATE TABLE articles_content (
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, 
    content,
    content='articles_content',
    content_rowid='id'
);

-- 自动同步触发器
CREATE TRIGGER articles_ai AFTER INSERT ON articles_content BEGIN
    INSERT INTO articles_fts(rowid, title, content) 
    VALUES (new.id, new.title, new.content);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles_content BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content) 
    VALUES ('delete', old.id, old.title, old.content);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles_content BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content) 
    VALUES ('delete', old.id, old.title, old.content);
    INSERT INTO articles_fts(rowid, title, content) 
    VALUES (new.id, new.title, new.content);
END;

-- 无内容表（只存储索引）
CREATE VIRTUAL TABLE docs USING fts5(content, content='');

-- FTS5重建索引
INSERT INTO articles(articles) VALUES('rebuild');

-- 优化
INSERT INTO articles(articles) VALUES('optimize');

-- 合并段
INSERT INTO articles(articles) VALUES('merge');

-- 删除特定行
DELETE FROM articles WHERE rowid = 1;

-- 清空表
DELETE FROM articles;

-- 创建文章表
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT,
    author TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建FTS索引
CREATE VIRTUAL TABLE posts_fts USING fts5(
    title,
    content,
    content='posts',
    content_rowid='id'
);

-- 创建同步触发器
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
    INSERT INTO posts_fts(rowid, title, content) 
    VALUES (new.id, new.title, new.content);
END;

CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, content) 
    VALUES ('delete', old.id, old.title, old.content);
END;

CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, content) 
    VALUES ('delete', old.id, old.title, old.content);
    INSERT INTO posts_fts(rowid, title, content) 
    VALUES (new.id, new.title, new.content);
END;

-- 搜索文章
SELECT 
    p.id,
    p.title,
    highlight(posts_fts, 1, '<mark>', '</mark>') AS content_highlight,
    p.author
FROM posts p
JOIN posts_fts fts ON p.id = fts.rowid
WHERE posts_fts MATCH 'database'
ORDER BY rank;

-- 产品搜索
CREATE VIRTUAL TABLE products_fts USING fts5(
    name,
    description,
    category
);

INSERT INTO products_fts VALUES
    ('iPhone 15', 'Latest Apple smartphone with A17 chip', 'Electronics'),
    ('MacBook Pro', 'Professional laptop with M3 chip', 'Electronics'),
    ('AirPods Pro', 'Wireless earbuds with noise cancellation', 'Audio');

-- 搜索产品
SELECT name, description 
FROM products_fts 
WHERE products_fts MATCH 'Apple OR chip';

-- 英文使用porter分词器
CREATE VIRTUAL TABLE docs_en USING fts5(content, tokenize='porter unicode61');

-- 中文使用合适的分词器
-- 需要安装扩展

-- 批量插入时使用事务
BEGIN;
INSERT INTO articles VALUES (...);
INSERT INTO articles VALUES (...);
...
COMMIT;

-- 定期执行优化
INSERT INTO articles(articles) VALUES('optimize');