解决SQLite删除记录后新插入clientId重复的问题

问题原因 #

你遇到的情况是因为SQLite中普通INTEGER主键（关联ROWID）和带AUTOINCREMENT关键字的主键行为完全不同。

你当前的clientId只是普通的INTEGER PRIMARY KEY，SQLite会自动复用已删除行的ROWID值。而你引用的文档描述的是使用AUTOINCREMENT关键字时的特殊行为——此时SQLite会严格保证新生成的ID比表中曾经存在过的最大ID大1，绝不会复用已删除的ID。

解决方法 #

要让SQLite生成永不重复的clientId，有两种可靠方案：

1. 修改表结构，添加AUTOINCREMENT关键字

由于SQLite不支持直接修改现有列添加AUTOINCREMENT，需要通过重建表的方式实现：

-- 1. 备份现有数据到临时表
CREATE TABLE orders_backup AS SELECT * FROM orders;

-- 2. 删除原表
DROP TABLE orders;

-- 3. 创建带AUTOINCREMENT约束的新表
CREATE TABLE orders
(
    clientId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    listId INTEGER,
    exchangeId NOT NULL,
    marketId NOT NULL,
    id INTEGER NOT NULL,
    accountType NOT NULL,
    clientGuid NOT NULL,
    side NOT NULL,
    type NOT NULL,
    status NOT NULL,
    price NOT NULL,
    stopPrice NOT NULL,
    amount NOT NULL,
    filled NOT NULL,
    cost NOT NULL,
    createTime NOT NULL,
    updateTime NOT NULL
);

-- 4. 恢复备份数据（原有clientId会被保留，新插入记录将自动生成递增ID）
INSERT INTO orders SELECT * FROM orders_backup;

-- 5. 清理临时表（可选）
DROP TABLE orders_backup;

添加AUTOINCREMENT后，SQLite会在sqlite_sequence系统表中记录该表的最大ID，后续插入时始终生成更大的ID，彻底避免复用已删除的ID。

2. 使用UUID作为唯一标识

如果不需要自增整数ID，可以改用UUID字符串作为clientId，每次插入时生成全新的UUID：

-- 重建表，将clientId改为TEXT类型主键
CREATE TABLE orders
(
    clientId TEXT NOT NULL PRIMARY KEY,
    listId INTEGER,
    exchangeId NOT NULL,
    marketId NOT NULL,
    id INTEGER NOT NULL,
    accountType NOT NULL,
    clientGuid NOT NULL,
    side NOT NULL,
    type NOT NULL,
    status NOT NULL,
    price NOT NULL,
    stopPrice NOT NULL,
    amount NOT NULL,
    filled NOT NULL,
    cost NOT NULL,
    createTime NOT NULL,
    updateTime NOT NULL
);

-- 插入时生成随机UUID
INSERT INTO orders (clientId, listId, exchangeId, marketId, id, accountType, clientGuid, side, type, status, price, stopPrice, amount, filled, cost, createTime, updateTime) 
VALUES (lower(hex(randomblob(16))), 19, 'myexchange', 'mymarket', 1163221156, 0, '93c39dd5-bc89-422a-b5e0-a34289ccff44', 0, 0, 1, 778856, 0, 160, 0, 0, 1741356070112000000, 1741356070112000000);

这种方式能完全避免ID复用，但UUID是字符串类型，查询性能略低于整数ID。

注意事项 #

• 带AUTOINCREMENT的主键会有轻微性能开销，且当ID达到最大值（9223372036854775807）后会抛出插入错误，而普通ROWID主键会尝试复用旧ID。
• 如果你的应用依赖自增整数ID且需要绝对唯一性，优先选择AUTOINCREMENT方案。

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