MySQL如何生成带前缀的自定义自增主键ID（如P001格式）

你可以根据业务场景选择以下两种常用方案，均原生适配MySQL环境：

方案一：基于现有自增ID的生成列方案（推荐，适配MySQL 5.7及以上版本） #

该方案保留原表的自增ID主键，新增自动计算的自定义编码字段，无并发风险、无需手动维护编码，性能最优。

操作步骤 #

1. 给现有product表新增自定义编码字段，设置为存储生成列，数据库会自动根据自增ID生成对应格式的编码：

ALTER TABLE `product`
ADD COLUMN `product_code` VARCHAR(10) 
GENERATED ALWAYS AS (CONCAT('P', LPAD(`id`, 3, '0'))) STORED UNIQUE NOT NULL;

2. 插入数据时无需指定product_code字段，数据库会自动生成：

-- 插入测试数据
INSERT INTO `product` (`name`) VALUES ('笔记本电脑'),('无线耳机'),('智能手表');
-- 查询验证
SELECT * FROM `product`;

查询结果会自动对应生成P001、P002、P003的编码。

格式调整说明 #

如果需要调整编码规则，直接修改生成规则即可：

• 前缀修改：把CONCAT里的'P'换成其他前缀即可
• 位数调整：把LPAD里的第二个参数3改成你需要的位数，比如要生成P00001就改成5
• 不需要补前导零的话，直接去掉LPAD函数，写成CONCAT('P', id)即可

方案二：触发器实现自定义编码作为主键 #

如果需要把自定义编码直接作为主键，不需要保留原自增ID，可以用触发器实现：

操作步骤 #

1. 调整表结构，把主键改为自定义编码字段：

CREATE TABLE `product` (
  `product_code` VARCHAR(10) PRIMARY KEY,
  `name` varchar(45) DEFAULT NULL
);

2. 创建插入前触发器，自动计算下一个编码：

DELIMITER //
CREATE TRIGGER `trg_product_generate_code` BEFORE INSERT ON `product`
FOR EACH ROW
BEGIN
  DECLARE next_serial INT;
  -- 提取已有编码的最大序号，没有数据则从1开始
  SELECT IFNULL(MAX(CAST(SUBSTRING(`product_code`, 2) AS UNSIGNED)), 0) + 1 
  INTO next_serial FROM `product`;
  -- 生成指定格式的编码
  SET NEW.product_code = CONCAT('P', LPAD(next_serial, 3, '0'));
END //
DELIMITER ;

注意事项 #

该方案在超高并发插入场景下，极小概率会出现序号重复问题，适合并发量不高的业务场景使用，高并发场景建议搭配分布式锁或使用方案一。

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