MySQL中如何将字段设为前缀+自增字段的拼接值？

方案1：AFTER INSERT触发器（推荐） #

BEFORE INSERT无法获取批量插入时的正确自增ID，改用AFTER INSERT触发器，插入完成后直接用当前行的确定ID拼接前缀更新字段，完美兼容单条/批量插入。

操作步骤： #

1. 调整表结构（先移除NOT NULL约束，因为插入时字段暂为空）：

ALTER TABLE your_table MODIFY COLUMN reference VARCHAR(50) UNIQUE;

2. 创建触发器：

DELIMITER //
CREATE TRIGGER update_ref_after_insert
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
    UPDATE your_table
    SET reference = CONCAT('Prefix-', NEW.id)
    WHERE id = NEW.id;
END //
DELIMITER ;

3. 补全已有数据的reference字段（如果表中已有数据）：

UPDATE your_table SET reference = CONCAT('Prefix-', id);

4. 重新添加NOT NULL约束：

ALTER TABLE your_table MODIFY COLUMN reference VARCHAR(50) NOT NULL UNIQUE;

方案2：预生成ID手动插入（需应用层配合） #

如果应用可以控制插入流程，先锁定表获取下一个自增ID，手动构造reference后插入，避免并发冲突。

示例代码（带事务锁）：

START TRANSACTION;
-- 锁定表获取下一个自增ID
SELECT AUTO_INCREMENT INTO @next_id 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '你的数据库名' AND TABLE_NAME = 'your_table' FOR UPDATE;

-- 批量插入示例
INSERT INTO your_table (id, reference) 
VALUES 
    (@next_id, CONCAT('Prefix-', @next_id)),
    (@next_id+1, CONCAT('Prefix-', @next_id+1));
COMMIT;

注意：此方法需要应用层处理ID生成逻辑，适合对插入流程有完全控制权的场景。

方案3：视图替代物理字段（无需存储时使用） #

如果reference仅用于查询展示，不需要存储到物理表中，直接创建视图即可：

CREATE VIEW your_table_view AS
SELECT 
    id, 
    CONCAT('Prefix-', id) AS reference,
    -- 其他需要展示的字段
FROM your_table;

视图自动拼接前缀和ID，天然满足唯一、非空要求，无需维护物理字段。

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