数据库原理与选型 / 系统课时

主键、ROWID 与自增机制

主键、ROWID 与自增机制

SQLite 中的主键并不都等价:INTEGER PRIMARY KEY 会直接成为 ROWID 的别名,普通主键通常需要独立索引,而 AUTOINCREMENT 还会改变编号复用规则。掌握这些差异,才能正确选择主键并避免不必要的写入成本。

学习目标

  • 解释普通 ROWID 表中 ROWID 与 INTEGER PRIMARY KEY 的关系
  • 区分普通主键和 INTEGER PRIMARY KEY 的存储与编号行为
  • 判断什么时候需要 AUTOINCREMENT,什么时候应避免使用

ROWID 是普通表自带的整数键

除 WITHOUT ROWID 表外,SQLite 的普通表都有一个 64 位有符号整数 ROWID。如果表没有声明同名列,还可以通过 rowid、_rowid_ 或 oid 访问这个值。

当某一列声明为 INTEGER PRIMARY KEY 时,这一列会成为 ROWID 的别名,两者读取和修改的是同一个整数标识。因此这类主键不需要再为主键维护普通的独立索引,通常也是 SQLite 中最直接的整数主键方案。

普通的 TEXT PRIMARY KEY、复合 PRIMARY KEY,以及使用 INT PRIMARY KEY 等其他类型名称声明的主键,都不会因此成为 ROWID 的别名。普通 ROWID 表内部仍然存在独立的 ROWID。

默认编号为什么可能复用

向 INTEGER PRIMARY KEY 列插入 NULL 或省略该列时,SQLite 会自动选择一个可用整数。正常情况下,如果尚未达到整数上限,它通常使用当前最大 ROWID 加一。

如果当前最大值对应的行被删除,之后的自动编号可能再次使用这个数字。因此默认机制保证的是当前表中不冲突,而不是某个数字历史上永远不会再次出现。外部系统如果要求标识永久不复用,就不能把普通自动 ROWID 的行为当成这种业务保证。

AUTOINCREMENT 改变的是复用规则

AUTOINCREMENT 只能用于 INTEGER PRIMARY KEY。启用后,SQLite 会利用内部的 sqlite_sequence 表记录该表曾经使用过的最大自动编号,使后续自动生成的 ROWID 高于此前已经成功生成过的最大值。

这个保证会增加额外的 CPU、磁盘访问和写入开销,而且多数场景并不需要。业务如果只要求每一行当前唯一,普通 INTEGER PRIMARY KEY 已经足够;只有确实要求自动编号不复用历史值时,才应考虑 AUTOINCREMENT。

用同一组操作对比两种编号

下面的示例创建普通整数主键表和 AUTOINCREMENT 表,各插入三行,再删除当前最大编号并插入新行。

sql
DROP TABLE IF EXISTS normal_id;
DROP TABLE IF EXISTS auto_id;

CREATE TABLE normal_id (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE auto_id (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

INSERT INTO normal_id(name) VALUES ('a'), ('b'), ('c');
INSERT INTO auto_id(name) VALUES ('a'), ('b'), ('c');

DELETE FROM normal_id WHERE id = 3;
DELETE FROM auto_id WHERE id = 3;

INSERT INTO normal_id(name) VALUES ('d');
INSERT INTO auto_id(name) VALUES ('d');

SELECT 'normal' AS kind, id, rowid, name
FROM normal_id
WHERE name = 'd'
UNION ALL
SELECT 'autoincrement', id, rowid, name
FROM auto_id
WHERE name = 'd';

预期结果

text
normal|3|3|d
autoincrement|4|4|d

普通表删除当前最大编号 3 后,新行再次得到 3;AUTOINCREMENT 表则继续到 4。同时两张表中的 id 与 rowid 都相等,验证了 INTEGER PRIMARY KEY 是 ROWID 的别名。

普通主键与 WITHOUT ROWID 的边界

如果主键天然是文本或多列组合,例如 (tenant_id, order_no),可以直接声明普通 PRIMARY KEY。这类主键在普通 ROWID 表中不是 ROWID 的别名,SQLite 既要保存行的 ROWID,又要维护用于保证主键唯一性的索引结构。

对于以复合主键为核心、无需隐藏整数行号的表,还可以评估 WITHOUT ROWID。这种表没有 ROWID,主键直接参与底层 B-tree 的组织方式,因此它与普通 ROWID 表的存储模型不同。

选择主键时,应从业务标识和访问方式出发:需要简单高效的内部整数键时使用 INTEGER PRIMARY KEY;只有确实要求自动编号历史上不复用时才增加 AUTOINCREMENT;业务天然由文本或复合字段唯一确定时,则使用对应的普通主键,并在合适时评估 WITHOUT ROWID。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

主键、ROWID 与自增机制

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通