PostgreSQL自增主键序列避坑指南

在数据库表设计中，自增主键可以说是最经典、最常用的主键策略之一。简单来说，它就是一个能自动递增的字段，专门用来给表中的每一行数据分配一个唯一的“身份证”。

当你往表里插入一条新数据时，完全不需要手动指定这个主键的值，数据库系统会自动生成一个唯一的、且比上一条记录更大的数值（比如 1, 2, 3...）。

? 为什么要使用自增主键？

1. 保证数据的唯一性

自增主键确保每条记录都有一个独一无二的数字标识，彻底避免了手动分配主键时可能出现的重复或冲突问题，数据的完整性也因此得到了保障。

2. 极高的写入与查询性能

这一点算是自增主键最大的亮点。因为 ID 是按顺序递增的，新插入的数据总是排在现有数据的最末尾。在数据库进行存储时（尤其是使用 B+ 树索引的场景），不需要频繁地移动或重排已有的数据页，磁盘碎片大大减少，数据的插入和查询效率自然就上去了。

3. 简化开发逻辑

对于开发者来说，自增主键是件很省心的事。写代码插入数据时，完全不用纠结“这个 ID 该填多少”，直接交给数据库自动生成就行，业务代码的复杂度也就随之下降了。

?️ 不同数据库是怎么实现的？

有意思的是，PostgreSQL、MySQL 和 SQL Server 在实现自增 ID 的底层原理上，差别还挺大的。简单来说，它们分别代表了 “独立对象模式”、“表属性计数器模式” 和 “元数据强绑定模式”。

下面我们来深入看看这三者的底层逻辑。

? 1.PostgreSQL：独立的序列（Sequence）对象

在 PostgreSQL 中，自增 ID 并不是直接依附于表的，它依赖于一个独立的数据库对象——序列（Sequence）。

底层实现：
当你创建一个 SERIAL 或 IDENTITY 类型的自增列时，PostgreSQL 会在后台自动创建一个名为 表名_字段名_seq 的独立序列对象。这个字段的默认值被设置为 nextval('序列名')。

核心特性：

• 独立性：序列独立于表存在，甚至可以跨表共享同一个序列。
• 事务非原子性（不回滚）：序列值的分配跟事务回滚没关系。如果一个事务获取了 ID=100 但随后回滚了，这个 100 就会被“浪费”掉，下一个事务会直接获取 101。这种设计是为了在高并发环境下，避免等待事务提交而加锁，从而极大地提升并发插入的性能。

?2. MySQL：表级别的自增计数器

在 MySQL（以主流的 InnoDB 存储引擎为例）中，自增 ID 是作为表的一个属性来维护的。

底层实现：
InnoDB 在内存中为每个带有自增主键的表维护一个自增计数器（Auto-increment Counter）。当需要插入新数据时，MySQL 会根据配置的锁模式（innodb_autoinc_lock_mode）获取锁，读取当前计数器的值作为新 ID，然后将计数器加 1。

核心特性：

• 依附于表：自增逻辑完全绑定在具体的表上，无法像 PostgreSQL 的序列那样跨表共享。
• 预分配与空洞：为了保证高并发插入的性能，MySQL 在批量插入（如 INSERT ... SELECT）时会预分配一段 ID 区间。如果批量插入中途失败或事务回滚，这些预分配的 ID 就会被直接丢弃，导致 ID 出现较大的跳跃。
• 重启恢复机制的进化：MySQL 5.7 及更早版本重启后需要扫描全表最大值来恢复计数器；而 MySQL 8.0 及更高版本将计数器的变更记录在 Redo Log 中，重启时直接从日志恢复，不再需要扫描全表。

?️ 3.SQL Server：基于元数据的 IDENTITY 属性

SQL Server 的 IDENTITY 属性在底层设计上有着自己独特的“强绑定”哲学。

底层实现：
IDENTITY 属于列定义的元数据（metadata-level attribute）。自增值由存储引擎在插入时原子生成并写入页结构，其序列状态（如 last_value 等）持久化在系统目录 sys.identity_columns 中。从 SQL Server 2012 开始，其后台实际上也使用了 sequence object（序列对象）进行封装，但它与列定义的绑定非常紧密。

