用惯了MySQL的人转过来用Oracle第一件事就是会发现建表的时候没有AUTO_INCREMENT这种写法。网上搜一圈答案五花八门序列、触发器、IDENTITY列、GUID……看着都行但没人讲清楚到底该用哪个、为什么用、坑在哪里。这篇文章就把Oracle主键自增这件事彻底讲透从原理到代码再到排错都是实际写库里用得上的东西。适合刚从MySQL转过来的开发也适合DBA建表时想搞清楚方案差异的读者。1. 为什么Oracle没有“内置自增”三种主流方案怎么选MySQL的AUTO_INCREMENT是表级别的一个属性在表里保存一个计数器每次插入的时候自动加一。这个设计在单机环境下很爽但到了Oracle这种诞生于大型机时代、从一开始就为高并发和集群环境设计的数据库里表级计数器就是灾难——多实例并发写同一张表的时候计数器得全局加锁性能全费在锁上了。所以Oracle很早就把“生成序号”这个能力抽出来做成了独立于表的数据库对象也就是序列SEQUENCE。Oracle 12c之前序列就是事实上的自增方案搭配触发器或者应用层显式调用。12c之后Oracle引入了IDENTITY列语法总算做到了和MySQL的AUTO_INCREMENT形式上一致。但注意底层仍然是序列Oracle只是把序列的创建和管理藏起来了。所以在实际工作中你会看到三种流派序列 触发器建表时挂一个BEFORE INSERT触发器应用层完全无感INSERT语句不用带主键序列 应用层显式赋值开发在INSERT语句里直接写seq.NEXTVAL不用触发器IDENTITY列12c建表时直接声明语法上和MySQL最接近。选哪个不是看心情主要看老系统兼容性、版本、以及团队约定。下面我把每个方案的操作细节、优缺点、坑一次说完。2. 序列SEQUENCE原理与实操自增的核心引擎2.1 序列的底层设计逻辑序列本质上是一个全局的、独立于事务的计数器。这句话要拆开理解。“全局”意味着所有会话、所有用户有权限的前提下都能拿到同一个序列的下一个值。“独立于事务”意味着你执行了SELECT seq.NEXTVAL FROM DUAL之后即使后续的INSERT语句回滚了这个序号也已经消耗掉了不会归还。这就导致了一个常见现象表里主键是跳号的中间有空洞。这不是故障是序列的设计特性。我在生产环境遇到过开发报问题说“主键怎么不连续”。解释一下序列不回滚的原理之后对方才明白Oracle和MySQL在自增这事上的根本区别MySQL的AUTO_INCREMENT虽然也不回滚已分配的值但它的计数器是跟着表走的Oracle的序列是全库共享的独立对象两者在设计目标上就不一样。序列的设计目标是在高并发、RAC多节点环境下依然保证快速拿到不重复的序号而不是保证连续。2.2 创建序列的完整参数与选择理由创建序列的语法并不复杂难在每一个参数你都该知道它是干什么的CREATE SEQUENCE seq_user_id START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999 NOCYCLE CACHE 20 NOORDER;逐个拆开讲START WITH起始值一般从1开始。INCREMENT BY步长通常为1。有些访问量极大的系统为了减少热点会把步长设为100让不同应用取到不同区间的ID但大多数业务用不到。MAXVALUE序列能到达的最大值。这里要注意和主键列类型的匹配如果用NUMBER(10)最大能存10位整数那MAXVALUE就不要超过9999999999否则主键先溢出了序列还没到顶。NOCYCLE到顶之后报错ORA-08004而不是从头循环。主键必须用NOCYCLE一旦循环就会和已有数据的主键冲突。CACHE 20预先往内存里放20个序号。这是性能的关键也是跳号的来源。序列每次取NEXTVAL都是写数据字典的重操作缓存可以大幅减少这种写入次数。默认值是20生产环境可以调大比如一次缓存100或1000换取更好的并发性能。NOORDER不保证所有会话拿到的值严格按请求先后排序。在RAC环境下节点1拿了1-20节点2可能同时拿了21-40谁先落库完全看网络和调度。如果业务上不要求“先请求的先拿到小号”用NOORDER就对了。极端情况下才需要ORDER但性能会大幅下降还要配合NOCACHE。2.3 NEXTVAL与CURRVAL的使用规则序列的核心就两个伪列弄明白这俩序列就掌握了一半。NEXTVAL每次调用都会生成一个新值同时把当前值记为CURRVAL。CURRVAL不是全局的它只在“当前会话”里有效而且必须先在本会话调用过NEXTVAL之后才能用CURRVAL。最常见的踩坑场景开发写了个存储过程里面输出了NEXTVAL然后去另一个会话里查CURRVAL想拿到刚才插入的主键结果报ORA-08002: CURRVAL is not defined in this session。原因就是CURRVAL是会话级别的不是表或者数据库级别的。想拿到当前插入记录的ID要么在同一个PL/SQL块里用RETURNING INTO要么把主键查回来不要跨会话用CURRVAL。序列在SQL里使用也有位置限制只能在SELECT列表、VALUES子句、SET子句、VALUES/TO赋值等有限位置出现不能用在WHERE条件、ORDER BY里。这些Oracle官方文档都有明确限定实际开发中也很容易遇到ORA-02287错误。2.4 序列在应用层显式赋值的两种写法序列单独使用的场景最常见的是INSERT语句里直接取NEXTVALINSERT INTO t_user (id, name) VALUES (seq_user_id.NEXTVAL, 张三);这种写法简单直接性能好而且主键的赋值逻辑在SQL里一眼就能看出来。另一个场景是在PL/SQL块里先从序列取值再作为参数传给其他逻辑DECLARE v_id NUMBER; BEGIN SELECT seq_user_id.NEXTVAL INTO v_id FROM DUAL; INSERT INTO t_user (id, name) VALUES (v_id, 李四); DBMS_OUTPUT.PUT_LINE(新的ID: || v_id); END; /注意这里我用了FROM DUAL。Oracle查询必须有FROMDUAL就是Oracle自带的一个单行单列的虚表专门用来执行SELECT 11、SELECT seq.NEXTVAL这类不涉及实际表的数据操作。3. 触发器自动赋值最“无感”的经典实现3.1 为什么要用触发器什么场景该用序列 触发器的经典组合本质上是把“取NEXTVAL”的动作从应用层挪到了数据库层。开发人员INSERT的时候完全不用管主键字段直接写INSERT INTO t_user (name) VALUES (王五)数据库在插进去之前自动把ID给填上。这个方案的优点是对应用透明老系统改造时最有用。我见过不少老项目几百个表都靠这个模式建主键统一管理应用层代码不用动DBA在数据库侧就把自增逻辑做了。缺点也很明显每插一行都要触发一次触发器逻辑触发器和SQL之间的上下文切换在高吞吐批量插入时会有性能损耗。大数据量导入的场景用这个方案会明显感觉慢我后面专门说优化办法。3.2 三步走建表、建序列、建触发器以用户表为例完整的一整套代码如下第一步建表CREATE TABLE t_user ( id NUMBER(10) PRIMARY KEY, username VARCHAR2(50), created_date DATE );第二步建序列CREATE SEQUENCE seq_t_user_id START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999 NOCYCLE CACHE 20;第三步建触发器CREATE OR REPLACE TRIGGER trg_t_user_bir BEFORE INSERT ON t_user FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT seq_t_user_id.NEXTVAL INTO :NEW.id FROM DUAL; END; /这段触发器有几个关键细节值得注意。WHEN (NEW.id IS NULL)这里NEW前面不能加冒号。很多人从:NEW的习惯直接写编译直接报错。但在赋值语句里:NEW就必须带冒号写成:NEW.id。这个冒号的有无是很多编译问题的根源是新手最容易踩的坑。WHEN条件本身的意思是如果INSERT语句里显式传了ID值触发器就跳过不覆盖如果没传或者传了NULL就让序列来生成。这种写法给开发留了后门——遇到历史数据导入、数据修补这些需要手工指定ID的场合不会和触发器打架。3.3 插入后如何拿到自动生成的主键触发器自动填了主键那应用层怎么知道新记录的ID是多少最优雅的方式是RETURNING INTO类似于其他数据库的OUTPUT子句直接从DML操作里把回写的值拿出来DECLARE v_new_id t_user.id%TYPE; BEGIN INSERT INTO t_user (username) VALUES (赵六) RETURNING id INTO v_new_id; DBMS_OUTPUT.PUT_LINE(新记录ID: || v_new_id); END; /用RETURNING INTO的好处是不需要再回查一次表少一条SQL也不用关心触发器内部是怎么赋值的。Java端如果配合JDBC的RETURN_GENERATED_KEYS机制也能拿到这个自增主键具体实现各家ORM框架不同后面有机会单独写。3.4 触发器方案的性能优化与注意事项生产环境里我遇到过最典型的性能问题凌晨跑批一批数据几十万行触发器方案跑得特别慢。原因就是每一行INSERT都要触发一次PL/SQL上下文切换本质上是在SQL引擎和PL/SQL引擎之间来回蹦。优化办法分几种改成应用层显式赋值去掉触发器INSERT里直接用seq.NEXTVAL性能提升立竿见影用FORALL批量插入重新改写业务逻辑把逐行INSERT改成批量绑定实在要保留触发器就把CACHE调大比如从20调到1000减少序列内部的数据字典操作。触发器还有个隐性坑DDL变更时可能失效。如果表结构变了比如加了字段触发器会进入INVALID状态这时候INSERT会直接报ORA-04098触发器无效或未验证。ALTER TABLE之后记得查一下dba_objects的STATUS别等线上报错了才发现触发器失效。4. 12c的IDENTITY列最像MySQL的官方解法4.1 IDENTITY列的三种生成模式Oracle 12c开始支持IDENTITY列建表时直接声明语法上终于和MySQL的AUTO_INCREMENT对上号了。三种写法如下CREATE TABLE t_user ( id NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username VARCHAR2(50) );这里有三个关键词组合GENERATED ALWAYS、GENERATED BY DEFAULT、GENERATED BY DEFAULT ON NULL。三者的区别用一句话说清楚ALWAYS数据库完全接管应用层绝对不能指定ID。你手动传一个ID进去直接报ORA-32744。BY DEFAULT应用层传了ID就用传入的没传就用序列生成。这个模式最灵活适合数据迁移。BY DEFAULT ON NULL应用层传NULL时走序列生成传了具体值就用传入的值。注意区分传NULL和“没传”在严格意义上是两种情形ON NULL把两者都归为“可以走生成逻辑”。实际开发里如果只想要MySQL那种无脑自增用ALWAYS就够了。但如果你的系统里有历史数据导入、老数据修复这些场景BY DEFAULT ON NULL更稳妥它既保证了正常业务的无感自增又给了显式插入的逃生通道。4.2 IDENTITY列底层还是序列参数改进与限制IDENTITY列不是魔法它的底层就是序列。Oracle在创建表的时候自动生成一个隐藏序列这个序列你查得到但操作不了名字通常是ISEQ$$_数字这种形式。你可以通过修改表的MODIFY子句来控制它的部分行为比如CACHE大小ALTER TABLE t_user MODIFY (id GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 200));但是你不能直接ALTER SEQUENCE那个隐藏序列。如果想控制更细的序列参数或者想多个表共享一个序列极少见还是得回到手动建序列的老方案。IDENTITY列还有一个版本上的限制它要求数据库初始化参数COMPATIBLE不低于12.0.0。老库升级后想用IDENTITY先检查这个参数特别是从11g升级上来的系统COMPATIBLE默认可能还是11.2.0那语法直接报错。这个坑我见过不止一次。4.3 IDENTITY列和序列触发器的对比选择列一张对比表方便做决策参考对比维度序列 触发器IDENTITY列序列 应用层显式赋值适用版本所有版本12c所有版本应用层无感程度高完全不用管ID高完全不用管ID低开发需显式取NEXTVAL性能每行有额外触发器开销底层隐式序列无PL/SQL上下文切换最优无触发器开销允许显式指定ID可设置WHEN条件实现ALWAYS不允许其他两种允许完全允许管理和排查复杂度中等两个对象都要管低都在表定义里低SQL里直白可见RAC扩展性序列保底无额外风险同序列同序列在我看来新系统、新项目、12c以上版本优先选IDENTITY列管理成本最低代码最干净。存量老系统为了不动应用代码继续用序列触发器也完全没问题。性能敏感的大批量写入场景序列应用层显式赋值是三个方案里最合适的代价是开发要多写一点点东西。5. 常见问题与排查技巧照着抄的避坑清单5.1 主键冲突、序列跳号和跨会话问题速查表下面是实际工作中频率最高的几类问题直接做成表格方便排查用错误/现象根本原因解决方式ORA-08002: CURRVAL before NEXTVAL当前会话未调用过NEXTVAL直接读CURRVAL先执行SELECT seq.NEXTVAL FROM DUAL再读CURRVALORA-00001: unique constraint violated表里有比序列当前值更大的ID典型的是导入数据后没重设序列把序列调整到MAX(ID)1主键中间有大段空洞事务回滚、CACHE丢失、数据库异常重启正常现象无需处理ORA-32744: cannot insert into generated always identity columnALWAYS模式下应用显式传ID改成BY DEFAULT ON NULL模式或删除INSERT里的ID字段ALTER TABLE后INSERT报ORA-04098表结构变更导致触发器失效查询ALL_OBJECTS中触发器STATUS重新编译RAC下ID顺序和时间先后不一致序列NOORDER模式多节点各自缓存ID无法完全避免业务不依赖ID顺序即可5.2 数据导入后怎么把序列调整到MAX1这是每个人都会遇到的需求上线时导入了十万行历史数据主键ID已经用到了100000但序列还在1附近转悠。新插入一条记录主键立刻冲突。最稳妥且老版本兼容的做法是这样SELECT MAX(id) FROM t_user; -- 假设结果是100000 ALTER SEQUENCE seq_t_user_id INCREMENT BY 100001; SELECT seq_t_user_id.NEXTVAL FROM DUAL; ALTER SEQUENCE seq_t_user_id INCREMENT BY 1;原理是把临时步长跳到某个远大于当前表最大值的数字取一个NEXTVAL让序列直接越过表里的最大ID再把步长改回来。注意这里用100001而不是100000是因为NEXTVAL是在原值基础上加步长先确认当前序列值比100000小再计算好跨度。实际做之前建议先查一下序列当前的LAST_NUMBER避免改完还是冲突。Oracle 19c开始支持RESTARTOracle 18c以下用不了老库只能走临时改步长的方案。5.3 批量插入时的序列性能优化先说结论能用一条SQL完成的批量插入就不要写循环逐行INSERT。下面这段是最常见的低效写法BEGIN FOR i IN 1..10000 LOOP INSERT INTO t_user (id, username) VALUES (seq_t_user_id.NEXTVAL, user || i); END LOOP; COMMIT; END; /每循环一次序列取一次值SQL引擎和PL/SQL引擎来回切换一次一万行就切一万次。换成一条SQL里带子查询一个语句搞定INSERT INTO t_user (id, username) SELECT seq_t_user_id.NEXTVAL, user || LEVEL FROM DUAL CONNECT BY LEVEL 10000;大批量初始化数据、跑批、ETL场景这种改法性能差距是数量级的。如果还是嫌慢就去调序列的CACHE参数一次性缓存更多值到内存减少刷新次数。5.4 触发器不生效的两个隐蔽场景第一个隐蔽场景WHEN条件里判断的是NEW.id IS NULL但当应用层用INSERT ALL多表插入时触发器的执行语义在某些复杂分支下可能因为多表插入对目标表的分区、条件路由产生交互影响导致部分行没走到你预期的那条赋值路径。我的建议是这类复杂DML写完之后一定要抽样去查实际生成的ID不要只看前几行成功就以为全对。第二个隐蔽场景触发器在用户SYS、SYSTEM这种超级用户下执行DDL或某些特殊操作时可能不触发有些工具导入数据也默认绕过了触发器。碰到“看起来没生效”第一时间查目标表触发器状态SELECT trigger_name, status FROM all_triggers WHERE table_name T_USER;STATUS是VALID才行INVALID就重新编译。5.5 关于分享一个踩过几次坑之后的个人习惯现在我建表遇到主键自增需求标准动作是先问三件事数据库版本多少、生产环境有无RAC、业务系统对ID连续性有没有要求。然后沿着这个顺序做决策12c以上新表直接IDENTITY BY DEFAULT ON NULL老库或者被DBA规范限定了只能手动建序列那就建序列触发器大批量写入场景把触发器和应用层方案结合着用平时无感自增跑批时动态替换成显式取序列。最后分享一个实用的小习惯序列和触发器命名一定要规范到底。我见过不少库序列叫SEQ1、触发器叫TRG3维护的时候完全不知道是干嘛的。建议统一叫SEQ_表名_ID、TRG_表名_BIR这种格式一眼就知道归属哪张表。等系统跑几年后有人半夜起来排障会感谢你当年多写的那几个字。