SQL表含双主键时，如何实现comment_id自动递增？

你需要的是针对每个post_id，让comment_id从1开始独立递增（同一帖子下评论ID不重复，不同帖子间可重复），结合你的表结构，这里分两种常用场景给出实现方案：

方案1：插入时通过子查询直接计算（适用于所有支持子查询的数据库） #

插入评论时，不需要手动指定comment_id，通过子查询获取对应帖子的最大评论ID，自动生成新的ID：

INSERT INTO Comment (comment_id, post_id, user_id, content, date)
VALUES (
    -- 如果该帖子暂无评论，COALESCE返回0，加1后得到初始ID 1
    COALESCE((SELECT MAX(comment_id) FROM Comment WHERE post_id = 1), 0) + 1,
    1, -- 目标帖子ID
    2, -- 评论用户ID
    "这是测试评论内容",
    CURRENT_TIMESTAMP
);

替换语句中的帖子ID、用户ID和评论内容即可直接使用。

方案2：用触发器自动赋值（更优雅，减少业务代码逻辑） #

如果你的数据库支持触发器（SQLite/MySQL/PostgreSQL等主流数据库都支持），可以创建触发器，在插入评论时自动计算并填充comment_id，业务代码只需传入其他字段即可。

SQLite 触发器实现 #

-- 创建触发器：当comment_id为NULL时，自动计算对应帖子的递增ID
CREATE TRIGGER trg_comment_autoincrement
BEFORE INSERT ON Comment
FOR EACH ROW
WHEN NEW.comment_id IS NULL
BEGIN
    SELECT COALESCE(MAX(comment_id), 0) + 1
    INTO NEW.comment_id
    FROM Comment
    WHERE post_id = NEW.post_id;
END;

插入时只需传入NULL或不指定comment_id：

INSERT INTO Comment (post_id, user_id, content, date)
VALUES (1, 2, "通过触发器生成ID的评论", CURRENT_TIMESTAMP);

MySQL 触发器实现 #

DELIMITER //
CREATE TRIGGER trg_comment_autoincrement
BEFORE INSERT ON Comment
FOR EACH ROW
BEGIN
    SET NEW.comment_id = (
        SELECT COALESCE(MAX(comment_id), 0) + 1
        FROM Comment
        WHERE post_id = NEW.post_id
    );
END //
DELIMITER ;

插入语句同样无需指定comment_id：

INSERT INTO Comment (post_id, user_id, content)
VALUES (1, 2, "MySQL触发器生成的评论");

注意事项 #

• 不要给comment_id加全局AUTOINCREMENT：SQLite的AUTOINCREMENT是全局唯一的，仅适用于单一主键字段；MySQL的AUTO_INCREMENT也是全局递增，均不符合你“按帖子递增”的需求。
• 高并发场景需注意冲突：如果同时有多个请求插入同一帖子的评论，可能出现ID重复问题，可结合数据库事务或锁机制（如SQLite的BEGIN EXCLUSIVE、MySQL的行锁）避免冲突。

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