核心特性：

• 物理存储耦合性：由于与表的物理存储和元数据强绑定，SQL Server 不支持通过简单的 ALTER COLUMN 语句直接为一个已有的普通列添加 IDENTITY 属性。如果需要这样做，通常必须重建表（创建新表带 IDENTITY -> 迁移数据 -> 删除旧表）。
• 事务日志不可逆性：若允许随意修改 IDENTITY，需要回滚已插入但未提交的 identity 值，而当前的日志格式不记录 identity 生成上下文，导致无法安全回退。
• 同样存在空洞：与 MySQL 和 PG 一样，由于删除记录、事务回滚或服务器重启等原因，IDENTITY 值也会出现不连续的情况。

⚔️ 4.三大数据库自增 ID 核心差异总结

总结一下：

PostgreSQL 的自增 ID 最灵活且独立；MySQL 的自增 ID 与表深度绑定，通过预分配机制换取高并发性能；而 SQL Server 的 IDENTITY 则最为严格和封闭，将其作为表物理结构的一部分，保证了极高的数据一致性，但牺牲了修改的灵活性。

最后补充一个值得注意的小特点：ID 不连续（空洞）。自增主键并不保证绝对连续。如果删除了某条数据，或者插入数据的事务发生了回滚，已经分配出去的 ID 通常不会被回收，从而产生“空洞”。它非常适合绝大多数业务场景，但在某些特殊需求下（例如需要跨数据库合并数据，或者主键本身需要有特定的业务含义时），可能就需要考虑 UUID 或其他形式的主键了。

? PostgreSQL 建表时如何创建自增主键

方法1

CREATE TABLE public.t_user_01 (
  id int4 DEFAULT nextval('users1_id_seq'::regclass) NOT NULL,
  user_name varchar(100) NOT NULL,
  -- 其他字段...
  CONSTRAINT users1_pkey PRIMARY KEY (id)
);

在这段建表语句中，nextval('users1_id_seq'::regclass) 是一个数据库函数调用，它的核心作用是为 id 字段生成一个唯一的、自增的序列值，通常用来作为表的主键。

我们可以把它拆解成三个部分来理解：

1. nextval 函数：nextval（即 "next value" 的缩写）是操作“序列（Sequence）”的核心函数。每次调用它时，它都会让指定的序列对象递增（比如从 1 变成 2），并返回这个新的数值。它能保证在多用户并发访问时，每个进程都能安全地获取到一个不重复的唯一值。
2. 'users1_id_seq' 序列名：这是 nextval 函数要操作的具体目标，即一个名为 users1_id_seq 的序列对象。序列是一个独立的数据库对象，专门用来按既定规则（如每次加1）生成数字。当在 id 字段设置这个默认值后，每次向 t_user_01 表插入一条新数据且没有手动指定 id 时，数据库就会自动从 users1_id_seq 这个序列里“拿”下一个数字填进去。
3. ::regclass 类型转换：这是 PostgreSQL 等数据库特有的语法，表示将前面的字符串 'users1_id_seq' 强制转换为 regclass 数据类型。为什么要转换？因为 nextval 函数的参数要求是 regclass 类型。通过 ::regclass 转换，数据库会在编译或准备阶段就锁定这个序列的真实身份（即“早期绑定”）。这意味着，即使以后给这个序列改了名或者移动了模式（schema），只要 OID 没变，这个默认值表达式依然能准确找到它，避免了运行时查找可能出现的错误。

补充一句：这种写法是早期 PostgreSQL 版本中定义自增主键的常见方式。在现代开发中，通常直接使用 SERIAL 或 BIGSERIAL 类型，或者使用标准的 GENERATED BY DEFAULT AS IDENTITY 语法，它们在底层其实也是自动创建了序列并绑定了类似的 nextval 逻辑。

方法2

CREATE TABLE t_user_023 (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_name varchar(100) NOT NULL
    -- 其他字段...
);

