如何在MySQL中设置触发器，阻止插入同时记录插入值？

核心问题是MySQL触发器与主操作共享同一事务，执行SIGNAL会触发整个事务回滚，包括触发器内的日志插入操作。以下是三种可行的解决思路：

1. 用存储过程拆分事务（推荐） #

通过存储过程将日志插入与主插入拆分到独立事务中，避免日志被连带回滚：

DELIMITER //
CREATE PROCEDURE insert_with_validation(IN p_col1 INT, IN p_col2 VARCHAR(255))
BEGIN
    -- 独立事务插入日志，不受后续主事务影响
    SET autocommit = 1;
    INSERT INTO log_table(col1, col2) VALUES(p_col1, p_col2);
    
    -- 开启主事务执行插入与校验
    START TRANSACTION;
    INSERT INTO target_table(col1, col2) VALUES(p_col1, p_col2);
    
    -- 触发校验条件，不满足则回滚主插入
    IF p_col1 < 10 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END //
DELIMITER ;

优点：无需额外依赖，安全可靠；缺点：需要将原插入逻辑替换为调用存储过程。

2. 借助UDF执行外部独立事务 #

安装lib_mysqludf_sys插件后，在触发器中调用系统命令执行日志插入，该操作处于独立事务，不受主事务回滚影响：

DELIMITER //
CREATE TRIGGER target_insert_trigger BEFORE INSERT ON target_table
FOR EACH ROW
BEGIN
    -- 拼接日志插入语句
    SET @log_sql = CONCAT('INSERT INTO log_table(col1, col2) VALUES(', NEW.col1, ', ''', NEW.col2, ''');');
    -- 调用mysql客户端执行语句，跳过事务绑定
    SET @exec_cmd = CONCAT('mysql -u your_user -pyour_pass your_db -e "', @log_sql, '" --skip-start-transaction');
    -- 执行系统命令
    SELECT sys_exec(@exec_cmd);
    
    -- 触发主事务回滚
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '校验不通过，回滚插入';
END //
DELIMITER ;

优点：无需修改应用层逻辑；缺点：需要安装第三方UDF，存在安全风险，需严格控制权限。

3. 文件+事件调度异步记录 #

触发器将待记录数据写入文件，再通过事件调度器定期读取文件插入日志表：

-- 触发器写入临时文件（MySQL 8.0.19+支持APPEND）
DELIMITER //
CREATE TRIGGER target_insert_trigger BEFORE INSERT ON target_table
FOR EACH ROW
BEGIN
    SELECT CONCAT(NEW.col1, ',', NEW.col2, '\n') INTO OUTFILE '/tmp/log_queue.txt'
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' APPEND;
    
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '校验不通过，回滚插入';
END //
DELIMITER ;

-- 开启事件调度器并创建同步任务
SET GLOBAL event_scheduler = ON;
DELIMITER //
CREATE EVENT log_sync_task
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    LOAD DATA INFILE '/tmp/log_queue.txt' INTO TABLE log_table
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
    -- 清空临时文件（需确保MySQL有文件权限）
    SELECT sys_exec('echo "" > /tmp/log_queue.txt');
END //
DELIMITER ;

优点：无需UDF依赖；缺点：日志记录有延迟，需处理文件并发写入问题。

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