如何在MariaDB中生成带时间戳的自定义主键作为发票编号

原写法问题说明 #

你最初的写法完全不可行，存在两个核心问题：

1. CREATE TABLE 语法不支持直接嵌入动态子查询作为列的默认值，无法在表定义阶段完成动态主键的逻辑绑定
2. 直接用 COUNT(id)+1 的方式在高并发场景下会出现多个插入请求拿到相同序号的问题，触发主键冲突，也没有处理跨年份序号重置的逻辑

推荐实现方案 #

方案1：数据库触发器实现（无业务代码侵入） #

该方案不需要修改业务代码，所有编号生成逻辑由数据库自动完成，是最简便的实现方式。

步骤1：创建基础表结构

CREATE TABLE invoice (
  -- 可选添加自增ID作为物理主键，降低索引碎片，提升存储性能
  auto_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  -- 自定义发票编号作为业务唯一主键，也可以直接设置为PRIMARY KEY
  invoice_no VARCHAR(32) NOT NULL UNIQUE COMMENT '发票编号',
  company_id INT UNSIGNED NOT NULL COMMENT '企业ID',
  invoice_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开票日期',
  -- 其他业务字段省略
  INDEX idx_company_id(company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步骤2：创建插入前触发器生成编号

如果是低并发业务，直接用统计逻辑生成序号即可：

DELIMITER //
CREATE TRIGGER trg_invoice_gen_no BEFORE INSERT ON invoice
FOR EACH ROW
BEGIN
  DECLARE cur_year INT;
  DECLARE seq INT;
  -- 取插入记录对应的年份
  SET cur_year = YEAR(NEW.invoice_date);
  -- 统计当前企业当年已有发票数量，+1作为流水号
  SELECT IFNULL(COUNT(*), 0) + 1 INTO seq 
  FROM invoice 
  WHERE company_id = NEW.company_id AND YEAR(invoice_date) = cur_year;
  -- 格式1：生成 年份+4位流水号 示例：20240001
  SET NEW.invoice_no = CONCAT(cur_year, LPAD(seq, 4, '0'));
  -- 如需第二种格式 年份-企业ID-流水号 替换为下面的语句即可
  -- SET NEW.invoice_no = CONCAT(cur_year, '-', NEW.company_id, '-', seq);
END //
DELIMITER ;

高并发场景优化

如果业务插入并发较高，为了彻底避免序号重复，建议新增独立的序号管理表做原子计数：

-- 新建序号管理表
CREATE TABLE invoice_seq (
  company_id INT UNSIGNED NOT NULL,
  year INT NOT NULL,
  current_seq INT NOT NULL DEFAULT 0,
  PRIMARY KEY (company_id, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改触发器逻辑，用原子更新拿序号
DELIMITER //
CREATE TRIGGER trg_invoice_gen_no BEFORE INSERT ON invoice
FOR EACH ROW
BEGIN
  DECLARE cur_year INT;
  DECLARE seq INT;
  SET cur_year = YEAR(NEW.invoice_date);
  -- 原子更新序号，不存在则初始化
  INSERT INTO invoice_seq (company_id, year, current_seq)
  VALUES (NEW.company_id, cur_year, 1)
  ON DUPLICATE KEY UPDATE current_seq = current_seq + 1;
  -- 获取最新序号
  SELECT current_seq INTO seq FROM invoice_seq WHERE company_id = NEW.company_id AND year = cur_year;
  SET NEW.invoice_no = CONCAT(cur_year, LPAD(seq, 4, '0'));
END //
DELIMITER ;

方案2：业务代码层生成 #

如果不想在数据库维护触发器，可把上述逻辑迁移到业务代码中实现，分布式场景下注意加分布式锁避免序号重复即可。

其他注意事项 #

• 流水号位数可自行调整，如需6位流水号把LPAD(seq, 4, '0')的第二个参数改为6即可
• 使用序号表的方案不会因为发票删除复用旧序号，符合财务单据的编号规范，更推荐使用
• 如要把自定义编号作为物理主键，把invoice_no的UNIQUE改为PRIMARY KEY即可

内容的提问来源于stack exchange，提问作者AndaluZ