-- 或者
CREATE TABLE public.t_user_03 (
  id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
  user_name varchar(100) NOT NULL,
  -- 其他字段...
  CONSTRAINT t_user_023_pkey PRIMARY KEY (id)
);

核心字段定义：

• id INT：定义了一个名为 id 的字段，数据类型为 INT。
• GENERATED BY DEFAULT AS IDENTITY：这是整段语句的精髓，也是 PostgreSQL 10 及以上版本推荐的自增主键写法（符合 SQL 标准）。底层会自动创建一个独立的序列（Sequence）对象来管理数字的递增。
  • BY DEFAULT：这是它的行为模式，意思是“默认情况下自动生成，但也允许手动指定”。如果插入数据时不写 id，数据库会自动从序列中拿下一个数字填进去；如果在插入数据时强行指定了 id（例如 INSERT INTO t_user_023 (id, user_name) VALUES (100, '张三')），数据库也会接受，而不会报错。
• PRIMARY KEY：将 id 字段设为主键，意味着 id 的值必须是唯一的，且不能为空。数据库会自动为该字段创建一个唯一索引。

深度对比：BY DEFAULT 与 ALWAYS

在定义自增列时，除了 BY DEFAULT，还有一个常用的模式是 GENERATED ALWAYS AS IDENTITY。两者的区别决定了平时插数据时的“自由度”：

总的来说，这段建表语句创建了一个带有 “灵活自增主键” 的用户表。它既享受了数据库自动分配唯一 ID 的便利，又保留了在特殊情况下（如数据迁移）手动控制 ID 的权利。

? PostgreSQL 怎么操作序列值？

在 PostgreSQL 中，查看序列的“当前值”其实有两种不同的需求：一种是查看下一个待生成的值，另一种是查看目前表中实际已分配的最大值。

这里有 4 种常用的方法，可以根据实际场景选择：

1. 查看下一个待生成的值（最常用）
   如果想知道下一次调用 nextval() 会产生什么 ID，可以直接查询序列对象：

-- 将序列的下一个值重置为 1000
ALTER SEQUENCE t_user_03_id_seq RESTART WITH 1000;

注：last_value 加上步长（increment_by）通常就是下一个生成的值。

2. 查看当前会话最后一次获取的值
   如果在当前会话（Session）中刚刚调用过 nextval('users1_id_seq')，可以用这个函数查看：

SELECT currval('users1_id_seq');

⚠️ 注意：currval 有严格的限制。它只能查看当前会话中最近一次获取的值。如果刚连上数据库还没用过这个序列，执行这条会直接报错。

3. 查看表中实际已分配的最大 ID（排查冲突最稳妥）
   最靠谱的方式是直接去表里查最大的 ID 是多少，这能判断序列是否落后于实际数据：

SELECT MAX(id) FROM t_user_01;

如果这个值比序列的 last_value 还要大，就说明序列确实滞后了，需要执行之前提到的 setval 语句来同步。

4. 动态获取序列的最新已分配值（生产级通用写法）
   如果不确定序列的确切名字，或者想要一个更健壮、能直接反映“最新已分配值”的查询，可以使用 PostgreSQL 的内置函数组合：

-- 自动根据表和字段名找到关联的序列，并获取其最新已分配值
SELECT pg_sequence_last_value(pg_get_serial_sequence('t_user_01', 'id')::regclass);

• pg_get_serial_sequence('表名', '字段名')：能自动查出 t_user_01 表的 id 字段绑定的是哪个序列（防止序列名记错）。
• pg_sequence_last_value：基于数据库日志解析，能非常准确地返回序列的真实进度。

总结建议：

• 日常开发想看下一个 ID 是多少，用方法 1 (SELECT * FROM 序列名)。
• 排查主键冲突或做数据迁移校验时，用方法 3 (SELECT MAX(id)) 结合方法 4 最准确可靠。

5. 修改自增主键的起始值

在 PostgreSQL 中，修改自增主键的起始值，本质上就是修改它背后绑定的序列。这里有三种最常用且实用的方法：

方法一：直接指定一个固定的起始值
如果下一个插入 ID 从一个特定的数字（例如 1000）开始，可以使用 ALTER SEQUENCE 命令。首先，需要知道 id 字段绑定的序列名（通常默认为 表名_字段名_seq，即 t_user_03_id_seq）。然后执行：

