用 BEFORE INSERT 触发器来生成流水号，这条路本身没问题，但有三类坑基本绕不开：并发场景下的重复号、跨表查询引发的报错（ORA-04091），以及不同数据库之间的语法不兼容。具体到实现上，MySQL 通常要配合独立计数表和 LAST_INSERT_ID()；Oracle 则必须声明 FOR EACH ROW，并通过赋值语句完成处理；PostgreSQL 更直接，应该直接赋值 nextval()，而不是走 SELECT INTO。

直接用 BEFORE INSERT 触发器生成流水号是可行的，但必须避开三类坑：并发重复、跨库/跨表查询报错（如 ORA-04091）、以及数据库间语法不兼容。MySQL 没 NEXTVAL，Oracle 不能少 FOR EACH ROW，PostgreSQL 不接受 SELECT ... INTO 赋值。

MySQL 必须用独立计数表 + LAST_INSERT_ID()

MySQL 没原生序列，CREATE SEQUENCE 在 8.0+ 虽支持，但无法在触发器中调用 NEXT VALUE FOR。硬写 SELECT MAX(bill_no) ... 会并发冲突；用 AUTO_INCREMENT 主键拼接又无法带前缀。

• 建一张 seq_counter 表，每行对应一种业务类型：

  CREATE TABLE seq_counter (
  biz_type VARCHAR(10) PRIMARY KEY,
  counter INT NOT NULL DEFAULT 0
  );

• 触发器内用原子更新：

  UPDATE seq_counter 
  SET counter = LAST_INSERT_ID(counter + 1) 
  WHERE biz_type = NEW.business_type;

• 立刻读取：SET NEW.bill_no = CONCAT(UPPER(NEW.business_type), '-', LPAD(LAST_INSERT_ID(), 6, '0'));
• 漏掉 PRIMARY KEY 或唯一索引，UPDATE 可能影响多行，导致错乱

Oracle 必须写成赋值语句，且带 FOR EACH ROW

SELECT seq_bill.NEXTVAL INTO :new.bill_no FROM dual 这句写法本身没问题，看起来也很“标准”；但要是没加 FOR EACH ROW，触发器只会跑一次，结果就是这一批插入的数据全拿到同一个编号。还有一种更常见的坑：在触发器里反查当前这张表，比如想按部门先做个统计再去拼前缀，基本会当场触发 ORA-04091: table is mutating。

• 正确写法是 PL/SQL 赋值：:new.bill_no := seq_bill.NEXTVAL;，不走 SELECT INTO
• 必须显式声明：CREATE OR REPLACE TRIGGER tr_gen_bill_no BEFORE INSERT ON orders FOR EACH ROW
• 日期拼接用 TO_CHAR(SYSDATE, 'YYYYMMDD')，别依赖会话 NLS_DATE_FORMAT，否则测试环境正常、生产环境出错
• 想实现“当日归零”，不能靠 TRUNC(SYSDATE) 查最大号——得用复合触发器（11g+）或改用应用层控制

PostgreSQL 直接用 nextval()，但别混用 SELECT 赋值

PG 的 nextval('seq_name') 是原子函数，无需额外锁表。但常见错误是照搬 Oracle 写法，在触发器里写 SELECT nextval('seq_bill') INTO NEW.bill_no —— 这在 PG 中语法不合法，会报错。

• 必须用直接赋值：NEW.bill_no := 'SO-' || LPAD(nextval('seq_so')::TEXT, 6, '0');
• 不同业务类型建议建独立序列（seq_so、seq_po），避免共用一个序列导致跳号不可控
• 拼日期用 to_char(CURRENT_DATE, 'YYYYMMDD')，不是 NOW() —— 后者含时分秒，且事务内多次调用可能返回不同值
• 序列值不回滚是设计行为，如果业务真要求“零跳号”，得加唯一约束兜底，而不是在触发器里反复重试

所有数据库都禁用 SELECT MAX() + 1 模式

无论哪种数据库，只要触发器里出现 SELECT MAX(xxx) FROM same_table WHERE ...，就是并发隐患。两个事务同时查到 0001，都写入 0002，最终入库两条相同流水号。

• MySQL 中 SELECT ... FOR UPDATE 可行，但锁粒度大、性能差，且触发器里不能跨事务保持锁
• PostgreSQL 可用 SELECT ... FOR UPDATE SKIP LOCKED 缓解，但仍不如原生序列可靠
• Oracle 的 SELECT ... FOR UPDATE 在触发器中直接触发 mutating table 错误，根本走不通
• 真正安全的底线是：流水号字段加 UNIQUE 约束，让数据库在最后一步拦截重复，而不是寄希望于触发器逻辑绝对不并发

最易被忽略的是：流水号生成失败时，整个 INSERT 会回滚，但应用层日志可能只显示“插入失败”，看不出是触发器里查计数表超时、还是序列权限不足、或是拼接后超出字段长度。上线前务必用并发压测工具跑真实流量，别只手动插几条。

《夸克》非常好用的免费AI浏览器

下载APP查看