MySQL多用户环境下如何避免条件递增发票号重复？

核心问题根源 #

你遇到的重复问题，本质是取号和插入操作不是原子性的——多用户同时读取同一个TYPE_ID的MAX(INVOICE_NO)，拿到相同值后各自加1插入，最终导致重复。下面是几种可靠的解决方法：

方法1：事务+排他查询锁 #

利用MySQL的事务和FOR UPDATE锁，确保同一时间只有一个事务能读取并更新对应TYPE_ID的发票号：

START TRANSACTION;
-- 锁定TYPE_ID=1的相关行，其他事务需等待当前事务提交后才能读取
SELECT MAX(INVOICE_NO) INTO @max_invoice FROM Table1 WHERE TYPE_ID=1 FOR UPDATE;
-- 处理无记录的初始情况（MAX返回NULL时设为0再加1）
SET @new_invoice = IFNULL(@max_invoice, 0) + 1;
INSERT INTO Table1 (TYPE_ID, TYPE_NAME, INVOICE_NO) VALUES (1, 'SALES', @new_invoice);
COMMIT;

注意：事务必须完整执行，若中途回滚，锁会释放，其他事务可以继续操作。

方法2：单独维护序列表（推荐） #

创建一个专门的序列表来存储每个TYPE_ID的最新发票号，利用ON DUPLICATE KEY UPDATE实现原子性递增：

-- 先创建序列表
CREATE TABLE TypeInvoiceSeq (
    TYPE_ID INT PRIMARY KEY,
    CURRENT_INVOICE_NO INT DEFAULT 0
);

-- 插入主表的完整事务逻辑
START TRANSACTION;
-- 若TYPE_ID不存在则插入初始值0，存在则直接递增
INSERT INTO TypeInvoiceSeq (TYPE_ID, CURRENT_INVOICE_NO) 
VALUES (1, 0) ON DUPLICATE KEY UPDATE CURRENT_INVOICE_NO = CURRENT_INVOICE_NO + 1;
-- 获取递增后的发票号
SELECT CURRENT_INVOICE_NO INTO @new_invoice FROM TypeInvoiceSeq WHERE TYPE_ID=1;
-- 插入主表
INSERT INTO Table1 (TYPE_ID, TYPE_NAME, INVOICE_NO) VALUES (1, 'SALES', @new_invoice);
COMMIT;

这种方法锁的粒度更小（仅锁定序列表的单行），比锁主表效率更高，适合高并发场景。

方法3：触发器自动生成发票号 #

把取号逻辑封装到触发器里，业务代码只需插入TYPE_ID和TYPE_NAME，无需手动处理发票号：

-- 先创建序列表（同方法2）
CREATE TABLE TypeInvoiceSeq (
    TYPE_ID INT PRIMARY KEY,
    CURRENT_INVOICE_NO INT DEFAULT 0
);

-- 创建BEFORE INSERT触发器
DELIMITER //
CREATE TRIGGER generate_invoice_no BEFORE INSERT ON Table1
FOR EACH ROW
BEGIN
    -- 原子性更新序列表
    INSERT INTO TypeInvoiceSeq (TYPE_ID, CURRENT_INVOICE_NO) 
    VALUES (NEW.TYPE_ID, 0) ON DUPLICATE KEY UPDATE CURRENT_INVOICE_NO = CURRENT_INVOICE_NO + 1;
    -- 将新发票号赋值给待插入的记录
    SELECT CURRENT_INVOICE_NO INTO NEW.INVOICE_NO FROM TypeInvoiceSeq WHERE TYPE_ID=NEW.TYPE_ID;
END //
DELIMITER ;

-- 插入时无需指定INVOICE_NO
INSERT INTO Table1 (TYPE_ID, TYPE_NAME) VALUES (1, 'SALES');

关键注意事项 #

• 确保MySQL事务隔离级别为默认的REPEATABLE READ，避免幻读导致的重复问题。
• 绝对不要在应用层做“读最大值+加1+插入”的逻辑，应用层多线程/进程无法保证操作的原子性，必然会出现重复。

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