MySQL如何创建不抛出错误即可阻止INSERT操作的Trigger

现有代码问题说明 #

你使用的SQLSTATE '01000'属于MySQL的警告级信号，仅会返回提示信息，不会中断INSERT操作的执行，因此重复数据仍会插入成功。如果直接使用错误级信号，则会触发整个事务回滚，不符合你的需求。

需求可实现，推荐两个可行方案： #

方案1：联合唯一约束+INSERT IGNORE（最优，适配你的重复校验场景） #

这是性能最高、逻辑最简洁的实现方式，不需要编写触发器：

1. 先给running_subtests表的running_test_id和subtest_id字段添加联合唯一约束

ALTER TABLE running_subtests ADD UNIQUE KEY uk_running_subtest (running_test_id, subtest_id);

2. 后续所有插入该表的语句统一使用INSERT IGNORE语法

INSERT IGNORE INTO running_subtests (running_test_id, subtest_id, ...其他字段) VALUES (...);

触发重复校验时，仅会返回警告，不会插入重复数据，也不会触发当前事务的整体回滚。

方案2：触发器+事务保存点（适合有复杂校验逻辑必须用触发器的场景） #

如果你的校验逻辑比简单去重更复杂，必须通过触发器实现，可以配合事务保存点实现局部回滚：

1. 触发器中校验不通过时抛出自定义错误级信号

BEGIN
    IF NEW.running_test_id in (
        select A.running_test_id
        From running_subtests A
        where (NEW.running_test_id = A.running_test_id and NEW.subtest_id = A.subtest_id)
    ) THEN
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate in running test and subtest choosen';
    END IF;
END

2. 执行插入操作前先设置事务保存点，捕获到触发器抛出的错误后仅回滚到保存点即可，不会影响整个事务

-- 执行插入前设置保存点
SAVEPOINT insert_single_row;
-- 执行插入语句，若触发触发器错误则捕获
INSERT INTO running_subtests (...) VALUES (...);
-- 捕获到指定错误时执行
ROLLBACK TO SAVEPOINT insert_single_row;

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