MySQL如何编写触发器实现producer表producer_code自动拼接多表字段

你原有触发器存在两个核心错误：

• 未通过外键关联查询其他表的code字段：直接写country.code、region.code这类字段，MySQL无法定位到插入的producer记录关联的对应国家、区域等记录，需要根据插入时传入的外键ID（NEW.country_id、NEW.region_id等）关联查询对应表的code值。
• BEFORE INSERT触发器无法直接获取自增ID：触发BEFORE INSERT逻辑时，producer表的自增id还未生成，直接引用producer.id会得到空值，拼接后整体结果也会为空。

方案1：单条插入常用方案（非高并发场景） #

普通业务场景下直接使用该方案即可，无需额外更新操作：

delimiter //
create trigger codeproducer before insert on producer
for each row
begin
  -- 声明变量存储各关联表的code和即将生成的自增ID
  declare v_country_code varchar(45);
  declare v_region_code varchar(45);
  declare v_zone_code varchar(45);
  declare v_community_code varchar(45);
  declare v_next_id int;

  -- 根据外键查询各关联表的code
  select code into v_country_code from country where id = NEW.country_id;
  select code into v_region_code from region where id = NEW.region_id;
  select code into v_zone_code from zone where id = NEW.zone_id;
  select code into v_community_code from community where id = NEW.community_id;

  -- 查询当前producer表下一个自增ID
  select AUTO_INCREMENT into v_next_id 
  from information_schema.TABLES 
  where TABLE_SCHEMA = database() 
    and TABLE_NAME = 'producer';

  -- 拼接生成producer_code，如需固定长度自增ID可使用lpad(v_next_id, 4, '0')补零
  set NEW.producer_code = concat(v_country_code, v_region_code, v_zone_code, v_community_code, v_next_id);
end //
delimiter ;

方案2：高并发场景适配方案 #

如果存在高并发批量插入需求，可使用双触发器方案避免自增ID获取冲突：

1. 先创建BEFORE INSERT触发器拼接关联表code，占位自增ID部分：

delimiter //
create trigger codeproducer_before before insert on producer
for each row
begin
  declare v_country_code varchar(45);
  declare v_region_code varchar(45);
  declare v_zone_code varchar(45);
  declare v_community_code varchar(45);
  select code into v_country_code from country where id = NEW.country_id;
  select code into v_region_code from region where id = NEW.region_id;
  select code into v_zone_code from zone where id = NEW.zone_id;
  select code into v_community_code from community where id = NEW.community_id;
  set NEW.producer_code = concat(v_country_code, v_region_code, v_zone_code, v_community_code, 'TEMP_ID');
end //
delimiter ;

2. 再创建AFTER INSERT触发器替换占位内容为实际生成的ID：

delimiter //
create trigger codeproducer_after after insert on producer
for each row
begin
  update producer 
  set producer_code = replace(producer_code, 'TEMP_ID', NEW.id) 
  where id = NEW.id;
end //
delimiter ;

内容的提问来源于stack exchange，提问作者Suallo Kamagaté