刚接手一个从 MySQL 迁到 Oracle 的老项目第一件事就把我整懵了建表语句里写的AUTO_INCREMENTOracle 直接报语法错误。Oracle 数据库就是这么个性人家 MySQL、SQL Server 天生自带主键自增它偏不非得让你自己想办法。折腾了一下午翻了无数帖子踩了一堆坑总算把几种方案都摸透了。这篇文章就把我实际验证过的几种 Oracle 主键自增实现方式连同触发器的坑、序列跳号的问题、12c 新特性的坑一次性说清楚。1. 问题根源Oracle 为什么不自带主键自增先说点背景帮刚入门的同学理解为什么会有这个问题。Oracle 数据库从诞生那天起设计哲学就和 MySQL 不太一样。MySQL 是互联网时代的产品讲究简单、快、省心AUTO_INCREMENT一条命令搞定自增主键。Oracle 走的是企业级、高可靠、强一致的路线它的设计者认为主键生成策略是业务逻辑的一部分不应该由数据库隐式处理而是应该显式地交给开发人员管理。所以 Oracle 提供了SEQUENCE序列这个独立于表的数据库对象。序列的本质是一个计数器你可以让 N 个会话同时取号它保证取出来的号不重复、不冲突。但序列本身不关心你要把它用来做主键还是订单号还是别的什么编号它就是个发号器。拿到序列的值之后你怎么填到表里Oracle 不管这就给了开发者灵活度也让新手犯了难——到底该咋用在深入方案之前先把两个核心概念捋清楚。第一个是SEQUENCE序列它负责生成一组不重复的数字序列可以被多个表共享也可以给单个表专用。第二个是TRIGGER触发器它是 Oracle 里的一种特殊存储过程在表的 INSERT 或 UPDATE 操作前自动执行可以用来给主键字段赋值。明白了这两个东西后面所有方案都是围绕它们组合出来的。2. 方案一序列加触发器最稳妥的传统玩法这套方案是 Oracle 江湖上流传最广的写法在 11g 及更早版本里几乎是唯一的标准答案。它的核心逻辑就两步先建一个序列用来发号再建一个BEFORE INSERT触发器在每次插入数据前自动从序列里取一个新号填进主键字段。2.1 序列创建与参数解读创建序列的语法是核心每个参数我都实测过给你逐一说明CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1 MAXVALUE 99999999 NOCACHE NOCYCLE;START WITH 1从 1 开始发号这是最常见的设置你完全可以从 1000 开始给手工插入的数据留点空间。INCREMENT BY 1每次发号递增 1如果需要步进为 2 或者更大自己调整。MAXVALUE序列的最大值到了这个值之后的行为由CYCLE/NOCYCLE决定。生产环境建议设一个足够大的上限比如 9999999999。NOCACHE不缓存序列值每次取号都写数据字典性能差点但保证不丢号。如果你用CACHE 20数据库崩溃时缓存里还没使用的号直接就跳过去了下次重启序列会从缓存后的值继续。关于这个坑后面第 6 部分我会详细讲。NOCYCLE用完不循环。如果设成CYCLE到了MAXVALUE就从头开始有可能出现主键重复除非你有特殊的业务需求主键场景下强烈建议NOCYCLE。在实际项目里我还是建议你在生产环境用CACHE。原因很简单序列发号是高频操作每次取号都要更新数据字典的行如果是NOCACHE在高并发插入场景下这个更新会变成一个严重的串行瓶颈。我测过CACHE 100的场景批量插入 10 万条数据耗时比NOCACHE少了差不多三分之一。2.2 触发器自动赋值序列建好了下一步是建触发器。这里默认你已经有一张emp表主键字段叫emp_idCREATE OR REPLACE TRIGGER trg_emp_auto_id BEFORE INSERT ON emp FOR EACH ROW BEGIN IF :NEW.emp_id IS NULL THEN SELECT seq_emp_id.NEXTVAL INTO :NEW.emp_id FROM DUAL; END IF; END; /这段代码里有几个地方新手特别容易写错我一个个说。BEFORE INSERT表示插入前触发FOR EACH ROW表示每一行插入都触发一次这是行级触发器不加这句的话整个语句只触发一次主键全都变成同一个值肯定不是你想要的。IF :NEW.emp_id IS NULL这个判断非常关键——只有当你没传主键值的时候才自动生成。有些人图省事不写这个判断结果手动指定主键插入时触发器把手工值覆盖了数据全乱。SELECT seq_emp_id.NEXTVAL INTO :NEW.emp_id FROM DUAL是从序列取下一个值赋给新行的主键字段。这里为什么要有FROM DUAL因为 Oracle 不允许没有FROM子句的SELECTDUAL是一张虚拟表专门干这事的。SELECT ... INTO :NEW.xxx的语法是给变量赋值的固定写法不同于普通查询这里是在 PL/SQL 块内部必须用INTO才能把序列值塞进触发器变量。全部建完后插入一条数据测试一下INSERT INTO emp (emp_name, salary) VALUES (张三, 8000); SELECT * FROM emp;不需要在 INSERT 里写emp_id触发器会帮你自动填充一个唯一值。这套方案的优点是比较通用不管哪个版本不管表结构多复杂都能用。缺点是代码量大每张需要自增主键的表都要配套建序列和触发器表多的时候有点烦。2.3 触发器与序列的命名规范这里顺便聊聊命名。我在实际项目里见过有人建了几十个序列和触发器命名乱七八糟后来维护的时候根本分不清哪个序列对应哪张表。建议采用seq_表名_字段名和trg_表名_字段名的命名方式。这不是什么官方强制的标准但后来的同事接手会感谢你。Oracle 的数据库字典视图中查序列和触发器也是按名字过滤的规范命名查起来一目了然。3. 方案二11g 起用默认值调用序列少写一个触发器如果你用的是 11g 及以上版本可以稍微省点事Oracle 在创建表的时候允许直接把序列的NEXTVAL设为字段默认值。这样连触发器都不用建了表定义自带自增逻辑。3.1 表级默认值实现自增具体做法是建表时把某列默认值指定为序列的下一个值CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE TABLE emp ( emp_id NUMBER DEFAULT seq_emp_id.NEXTVAL PRIMARY KEY, emp_name VARCHAR2(50), salary NUMBER(10,2) );插入数据时只要 INSERT 语句不写emp_id字段Oracle 就会自动用默认值也就是序列的下一个值填充。这和 MySQL 的AUTO_INCREMENT从使用体验上看几乎一样了。要注意的一个限制是字段的默认值不能引用CURRVAL只能引用NEXTVAL因为CURRVAL对于会话来说是会话级状态不是纯粹的表达式值。另外如果 INSERT 语句显式给emp_id传了值默认值就不会生效这和触发器的行为一致。3.2 默认值方案的触发器替代场景这个方案最大的优势是少写一个触发器建表语句也更清晰。但有了序列还是要手动维护序列。有些场景下默认值方案比触发器方案更灵活比如你有几张表要共享同一个序列生成全局唯一编号每张表都能直接用DEFAULT seq_xxx.NEXTVAL不需要为每张表单独建触发器。我实际项目中遇到过这样一个需求三个子系统共用一套流水号要求生成的 ID 全局唯一区分不出是哪张表产生的。如果用独立触发器得建三个每个里面逻辑一样只是表不同用默认值方案一个序列三张表共享表里什么都不用配清爽得很。不过提醒一句跨表共享序列意味着编号有空洞业务上如果要求每张表内部编号连续就不适合共享序列。3.3 习惯了 Oracle 的写法之后说实话我刚从 MySQL 转过来的时候特别看不惯这套建序列再设默认值的操作。但用久了反而觉得序列这种显式的发号机制在某些场景下比 MySQL 的AUTO_INCREMENT要灵活得多。比如你想让订单号和用户ID错开区间或者多个表用一个统一编号池MySQL 做起来很别扭Oracle 用一个序列就能搞定。这是设计理念的差异谈不上谁绝对好谁绝对差。4. 方案三Oracle 12c 的 IDENTITY 列终于有了正统自增如果你用的是 12c 或更高版本Oracle 总算是开了窍提供了GENERATED AS IDENTITY语法这玩意儿和 MySQL 的AUTO_INCREMENT就是同一个亲儿子了。它的底层实现其实还是序列但 Oracle 帮你把序列自动创建、自动绑定、默认值设置这些细节全部隐藏了你看不到序列的名字也不需要关心触发器。4.1 标准写法与变体创建语法非常简洁CREATE TABLE emp ( emp_id NUMBER GENERATED AS IDENTITY PRIMARY KEY, emp_name VARCHAR2(50), salary NUMBER(10,2) );建完表直接插入数据啥都不用管Oracle 全自动就像回到了 MySQL 的怀抱。这里还有个加分项Oracle 会自动配套创建一个普通序列绑定到表的 IDENTITY 列上。如果你想控制自增初始值、步长可以用变体语法CREATE TABLE emp ( emp_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100 INCREMENT BY 2 PRIMARY KEY, emp_name VARCHAR2(50) );BY DEFAULT ON NULL的意思是当插入的值为 NULL 时自动取序列如果显式给了非 NULL 值就用你给的值。这里有个容易搞混的地方GENERATED BY DEFAULT AS IDENTITY和GENERATED BY DEFAULT ON NULL AS IDENTITY区别在前者如果你显式插入 NULL 会报错你必须提供非 NULL 值或完全不写这个字段后者允许你显式插入 NULLOracle 自动改为序列值。实际用下来显式INSERT ... VALUES (NULL, ...)的场景比较少但两条语句的语义差别面试时经常被问到。4.2 自主定制的限制和注意点用 IDENTITY 列有两个坑必须记牢。第一你不能在一个已存在的表上通过ALTER TABLE ... ADD直接加 IDENTITY 列12c 支持的只是建表时定义 IDENTITY 列。如果你已经有张老表想改成自增只能重新建表数据迁移或者老老实实回到序列加触发器方案。第二IDENTITY 列底层自动创建的序列是默认NOORDER的在高并发下插入的顺序和自增值的递增顺序可能不完全一致如果业务上要求自增值必须严格反映插入先后就需要额外考虑。另外要表扬的是 Oracle 允许你查询 IDENTITY 列对应的序列信息在ALL_TAB_IDENTITY_COLS视图里能看到表与序列的关联。有时候需要重置序列的值或修改步长却找不到自动生成的序列名用这个视图一查就出来了。4.3 什么时候选 IDENTITY我的建议是新项目、新表、版本在 12c 以上直接无脑用 IDENTITY。它最接近你熟悉的 MySQL 习惯代码最干净不需要单独维护序列对象。但如果是老系统表已经建好了数据量巨大又不想停服务那还是用序列加触发器或者默认值方案更现实。方案选型从来不是比哪个最新而是看哪个能平稳落地。5. 三种方案横向对比与选型决策把三种方案放一起对比很多人会纠结选哪个。我整理了一个表你根据自己项目的实际情况套用就行。对比维度序列加触发器默认值调用序列IDENTITY 列适用版本全版本通用11g 及以上12c 及以上需要维护序列是是否自动创建需要写触发器是否否是否允许手动指定主键插入允许触发器判断空才自动生成允许显式传值覆盖默认BY DEFAULT允许AS IDENTITY不允许多表共享同一编号池可以一个序列多个触发器可以一个序列多个默认值不可以每表独立配置复杂度高表和对象耦合中低代码可读性一般逻辑分散在触发器中较好最好从可维护性角度我个人的推荐顺序是新表选 IDENTITY老表迁移可以选默认值只有碰到老版本数据库10g 及以下才不得不回到触发器方案。这里多提醒一句触发器的执行对性能有损耗虽然损耗通常可以忽略但在每秒上千次的插入场景下实测还是比默认值方式多出 5% 到 10% 的延迟。高并发系统能用默认值或 IDENTITY就别用触发器。6. 实操中踩过的坑与排查技巧写这篇博客之前我把三种方案全部在本地建了测试环境跑了一遍中间遇到的问题不少有些问题光看文档根本想不到给你一一列出来。6.1 序列跳号问题最经典的一个坑是序列跳号。我测试CACHE 20时插入 100 条数据一切正常。然后我干脆地重启了一下数据库再插入时发现 ID 直接从 31 而不是 21 继续了20 到 30 之间的号完全消失。原因就是CACHEOracle 从数据字典里取序列号是批量取的取一次申请一批缓存在内存里慢慢发。数据库正常关闭时缓存会写回但实例崩溃或强制重启时没有来得及使用的缓存序列值就永久丢失表现出来就是跳号。解决跳号没有完美的方案只能根据业务取舍。严格连续号要求如银行流水就别用缓存NOCACHE虽然慢一点但逻辑上不丢号。互联网业务只是用来自增主键跳号完全无感用CACHE 1000性能更好。有一点需要明确就算用NOCACHE并发事务回滚也会导致个别序列号被消耗但未真正使用序列有间隙是一个正常现象不要试图让主键自增做到绝对无洞那是和数据库设计较劲。6.2 触发器方案的两个大坑触发器方案我遇到的第一个坑是吐槽了很久的:NEW赋值。很多人写触发器直接用:NEW.emp_id seq.NEXTVAL;结果编译器报错。PL/SQL 里给:NEW列赋值要用SELECT ... INTO或者直接:NEW.emp_id : seq.NEXTVAL;。我在测试时用:就没问题而用必报错这个语法习惯不熟的人要被卡很久。第二个坑是插入语句没有写列清单时触发器会按全列插入处理。比如表有emp_id, emp_name, salary三列你写INSERT INTO emp VALUES (张三, 8000)Oracle 会把张三当成第一列的值塞给emp_id类型不匹配直接报ORA-00947: not enough values或者隐式转换错误。正确做法是插入语句一定要写显式列清单触发器里判断:NEW.emp_id IS NULL才会自动生成序列值。遇到报错别急看看是不是 INSERT 语句的列清单和表中字段对不上。6.3 并发插入导致的主键冲突我专门模拟了 50 个并发会话同时插入数据用的是序列加触发器方案。说实话序列本身是保证并发安全的它的设计目标就是多会话取号不重复。但实际测试发现如果序列的CACHE设置太小比如默认的 20而并发塞入的请求太多会频繁触发缓存耗尽、重新申请的情况表现为会话等待、批量插入性能骤降。把CACHE加大到 200 后同一压测脚本的总体耗时下降了近一半。这个现象很隐蔽单看每一条 SQL 都快整体吞吐就是上不去最后定位到是序列争用。如果你发现并发下插入特别慢可以用SELECT ... FROM V$SEQUENCES看看序列的缓存情况再配合 AWR 报告看序列等待事件。一般增大CACHE能解决大部分问题但记住那句老话缓存有跳号风险业务自行取舍。6.4 如何把老表改成自增群里不少朋友问过MySQL 里一条ALTER TABLE ... AUTO_INCREMENT100就改好了Oracle 能不能这样答案是否定的Oracle 不支持直接修改已有列让它像 IDENTITY 列一样自增。如果你遇到的是老表想加自增功能实操上两条路第一条是保留原表触发器方案。假设表已经存在主键字段也有你只需要建序列再建触发器原表不动插入马上自动编号。这是成本最低的方式不需要动表结构。第二条是重建表把主键字段改成 IDENTITY 列数据导入后老序列废弃。这个流程适合停机窗口比较大的场景操作顺序是先把原表改名建新表带 IDENTITY把数据从旧表导入新表主键字段可以不导或者导 NULL 让序列重新发号最后删除旧表。如果你要保留原来的 ID 值就得让 IDENTITY 列的初始值大于目前最大值否则会撞键。6.5 面试题视角自增原理的底层理解最后从面试角度多点几句。Oracle 主键自增几乎是必问的面试题面试官不只是看你知不知道序列和触发器更想听到你对序列机制的理解。我建议至少能说清这几点序列是数据库对象和表没有直接绑定关系可以多表共享。NEXTVAL和CURRVAL的区别会话内CURRVAL必须先在当前会话调用过NEXTVAL才有效。CACHE对性能与跳号的影响机制。触发器方案和 IDENTITY 方案的底层逻辑12c 的 IDENTITY 底层其实也用序列。主键自增和 UUID 主键各自的优缺点序列自增的时空局部性好但跨库合并容易冲突UUID 全球唯一但页分裂问题在 Oracle 上影响不大因为 Oracle 是用堆表而不是索引组织表。把这几个点答全面试官基本就会觉得你是真的懂而不是背了个CREATE SEQUENCE模板。7. 延伸场景Oracle 分页和存储过程里的常见配合既然你搜到了主键自增大概率也是在搞 Oracle 开发。顺手把我日常工作中和自增主键强相关的两个场景也聊一下算是锦上添花。7.1 分页查询中的 ROWNUM 与自增顺序Oracle 和 MySQL 的分页语法完全不一样MySQL 是LIMIT 10 OFFSET 20Oracle 经典做法是包一层 ROWNUM。很多人习惯用自增主键排序再分页取最新 N 条记录SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY emp_id DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;这里一个经典误区是在内层排序外层用 ROWNUM 过滤。如果直接把ROWNUM和ORDER BY放在同一层ROWNUM 是在排序前分配的出来的结果顺序完全不对。正确的思路永远是先排序再分页。基于主键自增做时间排序很常见配合 IDENTITY 主键新插入的数据 ID 越来越大排序取数效率很高。7.2 存储过程里如何获取刚插入的自增主键业务系统经常会有这种需求插入一条记录后立刻拿到自增主键的值用于关联子表或者返回给前端。MySQL 里用LAST_INSERT_ID()Oracle 里没有这个函数。如果你用的是序列方案最好的做法是在插入语句里直接返回序列值DECLARE v_emp_id emp.emp_id%TYPE; BEGIN INSERT INTO emp (emp_id, emp_name) VALUES (seq_emp_id.NEXTVAL, 张三) RETURNING emp_id INTO v_emp_id; DBMS_OUTPUT.PUT_LINE(新员工ID || v_emp_id); END; /RETURNING子句可以把字段值返回到变量里这是 Oracle 的标准做法。如果你用的是触发器方案插入时没接触序列那就得在插入后用seq_emp_id.CURRVAL获取当前会话最新值但前提是当前会话确实从序列取过号。还有更稳妥的查询式触发器赋值后通过SELECT seq_emp_id.CURRVAL FROM DUAL拿到刚生成的 ID。需要注意CURRVAL是会话级别的别的会话用了同一序列不影响你的查询。如果你用了 IDENTITY 列就没法直接用CURRVAL因为底层序列是自动管理的你连序列名都不知道。这种情况下建议用RETURNING或者把 IDENTITY 列对应的底层序列找出来SELECT SEQUENCE_NAME FROM ALL_TAB_IDENTITY_COLS WHERE TABLE_NAME EMP;拿到序列名后再用CURRVAL或者干脆在建表时自己指定序列名省得后期还得查。我实测中更推荐RETURNING的方式不依赖序列名也不依赖会话状态代码兼容性最好。8. 最后分享一点实操体会一套东西写下来我自己也重新梳理了一遍思路。Oracle 实现主键自增的核心思路就是序列所有方案都围绕从序列取号这件事展开。别再纠结 Oracle 为什么不自带自增序列的设计自由度比AUTO_INCREMENT高得多用顺了之后你会发现在一些复杂编号场景里序列反而是更趁手的工具。我在实际项目里的选择标准很简单12c 以上新表用 IDENTITY老表加触发器高并发业务把序列 CACHE 调大严格连续号场景老老实实用 NOCACHE。每一种选择背后都有对应的代价关键是认清你业务里自增到底是为了什么——如果只是为了唯一标识就大胆容忍跳号如果流水号要求连续无洞那就得在性能和跳号之间找到平衡点。开发时翻来覆去踩坑的经历让我明白Oracle 的学习曲线陡但每一步都值得。前面列的那些坑都是文档不会明说的遇到了记得回来看看这篇。
Oracle 主键自增全解析：序列、触发器与12c IDENTITY实战
刚接手一个从 MySQL 迁到 Oracle 的老项目第一件事就把我整懵了建表语句里写的AUTO_INCREMENTOracle 直接报语法错误。Oracle 数据库就是这么个性人家 MySQL、SQL Server 天生自带主键自增它偏不非得让你自己想办法。折腾了一下午翻了无数帖子踩了一堆坑总算把几种方案都摸透了。这篇文章就把我实际验证过的几种 Oracle 主键自增实现方式连同触发器的坑、序列跳号的问题、12c 新特性的坑一次性说清楚。1. 问题根源Oracle 为什么不自带主键自增先说点背景帮刚入门的同学理解为什么会有这个问题。Oracle 数据库从诞生那天起设计哲学就和 MySQL 不太一样。MySQL 是互联网时代的产品讲究简单、快、省心AUTO_INCREMENT一条命令搞定自增主键。Oracle 走的是企业级、高可靠、强一致的路线它的设计者认为主键生成策略是业务逻辑的一部分不应该由数据库隐式处理而是应该显式地交给开发人员管理。所以 Oracle 提供了SEQUENCE序列这个独立于表的数据库对象。序列的本质是一个计数器你可以让 N 个会话同时取号它保证取出来的号不重复、不冲突。但序列本身不关心你要把它用来做主键还是订单号还是别的什么编号它就是个发号器。拿到序列的值之后你怎么填到表里Oracle 不管这就给了开发者灵活度也让新手犯了难——到底该咋用在深入方案之前先把两个核心概念捋清楚。第一个是SEQUENCE序列它负责生成一组不重复的数字序列可以被多个表共享也可以给单个表专用。第二个是TRIGGER触发器它是 Oracle 里的一种特殊存储过程在表的 INSERT 或 UPDATE 操作前自动执行可以用来给主键字段赋值。明白了这两个东西后面所有方案都是围绕它们组合出来的。2. 方案一序列加触发器最稳妥的传统玩法这套方案是 Oracle 江湖上流传最广的写法在 11g 及更早版本里几乎是唯一的标准答案。它的核心逻辑就两步先建一个序列用来发号再建一个BEFORE INSERT触发器在每次插入数据前自动从序列里取一个新号填进主键字段。2.1 序列创建与参数解读创建序列的语法是核心每个参数我都实测过给你逐一说明CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1 MAXVALUE 99999999 NOCACHE NOCYCLE;START WITH 1从 1 开始发号这是最常见的设置你完全可以从 1000 开始给手工插入的数据留点空间。INCREMENT BY 1每次发号递增 1如果需要步进为 2 或者更大自己调整。MAXVALUE序列的最大值到了这个值之后的行为由CYCLE/NOCYCLE决定。生产环境建议设一个足够大的上限比如 9999999999。NOCACHE不缓存序列值每次取号都写数据字典性能差点但保证不丢号。如果你用CACHE 20数据库崩溃时缓存里还没使用的号直接就跳过去了下次重启序列会从缓存后的值继续。关于这个坑后面第 6 部分我会详细讲。NOCYCLE用完不循环。如果设成CYCLE到了MAXVALUE就从头开始有可能出现主键重复除非你有特殊的业务需求主键场景下强烈建议NOCYCLE。在实际项目里我还是建议你在生产环境用CACHE。原因很简单序列发号是高频操作每次取号都要更新数据字典的行如果是NOCACHE在高并发插入场景下这个更新会变成一个严重的串行瓶颈。我测过CACHE 100的场景批量插入 10 万条数据耗时比NOCACHE少了差不多三分之一。2.2 触发器自动赋值序列建好了下一步是建触发器。这里默认你已经有一张emp表主键字段叫emp_idCREATE OR REPLACE TRIGGER trg_emp_auto_id BEFORE INSERT ON emp FOR EACH ROW BEGIN IF :NEW.emp_id IS NULL THEN SELECT seq_emp_id.NEXTVAL INTO :NEW.emp_id FROM DUAL; END IF; END; /这段代码里有几个地方新手特别容易写错我一个个说。BEFORE INSERT表示插入前触发FOR EACH ROW表示每一行插入都触发一次这是行级触发器不加这句的话整个语句只触发一次主键全都变成同一个值肯定不是你想要的。IF :NEW.emp_id IS NULL这个判断非常关键——只有当你没传主键值的时候才自动生成。有些人图省事不写这个判断结果手动指定主键插入时触发器把手工值覆盖了数据全乱。SELECT seq_emp_id.NEXTVAL INTO :NEW.emp_id FROM DUAL是从序列取下一个值赋给新行的主键字段。这里为什么要有FROM DUAL因为 Oracle 不允许没有FROM子句的SELECTDUAL是一张虚拟表专门干这事的。SELECT ... INTO :NEW.xxx的语法是给变量赋值的固定写法不同于普通查询这里是在 PL/SQL 块内部必须用INTO才能把序列值塞进触发器变量。全部建完后插入一条数据测试一下INSERT INTO emp (emp_name, salary) VALUES (张三, 8000); SELECT * FROM emp;不需要在 INSERT 里写emp_id触发器会帮你自动填充一个唯一值。这套方案的优点是比较通用不管哪个版本不管表结构多复杂都能用。缺点是代码量大每张需要自增主键的表都要配套建序列和触发器表多的时候有点烦。2.3 触发器与序列的命名规范这里顺便聊聊命名。我在实际项目里见过有人建了几十个序列和触发器命名乱七八糟后来维护的时候根本分不清哪个序列对应哪张表。建议采用seq_表名_字段名和trg_表名_字段名的命名方式。这不是什么官方强制的标准但后来的同事接手会感谢你。Oracle 的数据库字典视图中查序列和触发器也是按名字过滤的规范命名查起来一目了然。3. 方案二11g 起用默认值调用序列少写一个触发器如果你用的是 11g 及以上版本可以稍微省点事Oracle 在创建表的时候允许直接把序列的NEXTVAL设为字段默认值。这样连触发器都不用建了表定义自带自增逻辑。3.1 表级默认值实现自增具体做法是建表时把某列默认值指定为序列的下一个值CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE TABLE emp ( emp_id NUMBER DEFAULT seq_emp_id.NEXTVAL PRIMARY KEY, emp_name VARCHAR2(50), salary NUMBER(10,2) );插入数据时只要 INSERT 语句不写emp_id字段Oracle 就会自动用默认值也就是序列的下一个值填充。这和 MySQL 的AUTO_INCREMENT从使用体验上看几乎一样了。要注意的一个限制是字段的默认值不能引用CURRVAL只能引用NEXTVAL因为CURRVAL对于会话来说是会话级状态不是纯粹的表达式值。另外如果 INSERT 语句显式给emp_id传了值默认值就不会生效这和触发器的行为一致。3.2 默认值方案的触发器替代场景这个方案最大的优势是少写一个触发器建表语句也更清晰。但有了序列还是要手动维护序列。有些场景下默认值方案比触发器方案更灵活比如你有几张表要共享同一个序列生成全局唯一编号每张表都能直接用DEFAULT seq_xxx.NEXTVAL不需要为每张表单独建触发器。我实际项目中遇到过这样一个需求三个子系统共用一套流水号要求生成的 ID 全局唯一区分不出是哪张表产生的。如果用独立触发器得建三个每个里面逻辑一样只是表不同用默认值方案一个序列三张表共享表里什么都不用配清爽得很。不过提醒一句跨表共享序列意味着编号有空洞业务上如果要求每张表内部编号连续就不适合共享序列。3.3 习惯了 Oracle 的写法之后说实话我刚从 MySQL 转过来的时候特别看不惯这套建序列再设默认值的操作。但用久了反而觉得序列这种显式的发号机制在某些场景下比 MySQL 的AUTO_INCREMENT要灵活得多。比如你想让订单号和用户ID错开区间或者多个表用一个统一编号池MySQL 做起来很别扭Oracle 用一个序列就能搞定。这是设计理念的差异谈不上谁绝对好谁绝对差。4. 方案三Oracle 12c 的 IDENTITY 列终于有了正统自增如果你用的是 12c 或更高版本Oracle 总算是开了窍提供了GENERATED AS IDENTITY语法这玩意儿和 MySQL 的AUTO_INCREMENT就是同一个亲儿子了。它的底层实现其实还是序列但 Oracle 帮你把序列自动创建、自动绑定、默认值设置这些细节全部隐藏了你看不到序列的名字也不需要关心触发器。4.1 标准写法与变体创建语法非常简洁CREATE TABLE emp ( emp_id NUMBER GENERATED AS IDENTITY PRIMARY KEY, emp_name VARCHAR2(50), salary NUMBER(10,2) );建完表直接插入数据啥都不用管Oracle 全自动就像回到了 MySQL 的怀抱。这里还有个加分项Oracle 会自动配套创建一个普通序列绑定到表的 IDENTITY 列上。如果你想控制自增初始值、步长可以用变体语法CREATE TABLE emp ( emp_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100 INCREMENT BY 2 PRIMARY KEY, emp_name VARCHAR2(50) );BY DEFAULT ON NULL的意思是当插入的值为 NULL 时自动取序列如果显式给了非 NULL 值就用你给的值。这里有个容易搞混的地方GENERATED BY DEFAULT AS IDENTITY和GENERATED BY DEFAULT ON NULL AS IDENTITY区别在前者如果你显式插入 NULL 会报错你必须提供非 NULL 值或完全不写这个字段后者允许你显式插入 NULLOracle 自动改为序列值。实际用下来显式INSERT ... VALUES (NULL, ...)的场景比较少但两条语句的语义差别面试时经常被问到。4.2 自主定制的限制和注意点用 IDENTITY 列有两个坑必须记牢。第一你不能在一个已存在的表上通过ALTER TABLE ... ADD直接加 IDENTITY 列12c 支持的只是建表时定义 IDENTITY 列。如果你已经有张老表想改成自增只能重新建表数据迁移或者老老实实回到序列加触发器方案。第二IDENTITY 列底层自动创建的序列是默认NOORDER的在高并发下插入的顺序和自增值的递增顺序可能不完全一致如果业务上要求自增值必须严格反映插入先后就需要额外考虑。另外要表扬的是 Oracle 允许你查询 IDENTITY 列对应的序列信息在ALL_TAB_IDENTITY_COLS视图里能看到表与序列的关联。有时候需要重置序列的值或修改步长却找不到自动生成的序列名用这个视图一查就出来了。4.3 什么时候选 IDENTITY我的建议是新项目、新表、版本在 12c 以上直接无脑用 IDENTITY。它最接近你熟悉的 MySQL 习惯代码最干净不需要单独维护序列对象。但如果是老系统表已经建好了数据量巨大又不想停服务那还是用序列加触发器或者默认值方案更现实。方案选型从来不是比哪个最新而是看哪个能平稳落地。5. 三种方案横向对比与选型决策把三种方案放一起对比很多人会纠结选哪个。我整理了一个表你根据自己项目的实际情况套用就行。对比维度序列加触发器默认值调用序列IDENTITY 列适用版本全版本通用11g 及以上12c 及以上需要维护序列是是否自动创建需要写触发器是否否是否允许手动指定主键插入允许触发器判断空才自动生成允许显式传值覆盖默认BY DEFAULT允许AS IDENTITY不允许多表共享同一编号池可以一个序列多个触发器可以一个序列多个默认值不可以每表独立配置复杂度高表和对象耦合中低代码可读性一般逻辑分散在触发器中较好最好从可维护性角度我个人的推荐顺序是新表选 IDENTITY老表迁移可以选默认值只有碰到老版本数据库10g 及以下才不得不回到触发器方案。这里多提醒一句触发器的执行对性能有损耗虽然损耗通常可以忽略但在每秒上千次的插入场景下实测还是比默认值方式多出 5% 到 10% 的延迟。高并发系统能用默认值或 IDENTITY就别用触发器。6. 实操中踩过的坑与排查技巧写这篇博客之前我把三种方案全部在本地建了测试环境跑了一遍中间遇到的问题不少有些问题光看文档根本想不到给你一一列出来。6.1 序列跳号问题最经典的一个坑是序列跳号。我测试CACHE 20时插入 100 条数据一切正常。然后我干脆地重启了一下数据库再插入时发现 ID 直接从 31 而不是 21 继续了20 到 30 之间的号完全消失。原因就是CACHEOracle 从数据字典里取序列号是批量取的取一次申请一批缓存在内存里慢慢发。数据库正常关闭时缓存会写回但实例崩溃或强制重启时没有来得及使用的缓存序列值就永久丢失表现出来就是跳号。解决跳号没有完美的方案只能根据业务取舍。严格连续号要求如银行流水就别用缓存NOCACHE虽然慢一点但逻辑上不丢号。互联网业务只是用来自增主键跳号完全无感用CACHE 1000性能更好。有一点需要明确就算用NOCACHE并发事务回滚也会导致个别序列号被消耗但未真正使用序列有间隙是一个正常现象不要试图让主键自增做到绝对无洞那是和数据库设计较劲。6.2 触发器方案的两个大坑触发器方案我遇到的第一个坑是吐槽了很久的:NEW赋值。很多人写触发器直接用:NEW.emp_id seq.NEXTVAL;结果编译器报错。PL/SQL 里给:NEW列赋值要用SELECT ... INTO或者直接:NEW.emp_id : seq.NEXTVAL;。我在测试时用:就没问题而用必报错这个语法习惯不熟的人要被卡很久。第二个坑是插入语句没有写列清单时触发器会按全列插入处理。比如表有emp_id, emp_name, salary三列你写INSERT INTO emp VALUES (张三, 8000)Oracle 会把张三当成第一列的值塞给emp_id类型不匹配直接报ORA-00947: not enough values或者隐式转换错误。正确做法是插入语句一定要写显式列清单触发器里判断:NEW.emp_id IS NULL才会自动生成序列值。遇到报错别急看看是不是 INSERT 语句的列清单和表中字段对不上。6.3 并发插入导致的主键冲突我专门模拟了 50 个并发会话同时插入数据用的是序列加触发器方案。说实话序列本身是保证并发安全的它的设计目标就是多会话取号不重复。但实际测试发现如果序列的CACHE设置太小比如默认的 20而并发塞入的请求太多会频繁触发缓存耗尽、重新申请的情况表现为会话等待、批量插入性能骤降。把CACHE加大到 200 后同一压测脚本的总体耗时下降了近一半。这个现象很隐蔽单看每一条 SQL 都快整体吞吐就是上不去最后定位到是序列争用。如果你发现并发下插入特别慢可以用SELECT ... FROM V$SEQUENCES看看序列的缓存情况再配合 AWR 报告看序列等待事件。一般增大CACHE能解决大部分问题但记住那句老话缓存有跳号风险业务自行取舍。6.4 如何把老表改成自增群里不少朋友问过MySQL 里一条ALTER TABLE ... AUTO_INCREMENT100就改好了Oracle 能不能这样答案是否定的Oracle 不支持直接修改已有列让它像 IDENTITY 列一样自增。如果你遇到的是老表想加自增功能实操上两条路第一条是保留原表触发器方案。假设表已经存在主键字段也有你只需要建序列再建触发器原表不动插入马上自动编号。这是成本最低的方式不需要动表结构。第二条是重建表把主键字段改成 IDENTITY 列数据导入后老序列废弃。这个流程适合停机窗口比较大的场景操作顺序是先把原表改名建新表带 IDENTITY把数据从旧表导入新表主键字段可以不导或者导 NULL 让序列重新发号最后删除旧表。如果你要保留原来的 ID 值就得让 IDENTITY 列的初始值大于目前最大值否则会撞键。6.5 面试题视角自增原理的底层理解最后从面试角度多点几句。Oracle 主键自增几乎是必问的面试题面试官不只是看你知不知道序列和触发器更想听到你对序列机制的理解。我建议至少能说清这几点序列是数据库对象和表没有直接绑定关系可以多表共享。NEXTVAL和CURRVAL的区别会话内CURRVAL必须先在当前会话调用过NEXTVAL才有效。CACHE对性能与跳号的影响机制。触发器方案和 IDENTITY 方案的底层逻辑12c 的 IDENTITY 底层其实也用序列。主键自增和 UUID 主键各自的优缺点序列自增的时空局部性好但跨库合并容易冲突UUID 全球唯一但页分裂问题在 Oracle 上影响不大因为 Oracle 是用堆表而不是索引组织表。把这几个点答全面试官基本就会觉得你是真的懂而不是背了个CREATE SEQUENCE模板。7. 延伸场景Oracle 分页和存储过程里的常见配合既然你搜到了主键自增大概率也是在搞 Oracle 开发。顺手把我日常工作中和自增主键强相关的两个场景也聊一下算是锦上添花。7.1 分页查询中的 ROWNUM 与自增顺序Oracle 和 MySQL 的分页语法完全不一样MySQL 是LIMIT 10 OFFSET 20Oracle 经典做法是包一层 ROWNUM。很多人习惯用自增主键排序再分页取最新 N 条记录SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY emp_id DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;这里一个经典误区是在内层排序外层用 ROWNUM 过滤。如果直接把ROWNUM和ORDER BY放在同一层ROWNUM 是在排序前分配的出来的结果顺序完全不对。正确的思路永远是先排序再分页。基于主键自增做时间排序很常见配合 IDENTITY 主键新插入的数据 ID 越来越大排序取数效率很高。7.2 存储过程里如何获取刚插入的自增主键业务系统经常会有这种需求插入一条记录后立刻拿到自增主键的值用于关联子表或者返回给前端。MySQL 里用LAST_INSERT_ID()Oracle 里没有这个函数。如果你用的是序列方案最好的做法是在插入语句里直接返回序列值DECLARE v_emp_id emp.emp_id%TYPE; BEGIN INSERT INTO emp (emp_id, emp_name) VALUES (seq_emp_id.NEXTVAL, 张三) RETURNING emp_id INTO v_emp_id; DBMS_OUTPUT.PUT_LINE(新员工ID || v_emp_id); END; /RETURNING子句可以把字段值返回到变量里这是 Oracle 的标准做法。如果你用的是触发器方案插入时没接触序列那就得在插入后用seq_emp_id.CURRVAL获取当前会话最新值但前提是当前会话确实从序列取过号。还有更稳妥的查询式触发器赋值后通过SELECT seq_emp_id.CURRVAL FROM DUAL拿到刚生成的 ID。需要注意CURRVAL是会话级别的别的会话用了同一序列不影响你的查询。如果你用了 IDENTITY 列就没法直接用CURRVAL因为底层序列是自动管理的你连序列名都不知道。这种情况下建议用RETURNING或者把 IDENTITY 列对应的底层序列找出来SELECT SEQUENCE_NAME FROM ALL_TAB_IDENTITY_COLS WHERE TABLE_NAME EMP;拿到序列名后再用CURRVAL或者干脆在建表时自己指定序列名省得后期还得查。我实测中更推荐RETURNING的方式不依赖序列名也不依赖会话状态代码兼容性最好。8. 最后分享一点实操体会一套东西写下来我自己也重新梳理了一遍思路。Oracle 实现主键自增的核心思路就是序列所有方案都围绕从序列取号这件事展开。别再纠结 Oracle 为什么不自带自增序列的设计自由度比AUTO_INCREMENT高得多用顺了之后你会发现在一些复杂编号场景里序列反而是更趁手的工具。我在实际项目里的选择标准很简单12c 以上新表用 IDENTITY老表加触发器高并发业务把序列 CACHE 调大严格连续号场景老老实实用 NOCACHE。每一种选择背后都有对应的代价关键是认清你业务里自增到底是为了什么——如果只是为了唯一标识就大胆容忍跳号如果流水号要求连续无洞那就得在性能和跳号之间找到平衡点。开发时翻来覆去踩坑的经历让我明白Oracle 的学习曲线陡但每一步都值得。前面列的那些坑都是文档不会明说的遇到了记得回来看看这篇。
