MySQL多租户数据库中生成租户内连续参考编号的实现问询

嗨，我刚好处理过好几个类似的多租户编号需求，给你整理几个靠谱的方案，你可以根据自己的业务优先级（比如性能、编号连续性要求）来选：

方案1：独立序列表 + 事务控制（最稳妥，保证绝对连续） #

这是我最推荐的、能严格保证租户内编号连续唯一的方案，核心是用一张单独的表来跟踪每个租户每个业务表的当前编号值，通过事务来确保操作的原子性。

具体操作： #

1. 先创建序列记录表：

CREATE TABLE tenant_sequence (
    tenant_id INT NOT NULL,
    table_name VARCHAR(50) NOT NULL,
    current_value INT NOT NULL DEFAULT 0,
    PRIMARY KEY (tenant_id, table_name) -- 复合主键确保每个租户+表的唯一性
);

2. 每次生成新编号时，用事务包裹更新+插入操作：

START TRANSACTION;

-- 先递增对应租户和表的序列值（InnoDB会自动加行锁，防止并发冲突）
UPDATE tenant_sequence 
SET current_value = current_value + 1 
WHERE tenant_id = ? AND table_name = 'book';

-- 获取递增后的编号
SELECT current_value INTO @new_book_ref 
FROM tenant_sequence 
WHERE tenant_id = ? AND table_name = 'book';

-- 插入书籍记录
INSERT INTO book (tenant_id, book_ref, title, ...) 
VALUES (?, @new_book_ref, 'xxx', ...);

COMMIT;

如果你觉得上面的步骤有点繁琐，可以用538866简化：

SET @tenant_id = 1;
-- 第一次使用时自动插入初始值，之后直接递增
INSERT INTO tenant_sequence (tenant_id, table_name, current_value)
VALUES (@tenant_id, 'book', 0) ON DUPLICATE KEY UPDATE current_value = LAST_INSERT_ID(current_value + 1);

-- 直接拿到新编号
SELECT 538866 AS new_book_ref;

优缺点： #

• ✅ 优点：绝对保证租户内编号连续、无重复，哪怕出现事务回滚，只要操作没提交，序列值就不会递增，完全没有间隙；逻辑清晰，容易维护。
• ❌ 缺点：每次插入都要多一次数据库操作，性能略有损耗；依赖数据库事务，高并发场景下要注意行锁的影响（但InnoDB的行锁粒度很细，一般没问题）。

方案2：应用层+缓存生成（高性能，允许少量间隙） #

如果你的业务可以接受偶尔的编号间隙（比如因为插入失败、应用崩溃导致的编号跳过），这个方案性能会好很多，适合高并发场景。

具体操作： #

1. 用Redis这类缓存工具来存储每个租户的当前最大编号，键名可以设计成tenant:{tenant_id}:book:max_ref。
2. 应用启动时，先从数据库加载对应租户的最大book_ref到Redis（如果是新租户，初始值设为0）。
3. 每次生成编号时，直接调用Redis的原子递增命令：

# 伪代码示例（Python+Redis）
tenant_id = 1
cache_key = f"tenant:{tenant_id}:book:max_ref"
# 原子递增，避免并发冲突
new_book_ref = redis_client.incr(cache_key)
# 插入数据库
db.execute(
    "INSERT INTO book (tenant_id, book_ref, title) VALUES (%s, %s, %s)",
    (tenant_id, new_book_ref, "Python入门")
)

4. 定期把Redis中的值同步回数据库，或者在应用重启时重新从数据库加载，防止缓存宕机丢失数据。

优缺点： #

• ✅ 优点：性能极高，不需要数据库事务，完全规避了数据库锁的开销；适合租户数量多、插入频率高的场景。
• ❌ 缺点：如果插入数据库失败但Redis已经递增，会导致编号间隙；缓存宕机时如果没及时同步，可能会出现重复编号（所以一定要加数据库的唯一约束兜底）。

必加的兜底约束 #

不管用哪种方案，一定要给业务表的(tenant_id, book_ref)加唯一约束，防止因为任何异常情况导致的重复编号：

ALTER TABLE book ADD UNIQUE INDEX idx_tenant_book_ref (tenant_id, book_ref);

避坑提醒 #

千万别直接用MySQL的全局自增列作为book_ref——一来会跨租户重复，二来全局递增的编号不符合“租户内连续”的需求，完全不适用这种场景。

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