SQLite如何实现跨多表共享序列生成全局唯一整数ID

共享序列实现逻辑 #

SQLite本身没有内置独立的SEQUENCE对象，但可以通过自研序列表的方式实现跨多表共享的自增序号生成能力，完全可以产出4字节长度的整数ID，存储空间远小于GUID，性能也更优。

具体实现步骤 #

• 第一步：创建全局序列管理表

CREATE TABLE global_sequence (
    name TEXT PRIMARY KEY NOT NULL,
    current_value INTEGER NOT NULL DEFAULT 1,
    step INTEGER NOT NULL DEFAULT 1
);
-- 初始化跨表共享的ID序列
INSERT INTO global_sequence (name) VALUES ('shared_table_unique_id');

• 第二步：原子获取下一个唯一ID
  SQLite 3.35.0及以上版本可直接使用带RETURNING的UPDATE语句，操作本身是原子性的，多并发场景也不会生成重复ID：

UPDATE global_sequence 
SET current_value = current_value + step 
WHERE name = 'shared_table_unique_id' 
RETURNING current_value;

低版本SQLite可以用事务包裹实现原子性：

BEGIN TRANSACTION;
SELECT current_value FROM global_sequence WHERE name = 'shared_table_unique_id' FOR UPDATE;
UPDATE global_sequence SET current_value = current_value + step WHERE name = 'shared_table_unique_id';
COMMIT;

• 第三步：自动适配多表插入
  可以给所有需要用到全局唯一ID的表加前置触发器，插入数据时自动分配ID，无需手动处理：

-- 给表A加触发器，表B、表C可复用同款逻辑
CREATE TRIGGER auto_assign_id_A BEFORE INSERT ON A
FOR EACH ROW BEGIN
  UPDATE global_sequence SET current_value = current_value + step WHERE name = 'shared_table_unique_id';
  SET NEW.uniqueId = (SELECT current_value FROM global_sequence WHERE name = 'shared_table_unique_id');
END;

4字节整数适配说明 #

• 4字节有符号整数的最大值为2147483647，若预估全局总数据量不超过该阈值，可直接使用，SQLite存储该范围的整数只会占用4字节空间
• 若数据量可能超出，可使用8字节整数，SQLite的INTEGER类型是动态存储的，小值仍会占用更小的存储空间，整体仍远小于32字节的GUID
• 有序自增ID相比乱序的GUID，索引插入效率更高，碎片更少

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