在业务系统开发中，我们经常会遇到需要为数据表生成唯一格式化编码的需求，比如用户编号要求为USR00001、USR00002这样的格式，订单号要求为ORD20240501001这样的格式。这类编码通常包含固定的前缀和自增的数字部分，手动维护很容易出现重复或者断层的问题，通过MySQL触发器可以在数据插入时自动完成编码的生成，核心就是获取当前表中最大的ID值，再按照规则格式化拼接。
基础表结构设计
首先我们需要创建一个测试用的业务表，这里以用户信息表为例，表中包含自增主键id、用户编码user_code、用户名称user_name三个字段，其中user_code就是我们需要自动生成的格式化编码。
-- 创建用户表 CREATE TABLE `user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `user_code` varchar(20) NOT NULL COMMENT '用户编码，格式为USR+5位数字', `user_name` varchar(50) NOT NULL COMMENT '用户名称', PRIMARY KEY (`id`), UNIQUE KEY `uk_user_code` (`user_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
触发器实现逻辑
触发器的核心逻辑分为三步：第一步是判断插入的数据中user_code是否为空，如果为空才需要自动生成；第二步是查询当前表中id的最大值，在此基础上加1作为新的自增数字；第三步是按照预设的格式拼接编码，赋值给NEW.user_code。
创建自动生成编码的触发器
我们创建一个BEFORE INSERT类型的触发器，在插入数据之前完成编码的生成，具体代码如下：
-- 创建触发器，自动生成用户编码
DELIMITER //
CREATE TRIGGER `trg_user_info_before_insert`
BEFORE INSERT ON `user_info`
FOR EACH ROW
BEGIN
-- 声明变量存储当前最大id
DECLARE max_id INT DEFAULT 0;
-- 如果插入的用户编码为空，才自动生成
IF NEW.user_code IS NULL OR NEW.user_code = '' THEN
-- 查询当前表中最大的id值
SELECT IFNULL(MAX(`id`), 0) INTO max_id FROM `user_info`;
-- 最大id加1作为新的自增数字，格式化为5位数字，不足补0，拼接前缀USR
SET NEW.user_code = CONCAT('USR', LPAD(max_id + 1, 5, '0'));
END IF;
END //
DELIMITER ;
代码逻辑说明
DELIMITER //：修改语句结束符，因为触发器内部包含分号，避免和MySQL默认的分号冲突。DECLARE max_id INT DEFAULT 0：声明一个变量用来存储查询到的最大id值。IFNULL(MAX(`id`), 0)：如果表中还没有数据，MAX(id)会返回NULL，用IFNULL转换为0，这样第一个编码的数字部分就是1。LPAD(max_id + 1, 5, '0')：将自增后的数字格式化为5位，不足的部分用0在左侧补齐，比如1会变成00001。CONCAT('USR', LPAD(...))：拼接固定前缀和格式化后的数字，得到最终的编码。
功能测试验证
我们插入几条测试数据，不指定user_code字段，查看触发器是否自动生成正确的编码：
-- 插入测试数据，不指定user_code
INSERT INTO `user_info` (`user_name`) VALUES ('张三');
INSERT INTO `user_info` (`user_name`) VALUES ('李四');
INSERT INTO `user_info` (`user_name`) VALUES ('王五');
-- 查询插入后的数据
SELECT `id`, `user_code`, `user_name` FROM `user_info`;
执行查询后可以看到结果如下：
| id | user_code | user_name |
|---|---|---|
| 1 | USR00001 | 张三 |
| 2 | USR00002 | 李四 |
| 3 | USR00003 | 王五 |
注意事项
并发场景问题
上述实现方式在并发插入的场景下可能会出现编码重复的问题，因为多个事务同时查询最大id时可能会得到相同的结果。如果需要支持高并发场景，建议将编码生成的逻辑改为基于数据库的序列或者分布式ID生成方案，或者在表中增加一个单独的序列管理表，通过行锁来保证获取序列的原子性。
触发器使用限制
MySQL的触发器不支持在触发器中执行动态SQL，也不支持在触发器中修改其他表的数据（部分版本支持，但是不建议使用），如果编码规则需要依赖其他表的数据，上述方式可能需要调整。另外触发器的逻辑会增加插入操作的开销，如果表的数据插入频率非常高，需要评估性能影响。
编码前缀调整
如果需要调整编码的前缀或者数字部分的位数，只需要修改触发器中CONCAT的前缀参数和LPAD的位数参数即可，比如要生成8位数字的后缀，只需要把LPAD的第二个参数改为8，前缀改为ORD就可以生成ORD00000001这样的编码。
需要注意的是，如果表中存在手动插入的编码，只要编码符合格式，不会和自动生成的编码冲突，因为触发器只会在插入时user_code为空的情况下自动生成，手动指定的编码会直接保留。