如何实现SQLite FTS5无损子串搜索？替代LIKE全表扫描方案

问题描述 #

使用SQLite FTS5虚拟表时，默认仅支持完整词搜索或前缀匹配（搜索词后加*），无法直接实现后缀或中间子串的快速搜索：

• 搜索werty无法匹配qwerty.png
• 不能在搜索词开头加*（如*werty会触发错误）
• 需求是无需全表扫描，实现类似LIKE '%wert%'的快速子串搜索效果

测试代码：

CREATE TABLE IF NOT EXISTS files (name TEXT, id INTEGER);
INSERT INTO files (name, id) VALUES ('qwerty.png', 1),  ('asdfgh.png', 2);
CREATE VIRTUAL TABLE IF NOT EXISTS names USING FTS5(name);
INSERT INTO names (name) SELECT name FROM files;

执行SELECT * FROM names WHERE name MATCH 'werty';无结果，仅前缀搜索（如qwerty、qwer*）有效。

解决方案 #

方法1：使用Trigram分词器（推荐，SQLite 3.34.0+） #

SQLite 3.34.0及以上版本内置了trigram分词器，专门用于任意子串的快速匹配，无需全表扫描。

步骤：

1. 创建FTS5表时指定tokenize='trigram'
2. 插入数据后直接用MATCH搜索子串

示例代码：

-- 创建带trigram分词器的FTS5表
CREATE VIRTUAL TABLE IF NOT EXISTS names_trigram USING FTS5(name, tokenize='trigram');
-- 导入数据
INSERT INTO names_trigram (name) SELECT name FROM files;
-- 执行子串搜索，效果等价于LIKE '%wert%'
SELECT * FROM names_trigram WHERE name MATCH 'wert';

该方法能高效匹配任意位置的子串，性能远优于LIKE '%xxx%'的全表扫描。

方法2：反向存储实现后缀搜索（适用于旧版SQLite） #

如果你的SQLite版本低于3.34.0，不支持trigram，可以通过反向存储字段来模拟后缀搜索，但无法处理中间子串的情况。

步骤：

1. 创建FTS5表时同时存储原名称和反向后的名称
2. 搜索后缀时，将搜索词反向后加*做前缀搜索

示例代码：

CREATE VIRTUAL TABLE IF NOT EXISTS names_reverse USING FTS5(original_name, reversed_name);
-- 插入数据时生成反向名称
INSERT INTO names_reverse (original_name, reversed_name) 
SELECT name, REVERSE(name) FROM files;
-- 搜索后缀匹配'werty'的条目（反向后为'yretw'，加*做前缀搜索）
SELECT original_name FROM names_reverse WHERE reversed_name MATCH REVERSE('werty') || '*';

注意事项 #

• Trigram分词器会生成更多索引条目，索引体积会比默认分词器大，但对于大多数业务场景来说在可接受范围内。
• 执行SELECT sqlite_version();可查看当前SQLite版本，低于3.34.0建议升级以使用trigram功能。

内容的提问来源于stack exchange，提问作者KeyKi