MySQL、SQL Server和Oracle主键自增设置详解

结果与目标

实现表主键的自动递增分配是数据库设计的常见需求。目标是创建能够根据插入顺序或特定规则自动产生不重复数值的标识列，从而避免应用层生成主键的复杂性与并发冲突风险。

准备条件

• MySQL：支持所有版本的 InnoDB 及 MyISAM 引擎。
• SQL Server：需确认当前数据库版本是否支持 IDENTITY 属性及 IDENTITY_INSERT 权限设置。
• Oracle：需要先具备创建序列（CREATE SEQUENCE）及创建触发器（CREATE TRIGGER）的系统权限。

第1步：MySQL 中使用 auto_increment

目的：利用 MySQL 原生特性快速定义自增主键。
入口：执行 DDL 语句创建或修改表结构。
动作：在定义主键字段时追加 AUTO_INCREMENT 关键字。MySQL 会自动维护一个内部计数器，每次插入新记录时递增该值。

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(15)
);

INSERT INTO customers (name) VALUES ('name1'), ('name2');
SELECT id FROM customers;

执行上述代码后，查询结果将显示 id 分别为 1 和 2，实现了完全自动化的递增分配。

第2步：SQL Server 中的 identity 与手动干预

目的：掌握 SQL Server 默认的自增机制及其特殊情况处理。
入口：T-SQL 脚本编辑器。
动作：使用 IDENTITY(start, increment) 定义自增列。其中第一个参数为起始值，第二个为增量步长。

CREATE TABLE customers (
    id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    name VARCHAR(15)
);

-- 注意：SQL Server 中字符串必须使用单引号
INSERT INTO customers (name) VALUES ('name1'), ('name2');
SELECT id FROM customers;

默认情况下，删除自增行会导致 ID 跳号。若业务需求要求填补这些空缺（即手动插入特定的 ID 值），需要在会话期间开启 IDENTITY_INSERT。

高风险操作提醒：手动插入自增值

在执行以下操作前，请确保理解业务后果，因为手动干预可能破坏数据连续性。

-- 1. 启用手动插入模式
SET IDENTITY_INSERT customers ON;

-- 2. 插入指定 ID 的记录
INSERT INTO customers (id, name) VALUES (1, 'name1');
INSERT INTO customers (id, name) VALUES (3, 'name1');
-- 尝试再次插入已存在的 ID 会报错
-- INSERT INTO customers (id, name) VALUES (3, 'name1'); -- 违反主键约束

-- 3. 恢复自增状态
SET IDENTITY_INSERT customers OFF;

第3步：Oracle 中的序列与触发器组合

目的：在 Oracle 中通过序列对象生成唯一标识，并利用触发器透明化插入过程。
入口：PL/SQL 块或 SQL*Plus。
动作：首先创建序列对象，然后创建 BEFORE INSERT 触发器，从序列中获取 NEXTVAL 并赋值给新行。

-- 创建序列：起始值 1，增量 2
CREATE SEQUENCE customer_id_seq INCREMENT BY 2 START WITH 1;

-- 创建表
CREATE TABLE customers (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(15)
);

-- 创建触发器：自动从序列取值填充 id
CREATE OR REPLACE TRIGGER trg_customers 
BEFORE INSERT ON customers 
FOR EACH ROW 
BEGIN 
    SELECT customer_id_seq.NEXTVAL INTO :new.id FROM dual; 
END;

此后，只需插入非主键字段，触发器便会自动调用序列：INSERT INTO customers (name) VALUES ('test');

若不使用触发器，直接在 Insert 语句中引用序列也会生效，但代码略显冗余：INSERT INTO customers VALUES (customer_id_seq.NEXTVAL, 'name1');

完成判断

• MySQL：查询表中新增行的 id 字段随插入数量连续递增且无空白。
• SQL Server：默认插入连续递增；执行 SET IDENTITY_INSERT ON 后可成功插入指定 id 值，关闭后恢复自动增长。
• Oracle：插入记录后无需指定 id，通过触发器自动获得由序列控制的增长值。

简要总结

MySQL 和 SQL Server 均通过内置属性简化了自增主键的实现，适合快速开发场景；而 Oracle 依靠序列和触发器架构，提供了更高程度的灵活性与分离性，但也增加了配置的复杂度。开发者应根据实际使用的数据库类型选择合适的配置策略。以上就是关于 MySQL、SQL Server 和 Oracle 主键自动增长设置的详细内容，更多关于数据库设计与主键管理的资料请关注本站其它相关文章！