如何为表定义按PrjID重置的动态默认唯一值列

针对你需要在插入时自动生成按PrjID分组自增的Uniquevalue（格式为TBL00+序号，每组从1开始），且避免后续更新（防止百万级并发下的排他锁）的需求，核心是在插入阶段原子性地获取分组序号，以下是主流数据库的具体实现：

核心逻辑说明 #

SQL默认值函数是无状态的，无法根据当前插入行的PrjID获取对应分组的最大序号，因此必须通过触发器结合原子化操作实现，全程无需后续更新，避免锁冲突。

1. MySQL 实现 #

利用INSERT ... ON DUPLICATE KEY UPDATE的原子性来维护分组序号：

步骤1：创建辅助表存储每个PrjID的当前最大序号 #

CREATE TABLE PrjSeq (
    PrjID BIGINT PRIMARY KEY,
    CurrentSeq INT DEFAULT 1
);

步骤2：创建前置触发器自动生成Uniquevalue #

DELIMITER //
CREATE TRIGGER trg_TableA_UniqueValue
BEFORE INSERT ON TableA
FOR EACH ROW
BEGIN
    -- 原子初始化或递增当前PrjID的序号
    INSERT INTO PrjSeq (PrjID) VALUES (NEW.PrjID)
    ON DUPLICATE KEY UPDATE CurrentSeq = CurrentSeq + 1;
    
    -- 拼接生成最终的Uniquevalue
    SET NEW.Uniquevalue = CONCAT('TBL00', (SELECT CurrentSeq FROM PrjSeq WHERE PrjID = NEW.PrjID));
END //
DELIMITER ;

步骤3：创建TableA主表 #

CREATE TABLE TableA (
    PrjID BIGINT,
    RvId BIGINT,
    Uniquevalue VARCHAR(1000)
    -- 建议添加(PrjID, RvId)作为主键或唯一索引，保证数据唯一性
);

2. PostgreSQL 实现 #

利用PostgreSQL的序列特性，为每个PrjID创建独立序列，天然支持高并发：

步骤1：创建TableA主表 #

CREATE TABLE TableA (
    PrjID BIGINT,
    RvId BIGINT,
    Uniquevalue VARCHAR(1000)
);

步骤2：创建生成分组序号的函数 #

CREATE OR REPLACE FUNCTION generate_prj_seq(p_prj_id BIGINT)
RETURNS INT AS $$
DECLARE
    seq_name VARCHAR := 'prj_seq_' || p_prj_id;
    current_seq INT;
BEGIN
    -- 检查序列是否存在，不存在则创建（初始值为1）
    IF NOT EXISTS (SELECT 1 FROM pg_sequence WHERE seqrelid = seq_name::regclass) THEN
        EXECUTE 'CREATE SEQUENCE ' || seq_name || ' START WITH 1';
    END IF;
    -- 原子获取下一个序列值
    EXECUTE 'SELECT nextval(''' || seq_name || ''')' INTO current_seq;
    RETURN current_seq;
END;
$$ LANGUAGE plpgsql VOLATILE;

步骤3：创建前置触发器 #

CREATE TRIGGER trg_TableA_UniqueValue
BEFORE INSERT ON TableA
FOR EACH ROW
SET NEW.Uniquevalue = CONCAT('TBL00', generate_prj_seq(NEW.PrjID));

3. SQL Server 实现 #

利用MERGE语句的原子性维护分组序号，结合INSTEAD OF INSERT触发器处理插入逻辑：

步骤1：创建辅助表存储分组序号 #

CREATE TABLE PrjSeq (
    PrjID BIGINT PRIMARY KEY,
    CurrentSeq INT DEFAULT 1
);

步骤2：创建替代触发器 #

CREATE TRIGGER trg_TableA_UniqueValue
ON TableA
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 原子更新或初始化分组序号
    MERGE PrjSeq AS target
    USING (SELECT PrjID FROM inserted) AS source
    ON target.PrjID = source.PrjID
    WHEN MATCHED THEN
        UPDATE SET CurrentSeq = CurrentSeq + 1
    WHEN NOT MATCHED THEN
        INSERT (PrjID) VALUES (source.PrjID);
    
    -- 插入数据并生成Uniquevalue
    INSERT INTO TableA (PrjID, RvId, Uniquevalue)
    SELECT 
        i.PrjID,
        i.RvId,
        'TBL00' + CAST(p.CurrentSeq AS VARCHAR(10))
    FROM inserted i
    JOIN PrjSeq p ON i.PrjID = p.PrjID;
END;

步骤3：创建TableA主表 #

CREATE TABLE TableA (
    PrjID BIGINT,
    RvId BIGINT,
    Uniquevalue VARCHAR(1000)
);

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