PostgreSQL中如何生成带指定格式规则的自定义自增列？

通用实现思路 #

• 核心依赖自增序列/表自增主键作为数值来源，避免重复
• 对自增数值补前导零到4位，拼接固定前缀test生成最终字段值
• 通过触发器、计算列、默认值约束实现插入时自动赋值，无需手动指定该字段

各数据库具体实现 #

MySQL 实现 #

兼容所有MySQL版本，使用触发器实现自动赋值：

-- 1. 示例表结构（如果是存量表可跳过建表步骤）
CREATE TABLE IF NOT EXISTS dept_registration (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    dept_code VARCHAR(8) NOT NULL UNIQUE,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. 创建插入前触发器自动生成dept_code
DELIMITER //
CREATE TRIGGER trg_dept_code_gen
BEFORE INSERT ON dept_registration
FOR EACH ROW
BEGIN
    SET NEW.dept_code = CONCAT('test', LPAD(
        (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dept_registration'),
        4, '0'
    ));
END //
DELIMITER ;

测试验证：

-- 插入时无需指定dept_code
INSERT INTO dept_registration(dept_name) VALUES ('技术部'),('人事部'),('财务部');
-- 查询结果会自动生成test0001、test0002、test0003格式的dept_code
SELECT dept_code, dept_name FROM dept_registration;

PostgreSQL 实现 #

使用序列+默认值实现，更简洁高效：

-- 1. 创建自增序列
CREATE SEQUENCE dept_code_seq START WITH 1 INCREMENT BY 1;

-- 2. 建表时直接指定dept_code生成规则
CREATE TABLE dept_registration (
    id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    dept_code VARCHAR(8) NOT NULL UNIQUE DEFAULT CONCAT('test', LPAD(nextval('dept_code_seq')::TEXT, 4, '0')),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SQL Server 实现 #

使用持久化计算列实现，无需额外触发器：

CREATE TABLE dept_registration (
    id INT IDENTITY(1,1) PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    dept_code AS 'test' + RIGHT('0000' + CAST(id AS VARCHAR(4)), 4) PERSISTED UNIQUE,
    create_time DATETIME DEFAULT GETDATE()
);

注意事项 #

• 存量表改造前需要先按照规则补全现有数据的dept_code，避免触发唯一约束冲突
• 需要重置编号起始值时，对应修改自增序列/自增主键的起始值即可
• 并发插入场景下自增逻辑本身线程安全，不会出现重复编码问题

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