代码之家 › 专栏 › 技术社区 › Axel

Sqlite使用fts5表代替主表执行所有查询

ios-sqlite android-sqlite datatable sqlite

0

Axel · 技术社区 · 3 年前

假设我有两张桌子: users 和 users_fts 哪里 用户_fts 作为搜索表。所以 用户 有两个字段 name 和 surname .

通常我会为他们两个创建索引。但既然我有 用户_fts 我应该为创建索引吗 名称 和 姓 在里面 用户 ? 使用时是否有任何警告 用户_fts 执行所有查询而不是使用主表 用户 ?

0 回复 | 直到 3 年前

1

3

Bjorg P 3 年前

SQLite提供全文搜索,我假设这就是您从表名中使用的内容。我将使用FTS5显示示例代码,但如果需要,您可以向后调整它 users 桌子上有这样的东西:

CREATE TABLE users(
    id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    surname TEXT NOT NULL
);

然后你用这样的东西制作了全文搜索表:

CREATE VIRTUAL TABLE users_fts USING fts5(
    name, 
    surname, 
    content='user', 
    content_rowid='id'
);

在这一点上,我们必须确保 用户 表为全文搜索建立索引,这可以使用上的触发器来完成 用户 表自动执行。触发器看起来像:

CREATE TRIGGER users_ai AFTER INSERT ON users
    BEGIN
        INSERT INTO users_fts (rowid, name, surname)
        VALUES (new.id, new.name, new.surname);
    END;

CREATE TRIGGER users_ad AFTER DELETE ON users
    BEGIN
        INSERT INTO users_fts (users_fts, rowid, name, surname)
        VALUES ('delete', old.id, old.name, old.surname);
    END;

CREATE TRIGGER users_au AFTER UPDATE ON users
    BEGIN
        INSERT INTO users_fts (users_fts, rowid, name, surname)
        VALUES ('delete', old.id, old.name, old.surname);
        INSERT INTO users_fts (rowid, name, surname)
        VALUES (new.id, new.name, new.surname);
    END;

有了所有这些,您现在可以使用 users_fts 表以执行全文搜索。

那么,如何在 用户 表影响 用户_fts 桌子如果您仅使用 用户_fts 表,然后对 用户 桌子无关紧要。我不知道您计划如何填充 用户_fts 表,但如果在上使用触发器 用户 表,然后是上的建议索引 用户 桌子仍然无关紧要。如果您手动保留 用户_fts 表是最新的,那么答案是 用户 桌子可以影响性能。我认识的大多数人都使用触发器方法,这就是我所做的,让您忘记手动维护全文搜索,并获得额外的好处,即在填充全文搜索时可以忽略源表上的索引。请记住,尽管在这种情况下,您不会查询 用户 表-如果您对 用户 桌子,那么你也许需要支持索引。

您还询问了使用 用户_fts 表用于查询-只要您保留 用户_fts 表是最新的,那么这种方法没有缺点。如果您需要全文搜索功能和排名,这是一种非常方便的SQLite方法。它将需要更多的存储空间,但您可以通过使用外部内容表(我在创建 用户_fts 桌子您可以在FTS5扩展文档的第4.4.2节中阅读有关它的一些详细信息,网址为 https://www.sqlite.org/fts5.html

这种方法适用于全文搜索功能,只要您维护索引,它就会很好地工作,并为您提供更多的搜索和排名功能。根据我的经验,大多数全文搜索都比使用标准SQL函数和运算符(例如 LIKE 等等)并且更强大。