在企业管理系统的数据库设计中，不同业务类型往往要求单据编号遵循各自的规则，例如销售订单以SO开头、采购单以PO开头、退货单以RO开头，后面跟上日期和自增序号。如果把这些逻辑完全放在应用程序里，不仅代码分散，而且在多服务实例同时写入时很难保证序号不冲突。利用数据库触发器，可以在INSERT事件发生前自动计算并填充单据编号字段，由数据库自身的事务机制来保证一致性。
一、基础表结构设计
要实现按业务类型生成编号，首先需要一个配置表来记录每种业务的编号前缀和当前序号。这样触发器才能知道从哪个数字开始累加，以及生成的编号长什么样。配置表的设计应当尽量简单，同时考虑并发安全。
下面以MySQL 8.0为例，创建业务类型配置表与单据主表。配置表中business_type作为业务类型唯一标识，prefix是编号前缀，seq是当前已使用的最大序号，reset_cycle表示重置周期，这里用month表示按月重置。
CREATE TABLE biz_sequence (
business_type VARCHAR(20) PRIMARY KEY,
prefix VARCHAR(10) NOT NULL,
seq INT NOT NULL DEFAULT 0,
reset_cycle VARCHAR(10) NOT NULL DEFAULT 'month'
) ENGINE=InnoDB;
INSERT INTO biz_sequence (business_type, prefix, seq, reset_cycle) VALUES
('sale', 'SO', 0, 'month'),
('purchase', 'PO', 0, 'month'),
('return', 'RO', 0, 'month');
CREATE TABLE order_doc (
id INT AUTO_INCREMENT PRIMARY KEY,
doc_no VARCHAR(30) NOT NULL,
business_type VARCHAR(20) NOT NULL,
amount DECIMAL(10,2),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
上述结构中，order_doc表里的doc_no就是我们要通过触发器自动生成的字段。注意doc_no设置了NOT NULL，因此触发器必须保证在插入前为其赋值，否则语句会报错。配置表使用InnoDB引擎，便于在触发器里通过SELECT ... FOR UPDATE加行锁，防止并发更新丢号。
二、触发器实现自动编号
触发器的核心逻辑是：在插入order_doc之前，根据新行的business_type去biz_sequence里查找对应记录，使用FOR UPDATE锁定该行，判断是否需要因周期变化而重置序号，然后将seq加一，拼出新的doc_no并写回NEW.doc_no。这样每次插入都会得到一个格式为“前缀+年月+序号”的字符串。
下面的触发器演示了按月重置的实现。如果当前年月与表中记录的不一致，就把seq归零再递增，同时更新记录中的周期标记。这里用DATE_FORMAT来取年月，序号补零到四位，确保编号定长易排序。
DELIMITER $$
CREATE TRIGGER trg_before_insert_order_doc
BEFORE INSERT ON order_doc
FOR EACH ROW
BEGIN
DECLARE v_prefix VARCHAR(10);
DECLARE v_seq INT;
DECLARE v_cycle VARCHAR(10);
DECLARE v_cur_cycle VARCHAR(10);
SET v_cur_cycle = DATE_FORMAT(NOW(), '%Y%m');
SELECT prefix, seq, reset_cycle
INTO v_prefix, v_seq, v_cycle
FROM biz_sequence
WHERE business_type = NEW.business_type
FOR UPDATE;
IF v_cycle = 'month' THEN
IF v_seq = 0 OR v_cur_cycle > (SELECT DATE_FORMAT(updated_at,'%Y%m') FROM biz_sequence WHERE business_type = NEW.business_type) THEN
SET v_seq = 1;
ELSE
SET v_seq = v_seq + 1;
END IF;
ELSE
SET v_seq = v_seq + 1;
END IF;
UPDATE biz_sequence
SET seq = v_seq,
updated_at = NOW()
WHERE business_type = NEW.business_type;
SET NEW.doc_no = CONCAT(v_prefix, v_cur_cycle, LPAD(v_seq, 4, '0'));
END$$
DELIMITER ;
在上面的代码里，我们为biz_sequence补充了一个updated_at字段来记录上次更新周期，你需要在建表时加上它。触发器中先用FOR UPDATE锁住配置行，避免两个事务同时读到相同的seq。随后根据周期判断是延续序号还是从1开始，最后用CONCAT和LPAD拼出类似SO2024050001的编号。
这种写法的优点是逻辑完全下沉到数据库，应用层插入时只需要指定business_type和amount，不用关心编号怎么来。缺点是触发器调试不如程序直观，且如果业务类型在配置表不存在，变量会为NULL，导致doc_no异常，所以实际项目中应加上异常处理或外键约束。
三、并发与性能注意事项
在高并发下单场景里，触发器中的行锁可能成为瓶颈。因为如果所有销售单都锁同一行配置记录，事务只能串行化推进，吞吐量会受限于单点。此时可以考虑按业务类型加子分类，或者把序列缓存到应用端批量领取，但那就偏离了纯触发器方案。
另一个常见问题是死锁。如果多个触发器按不同顺序锁biz_sequence和order_doc，可能相互等待。由于本例只锁配置表且先插主表由数据库隐式处理，通常风险较低，但若有其他触发器也操作biz_sequence，就应统一访问顺序。此外，MySQL的触发器不支持显式事务控制，因此锁的释放依赖语句结束，无需手动UNLOCK。
-- 测试插入，观察编号自动生成
INSERT INTO order_doc (business_type, amount) VALUES ('sale', 100.00);
INSERT INTO order_doc (business_type, amount) VALUES ('purchase', 250.50);
INSERT INTO order_doc (business_type, amount) VALUES ('sale', 80.00);
SELECT doc_no, business_type, amount FROM order_doc;
执行上述测试语句后，你会看到sale类型的两行编号前缀都是SO且序号连续，purchase则是PO开头。这证明触发器已按业务类型分别计算。若跨月再插入，由于reset_cycle为month且updated_at已记录旧周期，序号会重新从0001开始，满足月度单据重新计数的习惯。
四、方案对比与适用场景
除了触发器，还可以用程序端UUID、数据库自增主键加前缀、或者序列对象（如PostgreSQL的SEQUENCE）来实现。UUID虽唯一但无序且太长，不适合人工辨认；自增主键加前缀简单但所有业务共享一个池，无法按类型隔离；序列对象可按类型建多个，但应用仍需拼装且不易做周期重置。
触发器方案最适合那些编号规则复杂、强依赖数据库事务、且团队希望业务逻辑少改应用的场景。它把规则收敛到一处，运维时直接改触发器或配置表即可。但若系统已全面使用ORM且禁止写存储过程，推行触发器会增加协作成本，这时可用轻量服务统一发号来替代。
| 方案 | 优点 | 缺点 |
|---|---|---|
| 触发器生成 | 规则集中、事务安全、应用无感知 | 调试难、行锁可能制约并发 |
| 程序拼装自增ID | 实现简单、易扩展 | 类型间不隔离、需应用保证唯一 |
| 独立发号服务 | 高性能、跨库通用 | 引入额外组件、网络开销 |
通过上面的对比可以看出，当单据编号必须严格反映业务分类且格式固定时，利用SQL触发器在库内闭环处理是一种务实选择。只要控制好配置表的锁粒度并写好重置逻辑，就能在长期运行中稳定输出符合预期的单据编号。