-- 将序列的下一个值重置为 1000
ALTER SEQUENCE t_user_03_id_seq RESTART WITH 1000;

执行后，下一次插入数据时，id 就会从 1000 开始。

方法二：同步到当前表的最大 ID（最推荐）
如果因为手动插入了一些数据，或者删除了部分数据，想要让自增 ID 接上当前表里实际最大的 ID，避免主键冲突，这是最稳妥的方法。可以结合 setval 函数和子查询，直接把序列的值拨到当前表的最大 ID 处：

-- 将序列的当前值设置为表中最大的 id
SELECT setval('t_user_03_id_seq', (SELECT COALESCE(MAX(id), 0) FROM t_user_023));

• MAX(id) 会查出当前表里最大的 ID。
• COALESCE(..., 0) 是为了防止表是空的（MAX 结果为 NULL）导致报错，空表时默认从 0 开始，下次自增就是 1。
• setval 设置后，序列的下一个值会自动变成 最大值 + 1。

方法三：全自动获取序列名并修改（最安全）
如果不确定序列的具体名称，可以使用 PostgreSQL 自带的 pg_get_serial_sequence 函数来自动获取。这在生产环境中非常实用，能有效避免因序列名写错导致的报错。这条语句完全不需要输入序列名，直接复制粘贴表名即可使用。

-- 自动获取 t_user_03 表 id 字段绑定的序列，并将其重置为当前最大 ID
SELECT setval(
    pg_get_serial_sequence('t_user_03', 'id'),
    (SELECT COALESCE(MAX(id), 0) FROM t_user_03)
);

? PostgreSQL 自增主键的步进设置

在 PostgreSQL 中，自增主键的步进（即每次增加的数值，默认为 1）是通过底层的序列（Sequence）对象来控制的。根据是新建表还是修改现有表，有以下几种设置步进的方法：

1. 新建表时设置步进

如果正在创建一张新表，可以通过以下两种方式自定义步进：

方式一：使用标准的 IDENTITY 语法（推荐）
在定义字段时，直接在 GENERATED ... AS IDENTITY 的括号内指定 INCREMENT BY。比如下面这个例子，id 字段每次自增的步长就是 10：

CREATE TABLE orders (
    id INT GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 10) PRIMARY KEY,
    order_name VARCHAR(100)
);

方式二：手动创建序列并绑定
先创建一个自定义步进的序列，再将其绑定到表字段上：

-- 1. 创建一个步进为 5 的序列
CREATE SEQUENCE custom_step_seq INCREMENT BY 5;

-- 2. 建表并绑定该序列
CREATE TABLE products (
    id INT PRIMARY KEY DEFAULT nextval('custom_step_seq'),
    product_name VARCHAR(100)
);

2. 修改现有表的步进

如果表已经存在，需要先找到它绑定的序列，然后通过 ALTER SEQUENCE 命令来修改步进。

步骤一：查找序列名
如果不记得序列的名字，可以使用系统函数自动获取：

-- 将 '表名', '字段名' 替换为实际的名称
SELECT pg_get_serial_sequence('表名', 'id');

步骤二：修改步进
获取到序列名（例如 表名_id_seq）后，执行以下命令修改步进（例如改为每次增加 10）：

ALTER SEQUENCE 表名_id_seq INCREMENT BY 10;

注意：修改后，新的步进规则会从下一次调用 nextval（即下一次插入数据）时开始生效。

3. 进阶：设置负数步进（递减序列）
   PostgreSQL 的序列同样支持负数步进，实现主键的自动递减：

-- 创建一个从 1000 开始，每次减少 5 的序列
CREATE SEQUENCE countdown_seq
START WITH 1000
INCREMENT BY -5
MINVALUE 1; -- 建议设置最小值，防止无限递减

性能优化与注意事项

