PostgreSQL实现各客户下项目ID独立自增的方法咨询

你需要实现客户维度独立递增的项目编号规则：

• 客户与项目为1:n一对多关系
• 每个客户名下的项目ID独立从101开始递增，不同客户下允许出现相同项目ID
• 最终通过客户ID.项目ID的组合唯一标识项目，对应关系示例：
  • 1001.101 -> 项目A
  • 1001.102 -> 项目B
  • 1002.101 -> 项目C
  • 1002.102 -> 项目D

当前实现的核心问题是project表的id字段使用全局共享的serial序列，所有客户的项目ID会全局连续累加，无法按客户维度独立计数。现有建表及初始化SQL如下：

CREATE TABLE customer 
(
    id serial PRIMARY KEY,
    company_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE project 
(
    customer_id INT NOT NULL,
    id serial,
    project_name VARCHAR(255) NOT NULL,

    FOREIGN KEY(customer_id) REFERENCES customer (id),
    PRIMARY KEY(customer_id, id)
);

SELECT setval('customer_id_seq', 1000);
SELECT setval('project_id_seq', 100);

注：原表设计的(customer_id, id)复合主键完全符合组合唯一标识的需求，不需要调整，只需要修改项目ID的生成逻辑即可。

方案1：计数表+行锁触发器（生产环境首选，并发安全、无重复） #

这个方案通过单独维护每个客户的项目计数，插入时自动加行锁，保证高并发场景下也不会生成重复ID，ID一旦生成永久固定，支持删除项目后保留编号缺口，符合绝大多数业务场景要求，实现步骤如下：

1. 移除原project表的全局自增序列依赖：

ALTER TABLE project ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE project_id_seq;

2. 创建客户项目计数表，记录每个客户当前已分配的最大项目ID，初始值设为100，首次分配+1后正好是起始值101：

CREATE TABLE customer_project_seq (
    customer_id INT PRIMARY KEY REFERENCES customer(id) ON DELETE CASCADE,
    current_project_id INT NOT NULL DEFAULT 100
);

3. 编写插入项目时自动生成客户维度独立ID的触发器函数：

CREATE OR REPLACE FUNCTION generate_customer_project_id()
RETURNS TRIGGER AS $$
BEGIN
    -- 新客户首次插入项目时自动初始化计数
    INSERT INTO customer_project_seq(customer_id)
    VALUES(NEW.customer_id)
    ON CONFLICT (customer_id) DO NOTHING;

    -- 对当前客户的计数行加行锁，更新计数并返回新的项目ID
    -- 行锁会保证同客户并发插入时ID不会重复
    UPDATE customer_project_seq
    SET current_project_id = current_project_id + 1
    WHERE customer_id = NEW.customer_id
    RETURNING current_project_id INTO NEW.id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

4. 给project表绑定前置插入触发器：

CREATE TRIGGER trg_project_id_generate
BEFORE INSERT ON project
FOR EACH ROW
EXECUTE FUNCTION generate_customer_project_id();

如果是有存量项目数据的老库，在绑定触发器前需要先初始化计数表，把每个客户现有最大项目ID同步到customer_project_seq中，避免新生成ID和存量数据冲突。

方案2：查询时动态计算编号（轻量无侵入，适合非固定编号场景） #

如果不需要项目ID永久固定、对编号连续性没有要求，可以不提前存储ID，查询时通过窗口函数按客户分组动态生成编号：

SELECT 
    customer_id,
    100 + ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS project_seq,
    project_name
FROM project;

这个方案不需要修改表结构和写入逻辑，但是缺点很明显：如果删除了客户下的中间项目，后续查询生成的编号会自动前移，无法保留历史编号，仅适合临时统计、不需要持久化编号的场景。

避坑提醒 #

不要采用「给每个客户单独创建一个数据库序列」的实现方式：当客户量级增长后会产生大量零散的序列对象，后续客户注销、序列清理的维护成本极高，可维护性和性能都很差。

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