MySQL使用SIGNAL SQLSTATE拦截插入触发回滚 如何同时写入其他表

你遇到的是MySQL事务原子性的特性导致的问题：BEFORE INSERT触发器和触发它的INSERT语句属于同一个事务，SIGNAL触发的异常会导致整个事务回滚，所以日志表的插入操作也会被同步撤销。另外你提供的触发器代码存在语法错误，判断条件中example.price应该改为NEW.price，否则执行时会报错。

方案1：修改日志表为非事务型存储引擎 #

适用场景：日志表不需要事务、外键支持

• MyISAM、MEMORY等非事务型存储引擎的写入操作执行后就会持久化，不会被事务回滚影响，是成本最低的改造方案。
• 操作步骤：
  1. 执行SQL修改日志表引擎：

  ALTER TABLE example_log ENGINE = MyISAM;
  

  2. 修正后的触发器代码如下：

  delimiter $$
  create trigger tr_example_before_ins before insert on example
  for each row
  begin
      if NEW.price < 10000 then
          insert into example_log(example_id, notes) values(NEW.example_id, "WORKED");
          signal sqlstate '45000' set message_text = '价格低于10000，插入被拒绝';
      end if;
  end$$
  delimiter ;
  

方案2：用存储过程封装插入逻辑（更推荐） #

适用场景：希望统一控制写入逻辑、避免触发器隐式副作用

• 完全放弃使用触发器做校验拦截，把所有逻辑放到存储过程中实现：先做参数校验，校验不通过直接写入日志后返回，不执行原表的插入操作，从根源上避免回滚问题。
• 示例代码：

delimiter $$
create procedure sp_insert_example(
    in p_example_id INT,
    in p_price DECIMAL(10,2),
    -- 其余example表需要的字段依次补充声明
    out p_result TINYINT COMMENT '返回执行结果：1=成功 0=校验失败'
)
begin
    set p_result = 0;
    -- 优先做参数校验
    if p_price < 10000 then
        insert into example_log(example_id, notes) values(p_example_id, "WORKED");
        return;
    end if;
    -- 校验通过后执行原表插入
    insert into example(example_id, price /* 其余字段 */) values(p_example_id, p_price /* 其余字段值 */);
    set p_result = 1;
end$$
delimiter ;

• 调用方式：

-- 插入价格低于10000的测试数据
call sp_insert_example(1001, 8000, @exec_res);
-- 查看执行结果
select @exec_res;

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