SQLite中如何跨表镜像type_id实现高效查询且保证数据一致性

可行方案：SQLite 自动镜像冗余字段的实现方式 #

你需要的自动同步、强一致、单表查询的需求可以通过 SQLite 原生特性组合实现，无需额外业务代码维护成本：

1. 最优方案：生成列 + 联合外键约束（SQLite 3.31.0 及以上版本支持） #

可以直接把 assignments_table 里的冗余 type_id 定义为持久化存储的生成列（STORED Generated Column），自动从 values_table 映射取值，同时配合联合外键避免人为修改导致不一致：

-- 先开启外键约束，SQLite 默认关闭该配置
PRAGMA foreign_keys = ON;

-- 重新定义 assignments_table 表结构
CREATE TABLE assignments_table (
    object_id INTEGER NOT NULL,
    value_id INTEGER NOT NULL,
    -- type_id 定义为自动从 values_table 映射的持久化生成列
    type_id INTEGER GENERATED ALWAYS AS (
        (SELECT type_id FROM values_table v WHERE v.value_id = value_id)
    ) STORED,
    -- 联合外键约束，避免人为修改 type_id 导致数据不一致
    FOREIGN KEY (type_id, value_id) REFERENCES values_table (type_id, value_id),
    -- 主键可按业务实际需求调整
    PRIMARY KEY (object_id, type_id)
);

该方案优势：

• 自动同步：插入、更新 value_id 时，SQLite 会自动刷新对应 type_id 的值，永远和 values_table 里的对应值完全一致
• 满足性能要求：type_id 是持久化存储的，不是查询时临时计算的，第一个查询需求直接单表过滤即可，不需要关联其他表：

  -- 直接单表查询，可命中索引
  SELECT object_id FROM assignments_table WHERE type_id = <target_type> AND value_id = <target_value>;
  

• 第二个查询需求本身不需要关联其他表，直接查 values_table 即可：

  SELECT value_id FROM values_table WHERE type_id = <target_type>;
  

2. 低版本兼容方案：触发器实现 #

如果你使用的 SQLite 版本低于 3.31.0 不支持生成列，可以用触发器实现完全相同的效果：

-- 插入触发器：新增绑定关系时自动填充 type_id
CREATE TRIGGER assign_type_id_insert BEFORE INSERT ON assignments_table
FOR EACH ROW BEGIN
    SET NEW.type_id = (SELECT type_id FROM values_table WHERE value_id = NEW.value_id);
END;

-- 更新触发器：修改绑定的 value_id 时自动更新 type_id
CREATE TRIGGER assign_type_id_update BEFORE UPDATE OF value_id ON assignments_table
FOR EACH ROW BEGIN
    SET NEW.type_id = (SELECT type_id FROM values_table WHERE value_id = NEW.value_id);
END;

-- 源端同步触发器：values_table 的 type_id 发生修改时自动同步到所有关联绑定记录
CREATE TRIGGER sync_type_id_source AFTER UPDATE OF type_id ON values_table
FOR EACH ROW BEGIN
    UPDATE assignments_table SET type_id = NEW.type_id WHERE value_id = NEW.value_id;
END;

性能优化建议 #

给 assignments_table 的 (type_id, value_id) 加联合索引，第一个查询可以直接命中索引，性能最优。

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