FTS中content_rowid为何必须为整数？使用非主键、文本列有何风险？

为什么FTS中的content_rowid必须为整数类型？ #

SQLite FTS全系列（FTS3/FTS4/FTS5）底层默认使用64位整数类型的rowid（别名docid）作为单篇文档的唯一标识，倒排索引的存储、检索全链路都是基于这个整数ID做关联，以此保证最高的读写性能和最低的存储开销。
当使用content参数绑定外部内容表时，content_rowid的作用是建立外部表指定列与FTS内部rowid的直接映射关系，因此必须和FTS内部rowid的类型保持一致，仅支持整数类型。 #

将content_rowid设置为非主键的整数键有什么问题？存在哪些隐藏风险？ #

你当前的示例可以正常运行只是暂时没有触发异常，核心隐藏风险有两个：

• 唯一性无保障：非主键的整数列没有默认唯一性约束，一旦出现createTime重复（比如同一时间点批量插入多条数据、系统时间回调），触发器往FTS表插入重复rowid时会直接报错，或是覆盖原有索引数据，最终导致搜索结果缺失、匹配错误。
• 同步逻辑缺失：你当前仅配置了INSERT触发器，若后续存在修改createTime、删除数据的操作，没有配套的UPDATE/DELETE触发器同步FTS表，会产生大量僵尸索引，搜索时会返回已删除的记录、或是匹配到已修改的旧内容。

你可以通过以下修改规避风险：

-- 给createTime增加唯一性约束，避免重复
CREATE TABLE tbl (a, createTime INTEGER NOT NULL UNIQUE);
CREATE VIRTUAL TABLE fts USING fts5(a, content=tbl, content_rowid=createTime);

-- 原有INSERT触发器
CREATE TRIGGER IF NOT EXISTS tbl_ai AFTER INSERT ON tbl
BEGIN
  INSERT INTO fts (rowid, a) VALUES (new.createTime, new.a);
END;

-- 补充UPDATE触发器
CREATE TRIGGER IF NOT EXISTS tbl_au AFTER UPDATE ON tbl
BEGIN
  DELETE FROM fts WHERE rowid = old.createTime;
  INSERT INTO fts (rowid, a) VALUES (new.createTime, new.a);
END;

-- 补充DELETE触发器
CREATE TRIGGER IF NOT EXISTS tbl_ad AFTER DELETE ON tbl
BEGIN
  DELETE FROM fts WHERE rowid = old.createTime;
END;

能不能将content_rowid设置为text类型的列？ #

不能，这是FTS的底层实现决定的，你遇到的dataType mismatch就是类型不匹配的明确报错，没有绕过该限制的方法。
如果业务必须使用text类型的guid作为业务主键，推荐两种适配方案：

• 给原表额外添加一个整数类型的自增主键，将该列设置为content_rowid，guid继续作为业务主键正常使用即可
• 不使用content参数绑定外部表，直接创建标准FTS表，将guid作为普通列存入FTS表，业务侧自行维护FTS表和原表的CRUD同步即可

你第二段示例的写法不符合FTS的设计要求，必然会触发类型不匹配报错，参考上述适配方案修改即可。 #

内容的提问来源于stack exchange，提问作者L.Min