PostgreSQL中高效实现按用户唯一的id+tid复合键生成方法

有两张数据库表：

• 学生表（主键为id）：

id | name
---+------
 1 | aaron
 2 | bob

• 分数表（复合主键为id和tid），用来存储学生每次测试的成绩：

id | tid | score 
---| --- | -----  

说明：tid是学生个人的测试序号，不同学生的tid没有关联——比如id=1和id=2的tid=1，并不代表同一场测试。

目前tid有两种生成方式：

• 方式一：全局唯一自增，示例如下：

id | tid | score 
-- | --- | -----  
 1 |  1  | 99
 1 |  2  | 98
 2 |  3  | 97
 2 |  4  | 96

这种方式的问题在于，学生能通过tid的数值推测全校的测试总量，不符合需求。

• 方式二：按学生id分组自增，每个学生的tid从1开始依次递增，不同学生的tid可以重复，示例如下：

id | tid | score 
-- | --- | -----  
 1 |  1  | 99
 1 |  2  | 98
 2 |  1  | 97
 2 |  2  | 96  

需求：找高效简便的方法实现第二种tid生成逻辑，不想用不重复随机数，更倾向于紧凑的自增整数。

1. MySQL/MariaDB #

方法一：插入时直接计算

用INSERT ... SELECT语句，插入时自动取当前学生的最大tid加1：

INSERT INTO score (id, tid, score)
SELECT 2, COALESCE(MAX(tid), 0) + 1, 97
FROM score
WHERE id = 2;

如果怕并发插入导致tid重复，就配合事务加排他锁：

BEGIN;
SELECT MAX(tid) FROM score WHERE id = 2 FOR UPDATE;
INSERT INTO score (id, tid, score) VALUES (2, @max_tid + 1, 97);
COMMIT;

方法二：触发器自动生成

创建触发器，每次插入前自动计算并赋值tid，之后插入只需要传id和score：

DELIMITER //
CREATE TRIGGER set_tid_before_insert
BEFORE INSERT ON score
FOR EACH ROW
BEGIN
    SELECT COALESCE(MAX(tid), 0) + 1 INTO NEW.tid
    FROM score
    WHERE id = NEW.id;
END //
DELIMITER ;

插入示例：

INSERT INTO score (id, score) VALUES (2, 97);

2. PostgreSQL #

基础插入方式

同样用INSERT ... SELECT计算tid：

INSERT INTO score (id, tid, score)
SELECT 2, COALESCE(MAX(tid), 0) + 1, 97
FROM score
WHERE id = 2;

触发器实现

创建函数和触发器，自动生成tid：

CREATE OR REPLACE FUNCTION set_score_tid()
RETURNS TRIGGER AS $$
BEGIN
    SELECT COALESCE(MAX(tid), 0) + 1 INTO NEW.tid
    FROM score
    WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_score_tid
BEFORE INSERT ON score
FOR EACH ROW EXECUTE FUNCTION set_score_tid();

3. SQL Server #

基础插入方式

用INSERT ... SELECT结合ISNULL计算tid：

INSERT INTO score (id, tid, score)
SELECT 2, ISNULL(MAX(tid), 0) + 1, 97
FROM score
WHERE id = 2;

触发器实现

创建替代触发器处理tid生成：

CREATE TRIGGER trg_SetScoreTid
ON score
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO score (id, tid, score)
    SELECT 
        i.id,
        ISNULL(MAX(s.tid), 0) + 1,
        i.score
    FROM inserted i
    LEFT JOIN score s ON i.id = s.id
    GROUP BY i.id, i.score;
END;

注意点 #

• 高并发场景下，直接用MAX()+1可能出现tid重复，得结合事务、锁或者数据库原子操作来避免。
• 部分数据库支持分区序列（比如PostgreSQL），但触发器的方式兼容性更强，适合多数场景。

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