MySQL - SIGNAL

MySQL：使用 SIGNAL 语句进行自定义错误处理

在健壮的应用程序开发中，仅仅让存储过程或触发器因通用数据库错误而失败是不理想的。SIGNAL 语句赋予你对错误处理的精确控制，允许你停止执行并向调用应用程序返回自定义的、有意义的错误消息。

SIGNAL 是 MySQL 中高级异常处理的关键组件，常用于存储过程、存储函数和触发器中，以强制执行复杂的业务逻辑。

理解 SIGNAL

SIGNAL 语句用于显式地引发错误条件。当它被触发时，会将一个错误值传回给调用者。这可以是一个通用的 SQLSTATE 或你声明的命名条件。

语法

SIGNAL sqlstate_value | condition_name
[SET signal_information_item_name = value, ...];

关键组成部分：

• sqlstate_value | condition_name：这指定了错误。SQLSTATE 是一个 5 个字符的字符串。按照惯例，以 ‘45’ 开头的 SQLSTATE 值用于用户定义的错误。condition_name 是使用 DECLARE ... CONDITION 定义的 SQLSTATE 的更具可读性的别名。
• SET ...：这个可选子句允许你自定义错误。最有用的项是 MESSAGE_TEXT，它提供了一个人类可读的错误消息。你还可以设置 MYSQL_ERRNO（一个 MySQL 特定的错误号）。

实际示例

让我们探讨如何使用 SIGNAL 来强制执行业务规则。

示例 1：验证输入参数

假设一个存储过程用于添加员工，但要求年龄必须为 18 岁或以上。我们可以使用 SIGNAL 来拒绝无效的条目。

DELIMITER //

CREATE PROCEDURE AddEmployee(
    IN p_name VARCHAR(100),
    IN p_age INT
)
BEGIN
    -- 业务规则：员工年龄必须至少为 18 岁。
    IF p_age < 18 THEN
        -- 抛出自定义错误。
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Employee age must be 18 or greater.';
    END IF;

    -- 如果年龄有效，则继续插入。
    INSERT INTO Employees (name, age) VALUES (p_name, p_age);
    SELECT 'Employee added successfully.' AS message;
END //

DELIMITER ;

现在，让我们调用这个过程。

-- 此调用将成功。
CALL AddEmployee('John Doe', 30);

如果我们尝试添加一个未成年员工，将触发我们的自定义错误。

-- 此调用将失败。
CALL AddEmployee('Jane Smith', 16);

失败调用的输出

ERROR 1644 (45000): Employee age must be 18 or greater.

示例 2：使用 DECLARE ... CONDITION 提高可读性

对于具有多个自定义检查的复杂过程，声明命名条件可以使代码更清晰、更易于维护。

DELIMITER //

CREATE PROCEDURE UpdateProductPrice(
    IN p_product_id INT,
    IN p_new_price DECIMAL(10, 2)
)
BEGIN
    -- 声明一个用于无效价格的命名条件。
    DECLARE invalid_price CONDITION FOR SQLSTATE '45000';

    -- 业务规则：价格不能为负数。
    IF p_new_price < 0 THEN
        -- 发送命名条件信号。
        SIGNAL invalid_price
        SET MESSAGE_TEXT = 'Product price cannot be negative.';
    END IF;

    -- 业务规则：价格不能超过当前价格的两倍（示例）。
    -- （此处应放置获取当前价格的 SELECT 语句）

    UPDATE Products SET price = p_new_price WHERE id = p_product_id;
END //

DELIMITER ;

用负数价格调用此过程将引发错误。

CALL UpdateProductPrice(101, -5.00);

输出

ERROR 1644 (45000): Product price cannot be negative.

结合 SIGNAL 与 DECLARE HANDLER

虽然 SIGNAL 引发错误，但 DECLARE HANDLER 会捕获它。这允许你在过程退出之前执行清理操作（如回滚事务）或记录错误。

DELIMITER //

CREATE PROCEDURE TransferFunds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    DECLARE insufficient_funds CONDITION FOR SQLSTATE '45000';

    -- 如果发生错误，回滚事务。
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 重新抛出错误，以便调用者知道它失败了。
    END;

    START TRANSACTION;

    -- 检查源账户是否有足够的资金。
    IF (SELECT balance FROM Accounts WHERE id = from_account) < amount THEN
        SIGNAL insufficient_funds SET MESSAGE_TEXT = 'Insufficient funds.';
    END IF;

    -- 执行转账
    UPDATE Accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE Accounts SET balance = balance + amount WHERE id = to_account;

    COMMIT;
END //

DELIMITER ;

最佳实践

• 使用 SQLSTATE '45000'：这是“未处理的用户定义异常”的指定类别。它明确区分了自定义业务逻辑错误和标准 MySQL 服务器错误。
• 编写清晰的消息：MESSAGE_TEXT 是为开发人员或最终用户准备的。使其具有描述性和帮助性。
• 保持简单：不要过度使用 SIGNAL。标准约束（NOT NULL、CHECK、FOREIGN KEY）对于简单的数据验证更为高效。
• 与事务结合使用：对于多步操作，在事务内使用 SIGNAL 并设置一个处理程序以在失败时 ROLLBACK，从而确保数据一致性。