SQLite FTS表需要手动填充吗？

Question

SQLite FTS文档暗示应该使用INSERT、UPDATE、DELETE等方式填充和更新FTS表。

这就是我一直在做的——添加行、删除行等，但最近我注意到，只要我创建了FTS表，它就会自动使用来自源的数据进行填充。我是这样创建的：

CREATE VIRTUAL TABLE notes_fts USING fts4(content="notes", notindexed="id", id, title, body)

如果我向“notes”表添加一行，则它也会自动添加到notes_fts中。我猜这就是虚拟表的作用。

但是，为什么有一个关于填充FTS表的章节呢？如果我删除一行，但它仍在源表中，那么它将重新出现，所以这样做有什么意义呢？

你对此有什么想法吗？FTS是否真的需要被填充？

- laurent

---

我不知道旧版本的情况，但是使用外部内容表的 FTS5 在内容表中添加、删除或更改行时需要更新 fts 表，以便更新索引。也许有一些触发器被设置来执行这个操作，你可能忽略了它们？ - Shawn

留下注释说明出了什么问题是一个好习惯。我无法重现它 **演示**。 - Lukasz Szozda

@LukaszSzozda，实际上这个消息是相关触发器中的一个错误。不知何故，Sqlite不显示触发器的名称，这让人感到困惑。 - laurent

Answer 1

进一步阅读后，我发现FTS表确实需要手动与内容表保持同步。在运行CREATE VIRTUAL TABLE调用时，FTS表会自动填充，但之后的删除、插入和更新都必须手动完成。

在我的情况下，我使用以下触发器完成了此操作：

CREATE VIRTUAL TABLE notes_fts USING fts4(content="notes", notindexed="id", id, title, body

CREATE TRIGGER notes_fts_before_update BEFORE UPDATE ON notes BEGIN
    DELETE FROM notes_fts WHERE docid=old.rowid;
END

CREATE TRIGGER notes_fts_before_delete BEFORE DELETE ON notes BEGIN
    DELETE FROM notes_fts WHERE docid=old.rowid;
END

CREATE TRIGGER notes_after_update AFTER UPDATE ON notes BEGIN
    INSERT INTO notes_fts(docid, id, title, body) SELECT rowid, id, title, body FROM notes WHERE is_conflict = 0 AND encryption_applied = 0 AND new.rowid = notes.rowid;
END

CREATE TRIGGER notes_after_insert AFTER INSERT ON notes BEGIN
    INSERT INTO notes_fts(docid, id, title, body) SELECT rowid, id, title, body FROM notes WHERE is_conflict = 0 AND encryption_applied = 0 AND new.rowid = notes.rowid;
END;

- laurent

---

制作表格并使用触发器保持同步是一个不错的解决方案！感谢您的提示！ - Philipp Doerner

我不认为FTS表会自动填充。我不得不手动完成。 - undefined

Answer 2

根据sqlite文档，要删除条目，可以选择以下两种方式之一：

-- Insert a row with rowid=14 into the fts5 table.
INSERT INTO ft(rowid, a, b, c) VALUES(14, $a, $b, $c);

-- Remove the same row from the fts5 table.
INSERT INTO ft(ft, rowid, a, b, c) VALUES('delete', 14, $a, $b, $c);

或者

CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;

基于修改后的虚拟表进行重建

INSERT INTO ft(ft) VALUES('rebuild');

- winest