• 避免索引热点：在高并发写入的场景下，如果步进为 1，新插入的数据会集中在 B+ 树索引的同一页（页尾），容易引发“索引热点”竞争。将步进设置为较大的数值（如 10、100），可以让新生成的 ID 在索引中分布得更分散，从而减少页竞争，提升写入性能。
• 业务逻辑风险：设置非 1 的步进（尤其是大跨度步进）会导致主键 ID 出现大量“空洞”（不连续）。务必确保业务逻辑没有依赖 ID 连续性的判断（例如“偶数 ID 代表测试数据”等），以免造成业务漏洞。

? PostgreSQL 中一个非常经典的“坑”

问题现象：使用自增ID插入一条数据时，与已经存在的使用指定ID插入的数据，出现了主键冲突。

? 为什么会出现冲突？

根本原因在于：PostgreSQL 的序列（Sequence）和表里的实际数据是相互独立的。当手动插入一条指定 ID 的数据（例如 INSERT INTO t_user_01 (id, ...) VALUES (100, ...)）时，数据库只会乖乖地把这条数据存入表中，但它不会自动去更新 users1_id_seq 这个序列的当前值。序列对此一无所知，依然按自己的节奏往下走。当它走到 100 并准备生成下一个 ID 时，就会撞上刚刚手动插入的 ID 100，从而抛出“主键冲突”的错误。

?️ 如何解决？（重置序列）

解决这个问题的核心思路是：手动把序列的当前值，同步到表中最大 ID 的下一个位置。可以执行以下 SQL 语句来修复：

-- 将序列 users1_id_seq 的下一个值重置为表中当前最大 id + 1
SELECT setval('users1_id_seq',
              (SELECT COALESCE(MAX(id), 0) FROM t_user_01) + 1
       );

语句原理解析：

• (SELECT MAX(id) FROM t_user_01)：先查出表里目前最大的 ID（假设是 100）。
• +1：让序列从 101 开始，这样下次插入就不会冲突了。
• COALESCE(..., 0)：这是一个防御性写法。万一表是空的（MAX(id) 为 NULL），它会默认返回 0，加 1 后序列从 1 开始，避免报错。
• setval('序列名', 值)：把序列的当前值强行设置为这个计算出来的新值。

执行完这条语句后，再尝试正常插入数据（不指定 ID），nextval() 就会生成一个全新的、不冲突的 ID 了。

?️ 以后如何避免？

• 数据迁移/批量导入后必做：如果使用 COPY 命令或者第三方工具批量导入了带 ID 的历史数据，导入完成后务必顺手执行一次上面的 setval 语句来同步序列。
• 尽量少手动指定 ID：在正常的业务代码中，插入数据时不要给 id 字段传值，完全交给数据库自动生成。
• 建立检查机制：如果是生产环境，可以定期写个简单的脚本，检查核心表的 MAX(id) 和序列的 last_value 是否一致，及时发现并修复。

?️ 怎样让 PostgreSQL 自动更新自增 ID

PostgreSQL 的自增 ID（序列）默认情况下不会自动同步表中手动插入的数据，这正是之前遇到主键冲突的原因。不过，可以通过“自动化”的方式来避免手动执行 setval 命令：

使用触发器（Trigger）实现真正的“自动更新”
如果希望每次插入数据时，数据库都能自动检查并修正 ID，可以创建一个触发器。这个触发器会在每次插入前，自动把序列的值更新为当前表中最大 ID 的下一个值。

-- 1. 创建一个自动同步序列的函数
CREATE OR REPLACE FUNCTION sync_id_sequence()
RETURNS TRIGGER AS $$
BEGIN
    -- 将序列 users1_id_seq 的下一个值设为当前表最大ID + 1
    PERFORM setval('users1_id_seq', (SELECT COALESCE(MAX(id), 0) FROM t_user_01) + 1);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 为 t_user_01 表创建触发器，在每次插入前执行该函数
CREATE TRIGGER before_insert_sync_seq
BEFORE INSERT ON t_user_01
FOR EACH ROW
EXECUTE FUNCTION sync_id_sequence();

提示：这种方法虽然实现了全自动，但因为每次插入都要额外执行一次 MAX(id) 查询，在数据量极大的高并发场景下可能会轻微影响写入性能。