如何利用SQL触发器自动生成业务流水号_确保SQL并发下的唯一性-SQL-PHP中文网

必须用 before insert 触发器生成流水号并赋值给 new.bill_no，因 after insert 更新同表会报错；mysql 用计数表 + last_insert_id() 原子递增，postgresql 直接用序列 nextval()。

如何利用sql触发器自动生成业务流水号_确保sql并发下的唯一性

BEFORE INSERT 触发器必须用，不能用 AFTER

流水号得在插入前就生成并写进 NEW.bill_no，否则主键或唯一约束会直接报错。AFTER INSERT 里再 UPDATE 同一张表，MySQL 会报 Can't update table 'orders' in stored function/trigger，PostgreSQL 则触发 mutating table 错误。

实操要点：

所有数据库都只用 BEFORE INSERT FOR EACH ROW（PostgreSQL/Oracle）或 BEFORE INSERT（MySQL）
MySQL 中 NEW 是可写的，但必须显式赋值：SET NEW.bill_no = ...，不能靠子查询临时变量间接改
别在触发器里调 SELECT ... FROM orders WHERE bill_no LIKE 'SO-%' ORDER BY id DESC LIMIT 1——高并发下查到的不是最新值，极易重复

MySQL 用计数表 + LAST_INSERT_ID() 原子递增

MySQL 没原生序列，CREATE SEQUENCE 在 8.0+ 虽支持，但无法在触发器中调用 NEXT VALUE FOR。最稳方案是建一张独立计数表，靠 UPDATE ... SET counter = LAST_INSERT_ID(counter + 1) 实现原子自增。

示例结构：

CREATE TABLE seq_counter (
  biz_type VARCHAR(10) PRIMARY KEY,
  counter INT NOT NULL DEFAULT 0
);
INSERT INTO seq_counter VALUES ('SO', 0), ('PO', 0), ('INV', 0);

触发器内写法：

DELIMITER $$
CREATE TRIGGER tr_gen_bill_no BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  DECLARE next_no INT DEFAULT 0;
  IF NEW.business_type IN ('SO', 'PO', 'INV') THEN
    UPDATE seq_counter 
      SET counter = LAST_INSERT_ID(counter + 1) 
      WHERE biz_type = NEW.business_type;
    SET next_no = LAST_INSERT_ID();
    SET NEW.bill_no = CONCAT(UPPER(NEW.business_type), '-', LPAD(next_no, 6, '0'));
  ELSE
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid business_type';
  END IF;
END$$
DELIMITER ;

注意点：

必须给 biz_type 加唯一索引，否则 UPDATE 可能影响多行
别漏掉 IF ... ELSE SIGNAL 校验，空值或非法类型会导致 bill_no 为 NULL 或拼出怪前缀
LPAD(..., 6, '0') 统一补零位数，避免后期按字符串排序错乱（比如 'SO-999' 排在 'SO-1000' 前面）

PostgreSQL 直接用序列 + nextval() 最省心

PG 原生序列就是为这场景设计的：nextval() 是事务级原子操作，不依赖锁表，也不怕并发。每个业务类型配一个序列，触发器里分支调用即可。

建序列示例：

CREATE SEQUENCE seq_so START 1 INCREMENT 1 NOCACHE;
CREATE SEQUENCE seq_po START 1 INCREMENT 1 NOCACHE;
CREATE SEQUENCE seq_inv START 1 INCREMENT 1 NOCACHE;

触发器函数写法：

CREATE OR REPLACE FUNCTION gen_bill_no()
RETURNS TRIGGER AS $$
BEGIN
  CASE NEW.business_type
    WHEN 'SO' THEN NEW.bill_no := 'SO-' || LPAD(nextval('seq_so')::TEXT, 6, '0');
    WHEN 'PO' THEN NEW.bill_no := 'PO-' || LPAD(nextval('seq_po')::TEXT, 6, '0');
    WHEN 'INV' THEN NEW.bill_no := 'INV-' || LPAD(nextval('seq_inv')::TEXT, 6, '0');
    ELSE RAISE EXCEPTION 'Unknown business_type: %', NEW.business_type;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_orders_before_insert
  BEFORE INSERT ON orders
  FOR EACH ROW EXECUTE FUNCTION gen_bill_no();

关键提醒：

加 NOCACHE 可减少跳号，但会降低性能；默认 CACHE 1 已足够多数业务，跳号是正常行为，不是 bug
别在函数里用 currval() 或 lastval() 取刚生成的值——它们返回的是当前会话最近一次 nextval() 结果，不可靠
如果业务要求“按日重置”，就别用单一序列，改用带日期的复合键计数表（如 (biz_type, serial_date) 主键），否则每天都要手动 ALTER SEQUENCE ... RESTART

并发唯一性真正的风险点不在触发器逻辑本身

很多人盯着 nextval() 或 LAST_INSERT_ID() 是否线程安全，却忽略更隐蔽的问题：事务隔离级别、索引缺失、以及字段约束没配全。

必须检查的三项：

bill_no 字段必须加 UNIQUE 约束，这是兜底防线。触发器生成失败或绕过触发器直插时，靠它拦住重复
计数表（如 seq_counter）的 biz_type 字段必须是 PRIMARY KEY 或带 UNIQUE INDEX，否则并发 UPDATE 可能锁表失败或产生死锁
PostgreSQL 若用 SELECT ... FOR UPDATE 手动加锁取最大号，务必配合 SKIP LOCKED，否则容易卡住其他会话