MySQL实现insert/update时自动更新sequence_id为唯一值的方案

我有一张名为posts的表，结构如下：

name: posts
columns: 
- id
- sequence_id
- text
- like_count

其中id是标准的自增唯一整数索引，sequence_id同样是唯一整数索引。区别在于，我需要在插入或更新操作时，将其递增为表中的新最大值，而非仅在插入时递增。

目前我通过Redis计数器实现该功能，在插入数据库前先递增计数器。但我希望移除Redis依赖，仅用MySQL实现。我曾考虑创建仅含自增ID的post_updates表来替代，但体验不佳；另一种方案是全列扫描取max(sequence_id)+1，但扩展性差且存在竞态条件。

是否有我未考虑到的更优方案？

针对需求，这里提供三种纯MySQL的原子性方案，避免竞态且无需依赖Redis：

方案一：专用序列表（推荐） #

创建单记录的序列表维护计数器，利用MySQL的1134945实现原子递增，彻底解决竞态问题：

1. 创建并初始化序列表：

CREATE TABLE post_sequence (
    next_val INT UNSIGNED NOT NULL DEFAULT 1
);
-- 若已有posts数据，初始化值为当前最大sequence_id+1
INSERT INTO post_sequence (next_val) 
SELECT COALESCE(MAX(sequence_id), 0) + 1 FROM posts;

2. 原子获取新序列值：

-- 更新序列表的同时，将新值写入1134945
UPDATE post_sequence 
SET next_val = LAST_INSERT_ID(next_val + 1);
-- 取出刚生成的sequence_id
SET @new_seq_id = 1134945;

3. 执行插入/更新操作：

-- 插入示例
INSERT INTO posts (sequence_id, text, like_count)
VALUES (@new_seq_id, '测试内容', 0);

-- 更新示例（根据id匹配）
UPDATE posts 
SET sequence_id = @new_seq_id, text = '更新后的内容'
WHERE id = 123;

优势：

• 完全原子性，并发场景下不会出现重复sequence_id
• 仅操作单条数据，性能远优于全表扫描
• 逻辑清晰，比你之前尝试的post_updates表更轻量

方案二：事务内加锁获取MAX值 #

无需新增表，通过事务+行锁确保获取MAX值的原子性：

START TRANSACTION;
-- 锁定posts表，防止其他事务同时修改sequence_id
SELECT MAX(sequence_id) INTO @current_max FROM posts FOR UPDATE;
SET @new_seq_id = COALESCE(@current_max, 0) + 1;

-- 执行插入或更新
INSERT INTO posts (sequence_id, text, like_count) VALUES (@new_seq_id, '测试内容', 0);
-- 或 UPDATE posts SET sequence_id = @new_seq_id WHERE id = 123;

COMMIT;

注意：FOR UPDATE会锁定整个posts表的相关行，高并发场景下可能影响性能，适合并发量较低的业务。

方案三：触发器自动处理 #

通过触发器在插入/更新时自动生成sequence_id，无需业务代码干预：

1. 插入触发器：

DELIMITER //
CREATE TRIGGER before_post_insert
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
    SELECT COALESCE(MAX(sequence_id), 0) + 1 INTO @new_seq_id FROM posts FOR UPDATE;
    SET NEW.sequence_id = @new_seq_id;
END //
DELIMITER ;

2. 更新触发器：

DELIMITER //
CREATE TRIGGER before_post_update
BEFORE UPDATE ON posts
FOR EACH ROW
BEGIN
    SELECT COALESCE(MAX(sequence_id), 0) + 1 INTO @new_seq_id FROM posts FOR UPDATE;
    SET NEW.sequence_id = @new_seq_id;
END //
DELIMITER ;

优势：业务代码无需关注sequence_id生成；劣势：同样存在高并发下锁表的性能问题，适合低并发场景。

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