如何在MySQL中按分类限制单表记录数并保证并行插入一致性

可以在数据库层面实现该需求，且天然保证并行插入场景下的数据一致性，性能远优于应用层for update锁定读方案，以下是两种优先级从高到低的落地方案：

方案1：新增国家计数关联表+行级锁（性能最优，支持千级阈值场景） #

核心思路是新增一张独立的计数表维护每个国家的当前用户数，利用InnoDB的行级锁实现同国家插入的串行化，不同国家的插入完全并行无冲突。

实现步骤 #

1. 新建国家计数表，初始化已有国家的计数：

-- 建计数表
CREATE TABLE country_user_stat (
    country VARCHAR(10) PRIMARY KEY COMMENT '国家编码',
    current_user_count INT NOT NULL DEFAULT 0 COMMENT '当前用户数',
    max_limit INT NOT NULL DEFAULT 3 COMMENT '最大允许用户数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 初始化历史数据
INSERT INTO country_user_stat (country, current_user_count)
SELECT Country, COUNT(*) FROM Users GROUP BY Country;

2. 给Users表新增联合唯一约束，避免并发场景下的重复序号冲突：

ALTER TABLE Users ADD COLUMN country_user_seq INT NOT NULL COMMENT '当前国家下的用户序号';
ALTER TABLE Users ADD UNIQUE KEY uk_country_seq (Country, country_user_seq);

3. 创建插入前置触发器，自动校验阈值、更新计数：

DELIMITER //
CREATE TRIGGER trg_users_check_limit_before_insert
BEFORE INSERT ON Users FOR EACH ROW
BEGIN
    -- 原子更新计数，仅当当前计数小于阈值时更新成功
    UPDATE country_user_stat
    SET current_user_count = current_user_count + 1
    WHERE country = NEW.Country AND current_user_count < max_limit;
    
    -- 更新行数为0说明已达阈值，直接抛出错误终止插入
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '当前国家用户数已达上限，插入失败';
    END IF;
    
    -- 赋值当前用户在对应国家的序号，利用唯一约束兜底防并发异常
    SET NEW.country_user_seq = (SELECT current_user_count FROM country_user_stat WHERE country = NEW.Country);
END //
DELIMITER ;

优势 #

• 完全数据库层实现，业务代码无需任何改造
• 仅对相同国家的插入加行级锁，不同国家插入完全并行，性能损失极小
• 阈值调整仅需修改country_user_stat表的max_limit字段，无需修改触发器或表结构
• 阈值高达数千的场景也完全适用，无额外性能损耗

方案2：带条件的INSERT语句（轻量场景，无需额外表） #

如果不想新增表和触发器，可直接改造插入SQL，利用MySQL原生的原子操作保证一致性，适合并发量不高、阈值较小的场景：

INSERT INTO Users (UserName, Country)
SELECT '要插入的用户名', '要插入的国家'
FROM DUAL
WHERE (SELECT COUNT(*) FROM Users WHERE Country = '要插入的国家') < 3;

注意事项 #

• 数据库隔离级别需保持默认的可重复读（RR）级别，此时INSERT ... SELECT会自动对查询范围加间隙锁，避免并发插入导致的计数不准问题
• 阈值较大时count查询性能会下降，仅适合阈值在百级以内的场景
• 无需新增数据库对象，仅需业务侧调整插入SQL即可

常见误区：MySQL的CHECK约束为行级校验，无法实现跨行的计数统计，不能直接用于该需求。

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