同一表内实现两种不同类型ID独立自增的可行性及实现方法

这个需求完全可行！核心思路就是给两种类型的项目分别维护独立的自增序列，插入时根据项目类型自动填充对应ID字段、置零另一个字段。下面我针对MySQL、PostgreSQL这两种最常用的数据库，给出具体的实操方案：

1. MySQL 实现方案 #

方法一：触发器+序列表（持久化自增计数） #

MySQL没有原生序列对象，但我们可以用触发器配合单独的序列表来实现持久化的自增逻辑，即使数据库重启也不会丢失计数：

第一步：创建主数据表

CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 全局唯一的主ID
    idtype1 INT DEFAULT 0,
    idtype2 INT DEFAULT 0,
    project_name VARCHAR(255) NOT NULL,
    project_type ENUM('TYPE1', 'TYPE2') NOT NULL -- 必须加这个字段标记项目类型，触发器靠它判断逻辑
);

第二步：创建序列维护表

专门用来存两种类型ID的当前自增值，保证重启后数据不丢：

CREATE TABLE project_sequences (
    seq_type ENUM('TYPE1', 'TYPE2') PRIMARY KEY,
    current_value INT DEFAULT 0
);
-- 初始化两个序列的起始值
INSERT INTO project_sequences (seq_type, current_value) VALUES ('TYPE1', 0), ('TYPE2', 0);

第三步：编写BEFORE INSERT触发器

触发器会在插入数据前自动处理ID的赋值逻辑：

DELIMITER //
CREATE TRIGGER trg_projects_before_insert
BEFORE INSERT ON projects
FOR EACH ROW
BEGIN
    IF NEW.project_type = 'TYPE1' THEN
        -- 先更新TYPE1的序列值，再把新值赋给idtype1
        UPDATE project_sequences SET current_value = current_value + 1 WHERE seq_type = 'TYPE1';
        SELECT current_value INTO NEW.idtype1 FROM project_sequences WHERE seq_type = 'TYPE1';
        SET NEW.idtype2 = 0; -- 另一个ID置0
    ELSEIF NEW.project_type = 'TYPE2' THEN
        -- 同理处理TYPE2的逻辑
        UPDATE project_sequences SET current_value = current_value + 1 WHERE seq_type = 'TYPE2';
        SELECT current_value INTO NEW.idtype2 FROM project_sequences WHERE seq_type = 'TYPE2';
        SET NEW.idtype1 = 0;
    END IF;
END //
DELIMITER ;

测试插入

现在插入数据就完全符合你的需求了：

-- 插入TYPE1类型项目
INSERT INTO projects (project_name, project_type) VALUES ('name1', 'TYPE1');
INSERT INTO projects (project_name, project_type) VALUES ('name2', 'TYPE1');
-- 插入TYPE2类型项目
INSERT INTO projects (project_name, project_type) VALUES ('name3', 'TYPE2');
INSERT INTO projects (project_name, project_type) VALUES ('name4', 'TYPE2');

方法二：触发器+用户变量（简化版，适合测试或单实例） #

如果不需要持久化自增计数（比如数据库重启后自增值可以重置），可以用用户变量代替序列表，写法更简单：

DELIMITER //
CREATE TRIGGER trg_projects_before_insert_simple
BEFORE INSERT ON projects
FOR EACH ROW
BEGIN
    IF NEW.project_type = 'TYPE1' THEN
        SET @type1_seq = COALESCE(@type1_seq, 0) + 1;
        SET NEW.idtype1 = @type1_seq;
        SET NEW.idtype2 = 0;
    ELSE
        SET @type2_seq = COALESCE(@type2_seq, 0) + 1;
        SET NEW.idtype2 = @type2_seq;
        SET NEW.idtype1 = 0;
    END IF;
END //
DELIMITER ;

⚠️ 注意：这种方法的用户变量会在MySQL重启后清零，所以生产环境更推荐第一种方案。

2. PostgreSQL 实现方案 #

PostgreSQL有原生的SEQUENCE对象，实现起来更简洁优雅：

第一步：创建主数据表

CREATE TABLE projects (
    id SERIAL PRIMARY KEY, -- 全局自增主ID
    idtype1 INT DEFAULT 0,
    idtype2 INT DEFAULT 0,
    project_name VARCHAR(255) NOT NULL,
    project_type VARCHAR(10) CHECK (project_type IN ('TYPE1', 'TYPE2')) NOT NULL
);

第二步：创建两个独立序列

直接用原生序列来维护两种类型的自增值：

CREATE SEQUENCE seq_type1 START 1;
CREATE SEQUENCE seq_type2 START 1;

第三步：编写触发器函数和触发器

CREATE OR REPLACE FUNCTION trg_projects_before_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.project_type = 'TYPE1' THEN
        NEW.idtype1 := nextval('seq_type1'); -- 获取TYPE1序列的下一个值
        NEW.idtype2 := 0;
    ELSIF NEW.project_type = 'TYPE2' THEN
        NEW.idtype2 := nextval('seq_type2'); -- 获取TYPE2序列的下一个值
        NEW.idtype1 := 0;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_projects_before_insert
BEFORE INSERT ON projects
FOR EACH ROW EXECUTE FUNCTION trg_projects_before_insert();

测试插入

同样执行插入语句就能得到你想要的结果：

INSERT INTO projects (project_name, project_type) VALUES ('name1', 'TYPE1');
INSERT INTO projects (project_name, project_type) VALUES ('name2', 'TYPE1');
INSERT INTO projects (project_name, project_type) VALUES ('name3', 'TYPE2');
INSERT INTO projects (project_name, project_type) VALUES ('name4', 'TYPE2');

通用注意事项 #

• 必须添加project_type字段：这是触发器判断逻辑的核心，没有它就没法区分该触发哪个自增规则；
• 并发场景验证：如果是高并发插入，MySQL的序列表方案要注意锁的问题（其实UPDATE语句本身会加行锁，不会有冲突），PostgreSQL的序列本身就是线程安全的，不用担心重复；
• 分布式场景：如果是分布式数据库，本地序列可能无法保证全局唯一，这时候建议用分布式ID生成器（比如雪花算法）来替代本地序列。

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