SQLite INTEGER PRIMARY KEY 和普通 INTEGER 主键的区别

同一张表，两种写法，主键看起来都是整数自增，跑起来却完全是两回事。一个是 ROWID 本身，另一个只是建在 ROWID 之上的唯一索引。分不清这两者，插数据时主键冲突的报错、NULL 自动编号、查主键走不走索引，都会变得没法解释。

现象：写法只差一个词，行为差了一大截

复现用内存库就行，不用建文件：

CREATE TABLE a(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b(id INT PRIMARY KEY, name TEXT);
INSERT INTO a(name) VALUES('x');
INSERT INTO b(name) VALUES('x');
SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

输出：

1|1|x
1|1|x

到这里还看不出差别。差别在下面这句：

INSERT INTO a(id, name) VALUES(10, 'y');
INSERT INTO b(id, name) VALUES(10, 'y');
SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

输出：

1|1|x
10|10|y
1|1|x
2|10|y

a 表里 id=10 的那行，rowid 也变成了 10；b 表里 id=10 的那行，rowid 还是 2，id 只是个普通列值。a 的 id 就是 rowid，b 的 id 和 rowid 是两个东西。

再看插入 NULL：

INSERT INTO a(id, name) VALUES(NULL, 'z');
INSERT INTO b(id, name) VALUES(NULL, 'z');
SELECT rowid, id, name FROM a WHERE name='z';
SELECT rowid, id, name FROM b WHERE name='z';

输出：

11|11|z
3|3|z

两边都自动编号了，但 a 的编号直接写进了 rowid，b 的编号是唯一索引分配的，rowid 另算。这就是两种主键的本质差别。

判据：三句话判断手里的主键是哪一种

不用背文档，三条可验证的判据就够。

判据一：显式给 id 赋值后，rowid 是否跟着变。INSERT INTO t(id) VALUES(10) 之后查 SELECT rowid, id FROM t，rowid 和 id 相等，就是 ROWID 别名；rowid 还是自增序号，就是普通主键。

判据二：看索引清单。ROWID 别名不建额外索引，普通主键会多一个唯一索引：

SELECT type, name, tbl_name, sql FROM sqlite_master WHERE tbl_name IN ('a','b');

输出里 a 表只有一条 CREATE TABLE，没有为 id 建索引；b 表多了一条 CREATE UNIQUE INDEX sqlite_autoindex_b_1 ON b(id)。这个自动建的唯一索引，就是普通主键存在的证据。

判据三：看列定义原文。PRAGMA table_info(a) 和 PRAGMA table_info(b) 都会把 pk 列标成 1，光看这个分不出来。真正的分水岭在建表语句里的类型名：只有单列、类型名恰好是 INTEGER、且声明为 PRIMARY KEY 的列，才是 ROWID 别名。INT PRIMARY KEY、BIGINT PRIMARY KEY、INTEGER PRIMARY KEY DESC、复合主键里的 INTEGER 列，都不是。

顺手验证第三条里的 DESC 情况：

CREATE TABLE c(id INTEGER PRIMARY KEY DESC, name TEXT);
INSERT INTO c(id, name) VALUES(10, 'y');
SELECT rowid, id FROM c;

输出：

1|10

rowid 是 1，id 是 10。加了 DESC 就不再是 ROWID 别名，SQLite 会老老实实给它建唯一索引。

根因：SQLite 每张表本来就有一列隐藏主键

SQLite 的表分两种存储形态。普通表是 rowid 表，每行数据按一个 64 位整数 rowid 组织成 B 树，rowid 就是行的物理地址。没有显式声明 rowid 时，SQLite 按插入顺序给每行分配 rowid。

INTEGER PRIMARY KEY 的特殊之处在于，它没有新建一列，而是把声明的列直接指向 rowid。读 id 就是读 rowid，写 id 就是写 rowid，查 id 就是按 rowid 直接定位到 B 树叶子，不经过任何二级索引。

普通 INTEGER 主键走的是另一条路：rowid 照常存在，id 是另一列，数据库额外维护一棵唯一索引 B 树。按 id 查要先走索引找到 rowid，再按 rowid 回表取行。多一次跳转，多一份存储，写入时还要多维护一棵树。

还有一个容易被忽略的点：类型亲和性。INTEGER PRIMARY KEY 列带有 INTEGER 亲和性，往里面插文本 '123' 会被转成整数 123 再存：

CREATE TABLE d(id INTEGER PRIMARY KEY, v TEXT);
INSERT INTO d(id, v) VALUES('123', 't');
SELECT rowid, id, typeof(id) FROM d;

输出：

123|123|integer

插进去的文本变成了整数，rowid 也随之变成 123。普通 INT 主键列同样有 INTEGER 亲和性，这点两者一致，差别只在它是不是 rowid。

AUTOINCREMENT 也只认 ROWID 别名：

CREATE TABLE e(id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT);
CREATE TABLE f(id INT PRIMARY KEY AUTOINCREMENT, v TEXT);

第二条建表直接报错：AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY。这是个很好的反向判据，报错本身就说明 f 的主键不是 ROWID 别名。

修法：按用途选写法，别凭感觉

想让主键就是行号，查询按主键一次定位，写入少维护一棵索引，就用严格写法：单列、类型名写满 INTEGER、不要加 DESC、不要放进复合主键：

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

已经建错的表，修法分两种情况。表里还没数据，直接重建：

CREATE TABLE user_new(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO user_new(name) SELECT name FROM user;
DROP TABLE user;
ALTER TABLE user_new RENAME TO user;

表里已有数据且 id 值不能变，复制时要把 id 一起带过去，rowid 会跟着 id 走：

CREATE TABLE user_new(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO user_new(id, name) SELECT id, name FROM user;
DROP TABLE user;
ALTER TABLE user_new RENAME TO user;

反过来，如果业务主键本来就不该是行号，比如 id 是从外部系统同步来的业务编号，可能重复、可能变更，那就别用 INTEGER PRIMARY KEY，用 INT PRIMARY KEY 或者干脆不用整数主键，让 rowid 安安静静当内部行号：

CREATE TABLE order_sync(
  order_no INT PRIMARY KEY,
  amount REAL
);

还有一种情况值得单独提：主键是文本 UUID 或业务编码，表又经常按主键点查，可以考虑 WITHOUT ROWID 表，让主键本身成为聚簇键，省掉 rowid 这一层：

CREATE TABLE kv(
  k TEXT PRIMARY KEY,
  v TEXT
) WITHOUT ROWID;

WITHOUT ROWID 表没有 rowid，查 SELECT rowid FROM kv 会报错。这招只适合主键就是查询键的表，别滥用。

验证：改完之后跑一遍这组检查

修完表别凭感觉，跑一遍验证，确认主键形态符合预期：

-- 1. 索引清单：ROWID 别名不该有额外唯一索引
SELECT type, name FROM sqlite_master WHERE tbl_name='user';
-- 预期只有 table user 这一行，没有 sqlite_autoindex

-- 2. 显式赋值后 rowid 跟着变
INSERT INTO user(id, name) VALUES(100, 'check');
SELECT rowid, id, name FROM user WHERE name='check';
-- 预期输出：100|100|check

-- 3. NULL 自动编号，编号写进 rowid
INSERT INTO user(name) VALUES('auto');
SELECT rowid, id, name FROM user WHERE name='auto';
-- 预期输出：101|101|auto（rowid 取当前最大值加 1）

-- 4. 按主键点查走直接定位
EXPLAIN QUERY PLAN SELECT * FROM user WHERE id=100;
-- 预期输出：SEARCH user USING INTEGER PRIMARY KEY (rowid=?)
-- 普通主键这里会显示 SEARCH user USING INDEX sqlite_autoindex_user_1 (id=?)

第四条最能说明问题：ROWID 别名的执行计划直接写着 USING INTEGER PRIMARY KEY (rowid=?)，一次定位；普通主键要先 USING INDEX 再回表。数据量小的时候两者都快，差别只在计划文本里；数据量上去之后，多一次索引跳转和多一棵索引的维护成本，才会在写入和存储上显出来。

记住这条线就够了：INTEGER PRIMARY KEY 是 rowid 的另一个名字，INT PRIMARY KEY 是建在 rowid 之上的唯一索引。写表结构之前先想清楚主键要不要当行号，想清楚了再落笔。