17370845950

在 MySQL 中，触发器（Trigger）可以在插入、更新或删除数据时自动执行一段预定义的逻辑，常用于数据验证。通过触发器，可以确保写入的数据符合业务规则，防止非法或不合规的数据进入数据库。

什么是触发器数据验证

触发器数据验证是指在 INSERT 或 UPDATE 操作发生前，检查即将写入的数据是否满足特定条件。如果不满足，可以通过 SIGNAL 语句抛出错误，阻止操作完成。

使用 BEFORE 触发器进行数据校验

数据验证通常使用 BEFORE INSERT 或 BEFORE UPDATE 触发器，因为在数据真正写入之前就可以拦截非法值。

示例：假设有一个员工表 employees，要求员工年龄必须在 18 到 65 之间：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT
);

创建一个 BEFORE INSERT 触发器来验证年龄：

DELIMITER $$

CREATE TRIGGER validate_employee_age_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.age < 18 OR NEW.age > 65 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '员工年龄必须在 18 到 65 岁之间';
    END IF;
END$$

DELIMITER ;

同样地，为 UPDATE 操作创建触发器以保持一致性：

DELIMITER $$

CREATE TRIGGER validate_employee_age_before_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.age < 18 OR NEW.age > 65 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '员工年龄必须在 18 到 65 岁之间';
    END IF;
END$$

DELIMITER ;

常见验证场景与技巧

• 非空但有条件限制的字段：比如邮箱必须包含 '@'，可用 IF LOCATE('@', NEW.email) = 0 判断并报错。
• 字段间逻辑关系：例如结束时间不能早于开始时间，可在触发器中比较两个 datetime 字段。
• 状态流转控制：订单状态只能从 'pending' → 'shipped'，不能倒退，可用 CASE 或 IF 判断 OLD.status 和 NEW.status。
• 避免无限递归：MySQL 不支持递归触发器（默认 log_bin_trust_function_creators=0），但仍需注意不要在触发器中修改自身表，否则可能报错。

注意事项

触发器虽然强大，但不宜过度使用：

• 错误提示统一用 SIGNAL SQLSTATE '45000'，这是未处理异常的标准状态码。
• 触发器不会替代应用层校验，建议在数据库和应用层同时做验证。
• 调试困难，建议配合日志表记录关键操作（如写入 error_log 表）。
• 性能影响：每行操作都会执行触发器逻辑，大数据量批量插入时需谨慎。