MariaDB实现按项目自增的非主键issue_number并解决并发插入问题

针对MariaDB中需要按项目单独自增issue_number且解决并发插入问题的需求，以下是两种可靠的实现方式：

方案1：专用序列表+事务排他锁 #

通过创建独立的序列记录表，结合事务和行级锁保证并发环境下的编号唯一性：

第一步：创建序列记录表 #

CREATE TABLE IF NOT EXISTS ProjectIssueSequence (
    project_id INT PRIMARY KEY,
    current_number INT NOT NULL DEFAULT 0,
    FOREIGN KEY (project_id) REFERENCES Project(id) ON DELETE CASCADE
);

该表用于存储每个项目当前的最大issue_number，确保不同项目的编号序列独立维护。

第二步：并发安全的插入流程 #

每次插入Issue时，用事务包裹操作，通过FOR UPDATE锁定对应项目的序列行，避免并发冲突：

START TRANSACTION;

-- 锁定目标项目的序列记录，阻止其他并发请求修改
SELECT current_number INTO @next_num FROM ProjectIssueSequence WHERE project_id = 123 FOR UPDATE;

-- 若项目是首次创建Issue则初始化序列，否则自增编号
INSERT INTO ProjectIssueSequence (project_id, current_number)
VALUES (123, 1)
ON DUPLICATE KEY UPDATE current_number = current_number + 1;

-- 获取更新后的编号
SELECT current_number INTO @final_issue_num FROM ProjectIssueSequence WHERE project_id = 123;

-- 插入Issue记录
INSERT INTO Issue (project_id, issue_number, content)
VALUES (123, @final_issue_num, '用户反馈的问题内容');

COMMIT;

如果使用MariaDB 10.5及以上版本，可通过UPDATE ... RETURNING简化流程，减少一次查询：

START TRANSACTION;

INSERT INTO ProjectIssueSequence (project_id, current_number)
VALUES (123, 1)
ON DUPLICATE KEY UPDATE current_number = current_number + 1;

SELECT current_number INTO @final_issue_num FROM ProjectIssueSequence WHERE project_id = 123;

INSERT INTO Issue (project_id, issue_number, content) VALUES (123, @final_issue_num, '问题内容');

COMMIT;

方案2：触发器自动生成编号 #

把编号生成逻辑封装到触发器中，应用层无需关注编号细节，简化开发流程：

第一步：创建序列记录表（同方案1） #

CREATE TABLE IF NOT EXISTS ProjectIssueSequence (
    project_id INT PRIMARY KEY,
    current_number INT NOT NULL DEFAULT 0,
    FOREIGN KEY (project_id) REFERENCES Project(id) ON DELETE CASCADE
);

第二步：编写BEFORE INSERT触发器 #

DELIMITER //
CREATE TRIGGER generate_issue_number BEFORE INSERT ON Issue
FOR EACH ROW
BEGIN
    -- 自动更新序列并获取新编号
    INSERT INTO ProjectIssueSequence (project_id, current_number)
    VALUES (NEW.project_id, 1)
    ON DUPLICATE KEY UPDATE current_number = current_number + 1;
    
    -- 将生成的编号赋值给新插入的Issue记录
    SELECT current_number INTO NEW.issue_number FROM ProjectIssueSequence WHERE project_id = NEW.project_id;
END //
DELIMITER ;

第三步：应用层插入操作 #

此时应用层只需插入项目ID和问题内容，触发器会自动处理编号生成：

INSERT INTO Issue (project_id, content) VALUES (123, '新的问题描述');

关键说明 #

• 两种方案均通过行级锁（FOR UPDATE或触发器隐式事务锁）避免了并发场景下的编号重复问题，彻底解决了SELECT MAX(...) +1的数据过期问题。
• 若已有历史Issue数据，需先初始化ProjectIssueSequence表，将每个项目的current_number设置为该项目已存在的最大issue_number，避免编号冲突。

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