SQLite FTS5 全文搜索环境搭建：给 SQLite 加上搜索引擎的能力

sqlite3 --version
python3 --version
node --version

sudo apt update && sudo apt install -y sqlite3 libsqlite3-dev python3 python3-pip nodejs npm

mkdir -p ~/fts5-demo && cd ~/fts5-demo

sqlite3 articles.db << 'EOF'
-- 普通表，存正文内容
CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- FTS5 虚拟表，只做全文检索
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    content,
    content='articles',
    content_rowid='id'
);

-- 触发器：普通表增删改时自动同步到 FTS5
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, content) VALUES (new.id, new.title, new.content);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles 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 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;
EOF

sqlite3 articles.db ".tables"
sqlite3 articles.db "SELECT name, type FROM sqlite_master WHERE type IN ('table','trigger')"

sqlite3 articles.db << 'EOF'
INSERT INTO articles (title, content) VALUES
('Python 异步编程指南', 'async 和 await 是 Python 异步编程的核心语法。配合 asyncio 库可以高效处理并发任务，避免回调地狱。'),
('Docker 容器化部署实战', 'Docker 让应用打包成镜像，在任何环境一致运行。配合 docker-compose 可以一键启动多服务架构。'),
('Kubernetes 集群管理入门', 'K8s 是当前主流的容器编排工具。理解 Pod、Service、Deployment 三个核心概念就能上手。'),
('Go 语言高性能网络编程', 'Go 的 goroutine 和 channel 让网络编程变得简单。net/http 标准库已经非常强大。'),
('Redis 缓存策略设计', '缓存穿透、缓存击穿、缓存雪崩是三个经典问题。加锁、布隆过滤器、随机过期时间分别对应不同方案。'),
('PostgreSQL 索引优化技巧', 'B-tree、GIN、GiST 三种索引适用于不同场景。全文搜索用 GIN，地理空间用 GiST。'),
('Linux 系统性能监控', 'top、htop、vmstat、iostat 是 Linux 性能分析的基础工具。看懂 CPU 和 IO 指标就能定位瓶颈。'),
('Nginx 反向代理配置', 'Nginx 做反向代理是最常见的用法。upstream、location、proxy_pass 三个指令够用大部分场景。'),
('Rust 所有权模型详解', 'Rust 的所有权系统编译期就能杜绝空指针和野指针。借用检查器是 Rust 最核心的特性。'),
('前端构建工具对比', 'Webpack、Vite、esbuild 各有优势。Vite 利用原生 ESM 实现秒级热更新，适合现代前端项目。');
EOF

sqlite3 articles.db << 'EOF'
-- 基础关键词搜索
SELECT title, snippet(articles_fts, 1, '<b>', '</b>', '…', 8) AS preview
FROM articles_fts
WHERE articles_fts MATCH '异步';

-- 多关键词 OR 搜索
SELECT title, snippet(articles_fts, 1, '<b>', '</b>', '…', 8)
FROM articles_fts
WHERE articles_fts MATCH '异步 OR 并发';

-- 短语精确匹配
SELECT title FROM articles_fts
WHERE articles_fts MATCH '"容器编排"';

-- 按相关性排序（近匹配优先）
SELECT title, score(articles_fts) AS relevance
FROM articles_fts
WHERE articles_fts MATCH 'Docker'
ORDER BY relevance DESC;
EOF

cat > search.py << 'PYEOF'
import sqlite3
import sys

conn = sqlite3.connect('articles.db')
conn.row_factory = sqlite3.Row
cur = conn.cursor()

keyword = sys.argv[1] if len(sys.argv) > 1 else "Docker"

# 基础搜索
cur.execute("""
    SELECT title, snippet(articles_fts, 1, '<b>', '</b>', '…', 8) AS preview
    FROM articles_fts
    WHERE articles_fts MATCH ?
    ORDER BY rank
""", (keyword,))

print(f"\n搜索关键词: {keyword}\n")
for row in cur.fetchall():
    print(f"  [{row['title']}]")
    print(f"  {row['preview']}\n")

# 多关键词 OR
if ' ' in keyword:
    terms = keyword.split()
    query = " OR ".join(terms)
    cur.execute("""
        SELECT title, snippet(articles_fts, 1, '<b>', '</b>', '…', 8) AS preview
        FROM articles_fts
        WHERE articles_fts MATCH ?
        ORDER BY rank
    """, (query,))
    print(f"\n多关键词搜索结果:\n")
    for row in cur.fetchall():
        print(f"  [{row['title']}]")
        print(f"  {row['preview']}\n")

conn.close()
print("Python + FTS5 搜索完成！")
PYEOF

python3 search.py Docker

mkdir -p ~/fts5-node-test && cd ~/fts5-node-test
npm init -y
npm install sqlite3

cat > search.js << 'JSEOF'
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./articles.db');

const keyword = process.argv[2] || 'Docker';

db.all(
    `SELECT title, snippet(articles_fts, 1, '<b>', '</b>', '…', 8) AS preview
     FROM articles_fts
     WHERE articles_fts MATCH ?
     ORDER BY rank`,
    [keyword],
    (err, rows) => {
        if (err) { console.error(err); return; }
        console.log(`\n搜索关键词: ${keyword}\n`);
        rows.forEach(row => {
            console.log(`  [${row.title}]`);
            console.log(`  ${row.preview}\n`);
        });
        db.close();
        console.log('Node.js + FTS5 搜索完成！');
    }
);
JSEOF

node search.js Docker

sqlite3 articles.db << 'EOF'
-- 通配符搜索：匹配以 nginx 开头的所有词
SELECT title FROM articles_fts
WHERE articles_fts MATCH 'nginx*';

-- 排除搜索：搜索包含 Redis 但不包含缓存的
SELECT title FROM articles_fts
WHERE articles_fts MATCH 'Redis -缓存';

-- 嵌套短语搜索
SELECT title FROM articles_fts
WHERE articles_fts MATCH '"异步编程" AND Python';
EOF

cd ~/fts5-demo

# 1. 检查 FTS5 扩展已加载
sqlite3 articles.db "SELECT * FROM sqlite_master WHERE name='articles_fts';"

# 2. Python 搜索测试
python3 search.py "容器"

# 3. Node.js 搜索测试
cd ~/fts5-node-test && node search.js "Python"

# 4. 数据一致性检查
cd ~/fts5-demo
sqlite3 articles.db "SELECT COUNT(*) FROM articles; SELECT COUNT(*) FROM articles_fts;"

sqlite3 articles.db "ANALYZE articles_fts;"

sqlite3 articles.db "OPTIMIZE articles_fts;"

sqlite3 articles.db << 'EOF'
INSERT INTO articles (title, content) SELECT title, content FROM old_table;
EOF