基于SQL和PHP实现分结构年度重置的自动发票编号方案咨询

针对你提出的发票编号需求，下面给出两种可行的解决方案，同时解决你提到的自增字段适配问题和MAX查询的并发风险：

一、通用方案：专用编号控制表（适配所有SQL数据库） #

这个方案通过一张专门的表来记录每个业务、每年的当前编号，结合事务的原子更新来避免并发冲突和断号。

1. 创建编号控制表 #

首先创建用于存储编号序列的表，用业务标识和年份作为联合主键，确保每个业务+年度的编号独立：

CREATE TABLE invoice_seq (
    business_code VARCHAR(10) NOT NULL, -- 业务标识：ENG/FR
    year INT NOT NULL, -- 年份：如2022、2023
    current_num INT NOT NULL DEFAULT 0, -- 当前已用的最大编号
    PRIMARY KEY (business_code, year)
);

2. 生成发票编号的SQL逻辑 #

使用事务包裹更新和查询操作，保证每次只有一个请求能获取到下一个编号，彻底避免并发重复：

-- 开启事务
START TRANSACTION;

-- 若该业务+年份的记录不存在，则初始化编号为1；否则将当前编号+1
INSERT INTO invoice_seq (business_code, year, current_num)
VALUES ('ENG', YEAR(NOW()), 1)
ON DUPLICATE KEY UPDATE current_num = current_num + 1;

-- 查询并生成最终的发票编号
SELECT CONCAT(business_code, '_', year, '_', current_num) AS invoice_number
FROM invoice_seq
WHERE business_code = 'ENG' AND year = YEAR(NOW());

-- 提交事务
COMMIT;

方案优势 #

• 支持多业务：通过business_code区分ENG/FR等不同业务结构
• 无断号风险：原子更新保证编号连续递增，只要业务上不物理删除已生成编号的发票（建议用status字段标记作废，而非删除），就不会出现编号缺失
• 年度自动重置：每年会自动生成新的业务+年份记录，编号从1开始计数

二、针对支持序列的数据库（如PostgreSQL、Oracle） #

如果你的数据库支持序列对象，可以用动态序列的方式实现，适合不需要兼容多数据库的场景。

以PostgreSQL为例，创建一个生成编号的函数：

CREATE OR REPLACE FUNCTION get_invoice_number(p_business_code VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    v_year INT := EXTRACT(YEAR FROM CURRENT_DATE)::INT;
    v_seq_name VARCHAR := 'seq_' || p_business_code || '_' || v_year;
    v_next_num INT;
BEGIN
    -- 检查对应业务+年份的序列是否存在，不存在则创建
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = v_seq_name AND relkind = 'S') THEN
        EXECUTE 'CREATE SEQUENCE ' || v_seq_name || ' START 1';
    END IF;
    -- 获取序列的下一个值
    EXECUTE 'SELECT nextval(''' || v_seq_name || ''')' INTO v_next_num;
    -- 拼接生成最终发票编号
    RETURN CONCAT(p_business_code, '_', v_year, '_', v_next_num);
END;
$$ LANGUAGE plpgsql;

调用函数获取编号：

SELECT get_invoice_number('ENG'); -- 输出类似ENG_2023_1

方案说明 #

• 每年会自动创建对应业务的新序列，实现年度重置
• 序列本身是原子性的，不会出现并发重复编号的问题
• 缺点是不同数据库的序列语法差异大，且动态创建序列需要较高权限

关于你提到的两种思路的问题 #

1. 自增字段：单表自增确实无法按业务和年份拆分，除非为每个业务建单独的表，显然不现实；上述方案相当于把自增逻辑按业务+年份拆分到了专用表中。
2. MAX查询生成：直接用MAX(invoice_number)拆分后加1的方式，在并发场景下会导致多个请求拿到相同的MAX值，生成重复编号；同时如果有发票被物理删除，会出现编号断号的情况，因此不建议使用。

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