如何为企业维度的职位表实现SQL Scoped Auto Increment？

这个需求其实很常见，我来给你分享几个靠谱的方案，不管是数据库端直接解决还是服务端优化，都能避开你说的复杂逻辑：

数据库端原生/通用方案 #

1. 企业职位计数器表 + 原子事务（推荐） #

这是跨数据库通用的方案，完全由数据库保证原子性和唯一性，不用服务端处理复杂逻辑：

• 新建一张company_job_counters表，结构如下：

  CREATE TABLE company_job_counters (
      company_id INT PRIMARY KEY, -- 关联企业表的外键
      current_max_id INT DEFAULT 0 NOT NULL
  );
  

• 插入新职位时，在同一个事务中执行两步操作（可以合并成一条SQL简化）：
  • 对于MySQL：用INSERT ... ON DUPLICATE KEY UPDATE自动处理新企业/已有企业的情况，同时获取自增后的ID

    -- 先更新计数器，不存在则插入
    INSERT INTO company_job_counters (company_id, current_max_id)
    VALUES (?, 0)
    ON DUPLICATE KEY UPDATE current_max_id = current_max_id + 1;
    -- 获取更新后的ID
    SELECT current_max_id FROM company_job_counters WHERE company_id = ?;
    

  • 对于PostgreSQL：用INSERT ... ON CONFLICT DO UPDATE实现同样逻辑

    INSERT INTO company_job_counters (company_id, current_max_id)
    VALUES (?, 0)
    ON CONFLICT (company_id) DO UPDATE SET current_max_id = company_job_counters.current_max_id + 1
    RETURNING current_max_id;
    

  • 最后用返回的current_max_id作为该职位的企业内ID，插入职位表即可。

这个方案自动处理了所有边缘场景：新企业首次插入会自动初始化计数器，职位删除不影响后续ID生成（因为计数器只记录当前最大值，ID是标识而非连续序号，这符合大多数业务需求），并发插入时数据库会通过锁保证ID不重复。

2. 特定数据库原生特性 #

如果用的是特定数据库，还可以利用原生特性简化：

• SQL Server：可以用SEQUENCE结合触发器，或者用OUTPUT子句在更新计数器时直接获取新ID，逻辑和上面一致。
• Oracle：可以为每个企业创建单独的序列，但维护成本较高，不如计数器表方案灵活。

服务端优化方案（无需新增表） #

如果不想改动数据库结构，可以优化原有方案，降低逻辑复杂度：

• 原子查询+事务锁：避免先查询再更新的非原子操作，用事务加行锁保证并发安全：

  BEGIN TRANSACTION;
  -- 锁定该企业的职位记录，防止并发插入导致ID重复
  SELECT COALESCE(MAX(job_id), 0) INTO @max_job_id FROM jobs WHERE company_id = ? FOR UPDATE;
  -- 插入新职位，ID为最大值+1
  INSERT INTO jobs (company_id, job_id, ...) VALUES (?, @max_job_id + 1, ...);
  COMMIT;
  

  这里COALESCE自动处理了企业无历史职位的情况（返回0，加1后就是1），职位删除不影响MAX(job_id)，逻辑比你最初的方案简洁很多，且并发安全。

• 分布式缓存辅助（高并发场景）：如果并发量极高，可以用Redis等分布式缓存存储每个企业的当前最大职位ID，插入时先通过Redis的INCR命令原子性获取新ID，再异步同步到数据库。但这个方案有一定数据一致性风险，适合对一致性要求不严格的场景。

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