SQLite如何实现双向索引且不重复存储文本键？

问题根源 #

你使用WITHOUT ROWID表时将text设为主键，SQLite的WITHOUT ROWID表的所有辅助索引都会把完整主键作为行定位符存储，因此你创建的text_id索引的每一条条目都会附带完整的text内容，直接导致存储体积翻倍。

方案1：最小化存储（仅存1份文本） #

这是简化后的哈希方案，不需要复杂的自定义逻辑，用SQLite内置函数即可实现，文本全程只存储一次：

-- 主表仅存储ID和文本，文本只保留1份
CREATE TABLE texts (
    text_id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL
);
-- 仅对文本的哈希值建索引，索引内不存储完整文本
CREATE INDEX idx_text_hash ON texts (hash(text));

如果你的SQLite版本低于3.35.0（无内置hash函数），可以替换为md5(text)，效果一致。

查询逻辑非常简单：

1. 文本查ID：

SELECT text_id FROM texts WHERE hash(text) = hash(?) AND text = ?;

末尾的text = ?是为了规避极低概率的哈希冲突，哈希索引已经过滤了几乎所有不匹配的行，这一步对比最多执行1次，几乎不影响查询性能。
2. ID查文本：

SELECT text FROM texts WHERE text_id = ?;

插入数据前先执行一次文本查ID的逻辑，确认无重复即可插入，无需额外处理。
该方案的索引体积仅为直接建文本索引的几十分之一，整体存储体积几乎等于所有文本的总大小。

方案2：极致查询性能，逻辑零额外处理 #

如果你不想加任何额外的查询逻辑，愿意接受两份文本存储，可以直接使用普通主键表，相比你原来的方案存储占用没有区别，但写法更简洁：

CREATE TABLE texts (
    text_id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL UNIQUE
);

双向查询都不需要额外处理：

• 文本查ID：SELECT text_id FROM texts WHERE text = ?
• ID查文本：SELECT text FROM texts WHERE text_id = ?
  该方案性能最高，不需要处理任何额外逻辑，适合对存储成本不敏感的场景。

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