如何在MySQL生成按年月分组自增的唯一佛历发票ID

由于MySQL的存储生成列仅支持确定性表达式（相同输入必须返回固定结果），无法引用其他行数据计算分组自增序号，因此无法直接通过原方案实现需求。推荐使用触发器在插入数据时自动生成符合要求的发票编号。

步骤1：调整表结构 #

新增自增主键id（避免主键冲突），将invoice_no设为普通唯一约束字段：

CREATE TABLE `tbl_0_invoices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `invoice_date` DATE NOT NULL,
  `invoice_no` VARCHAR(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci UNIQUE NOT NULL
);

步骤2：创建插入触发器 #

触发器会在插入数据时自动转换佛历年月，并获取当前年月下的最大序号，生成完整发票编号：

DELIMITER //
CREATE TRIGGER generate_invoice_no BEFORE INSERT ON tbl_0_invoices
FOR EACH ROW
BEGIN
    DECLARE buddhist_ym VARCHAR(4);
    DECLARE current_max_seq INT;
    
    -- 转换为佛历年月：公历年份后两位+43，拼接月份
    SET buddhist_ym = CONCAT(DATE_FORMAT(NEW.invoice_date, '%y') + 43, DATE_FORMAT(NEW.invoice_date, '%m'));
    
    -- 获取当前佛历年月下已有的最大序号，无数据则设为0
    SELECT COALESCE(MAX(CAST(SUBSTRING(invoice_no, 8) AS UNSIGNED)), 0) 
    INTO current_max_seq
    FROM tbl_0_invoices
    WHERE invoice_no LIKE CONCAT('INV', buddhist_ym, '-%');
    
    -- 生成最终发票编号
    SET NEW.invoice_no = CONCAT('INV', buddhist_ym, '-', current_max_seq + 1);
END //
DELIMITER ;

步骤3：测试插入数据 #

插入示例数据：

INSERT INTO tbl_0_invoices (invoice_date) VALUES 
('2024-08-21'),
('2024-09-13'),
('2024-09-13'),
('2024-09-13'),
('2024-09-13'),
('2024-09-18'),
('2024-10-25'),
('2024-10-25'),
('2024-10-25'),
('2024-10-25'),
('2025-01-24');

查询结果：

关键说明 #

• 存储生成列无法实现分组自增：因为生成列的表达式必须独立于其他行数据，无法动态计算同组的序号。
• 触发器的优势：插入时自动计算，保证编号的唯一性和连续性，完全匹配业务需求。

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