如何自动生成带p-前缀的char类型SQL主键？

针对privateBooks表需要自动生成p-前缀加自增整数的主键需求，分几种主流数据库给出具体实现方式：

MySQL 实现方式 #

方法1：自增字段+触发器 #

1. 给表添加一个隐藏的自增整数列（比如seq_id），再设置业务主键id为char类型：

CREATE TABLE privateBooks (
    seq_id INT AUTO_INCREMENT PRIMARY KEY,
    id CHAR(10) NOT NULL UNIQUE,
    -- 其他业务字段...
);

2. 创建插入触发器，自动拼接生成带前缀的id：

DELIMITER //
CREATE TRIGGER generate_private_book_id BEFORE INSERT ON privateBooks
FOR EACH ROW
BEGIN
    SET NEW.id = CONCAT('p-', NEW.seq_id);
END //
DELIMITER ;

插入数据时无需指定id和seq_id，数据库会自动生成：

INSERT INTO privateBooks (其他业务字段) VALUES (...);

方法2：自定义函数+全局变量 #

如果不想额外加字段，可使用自定义函数配合全局变量（适合单数据库实例场景）：

SET @seq = 0;
CREATE FUNCTION get_private_book_id() RETURNS CHAR(10)
DETERMINISTIC
BEGIN
    SET @seq = @seq + 1;
    RETURN CONCAT('p-', @seq);
END;

创建表时将id的默认值设为函数返回值：

CREATE TABLE privateBooks (
    id CHAR(10) NOT NULL PRIMARY KEY DEFAULT get_private_book_id(),
    -- 其他业务字段...
);

PostgreSQL 实现方式 #

序列+默认值拼接 #

1. 先创建一个自增序列：

CREATE SEQUENCE private_book_seq START WITH 1 INCREMENT BY 1;

2. 创建表时，将id字段的默认值设为前缀拼接序列值：

CREATE TABLE privateBooks (
    id VARCHAR(10) PRIMARY KEY DEFAULT 'p-' || nextval('private_book_seq'),
    -- 其他业务字段...
);

插入数据时直接省略id字段即可自动生成：

INSERT INTO privateBooks (其他业务字段) VALUES (...);

注意事项 #

• 虽然你有业务需求用char类型做主键，但要明确：带前缀的char主键比整数主键占用更多存储空间，索引效率更低，涉及跨表关联时性能会受影响。建议内部关联用纯整数ID（比如MySQL方案里的seq_id），仅在API对外暴露时使用带前缀的格式。
• 触发器或序列生成的ID具备原子性，不用担心并发插入时的重复问题。

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