MySQL 使用 SIGNAL 语句创建自己的异常

到目前为止，我们已经讨论了如何处理 MySQL 在存储程序中执行 SQL 语句时引发的错误。然而，除了这些系统引发的异常之外，你肯定还需要处理一些特定于应用程序需求的错误。如果您的代码违反了该规则，您可能希望引发自己的错误，并将此问题反馈给用户。SQL:2003 规范为此提供了 SIGNAL 语句。

在 MySQL 5.5+ 版本中，引入了 SIGNAL 语句，允许你提出自己的错误条件。

下面示例演示了没有使用 SIGNAL 语句存在的糟糕情况：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user_ext`(
   IN in_id INT,
   IN in_name VARCHAR(50),
   IN in_age INT,
   IN in_sex VARCHAR(1),
   IN in_phone VARCHAR(200),
   IN in_email VARCHAR(200),
   -- 用来将错误信息传递给调用者
   OUT out_state VARCHAR(2000))
BEGIN
   -- 定义命名条件
   DECLARE null_val_error CONDITION FOR 1062;
   -- 定义处理器
   DECLARE CONTINUE HANDLER FOR null_val_error SET out_state=CONCAT('主键重复，ID=', in_id);
   
   -- 插入用户信息
   INSERT INTO `user`(
       `id`, `name`, `age`, `sex`, `phone`, `email`
   ) VALUES (
       in_id, in_name, in_age, in_name, in_phone, in_email
   );
END

使用 MySQL 客户端调用存储过程：

-- 定义变量
mysql> SET @out_state='';
Query OK, 0 rows affected (0.00 sec)

-- 调用存储过程，返回信息可知，调用成功了
mysql> call insert_user_ext(99, null, 30, 0, '15881002233', 'test@gmail.com', @out_state);
Query OK, 0 rows affected (0.00 sec)

-- 然后手动查看变量 @out_state 的值，才知道，原来调用失败了，主键重复
mysql> select @out_state;
+--------------+
| @out_state   |
+--------------+
| 用户姓名为空 |
+--------------+
1 row in set (0.09 sec)

从上面示例可以得知，如果存储过程被其他程序调用，那么即使更新实际上被拒绝，存储过程也会返回成功（至少不会引发错误）。当然，调用程序可以通过检查 @out_state 变量来发现这一点，但由于存储过程调用本身不会引发异常，所以程序很有可能会认为存储过程成功了。

mysql 广告位

如果这个存储过程能在用户ID、用户姓名为空时触发错误条件，那么它就会更加健壮，也不容易出错。ANSI SQL:2003 SIGNAL 语句允许你这样做，语法如下：

SIGNAL SQLSTATE sqlstate_code|condition_name [SET MESSAGE_TEXT=string_or_variable];

注意，你可以创建自己的 SQLSTATE 代码（允许使用的数字有一些规则），也可以使用现有的 SQLSTATE 代码或命名条件。

执行 SIGNAL 语句时，会引发一个数据库错误条件，其作用与无效 SQL 语句或违反约束引发的错误完全相同。该错误可能会返回给调用程序，也可能被本程序或其他存储程序中的处理程序捕获。例如：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user_ext`(
   IN in_id INT,
   IN in_name VARCHAR(50),
   IN in_age INT,
   IN in_sex VARCHAR(1),
   IN in_phone VARCHAR(200),
   IN in_email VARCHAR(200))
BEGIN
   -- 定义命名条件
   DECLARE arguments_invalid_error CONDITION FOR SQLSTATE '99001';
   
   IF in_id IS NULL THEN
       SIGNAL arguments_invalid_error SET MESSAGE_TEXT='用户ID为空';
   END IF;
   
   IF in_name IS NULL THEN
       SIGNAL arguments_invalid_error SET MESSAGE_TEXT='用户姓名为空';
   END IF;
   
   -- 插入用户信息
   INSERT INTO `user`(
       `id`, `name`, `age`, `sex`, `phone`, `email`
   ) VALUES (
       in_id, in_name, in_age, in_name, in_phone, in_email
   );
END

使用 MySQL 客户端调用存储过程：

mysql> call insert_user_ext(99, null, 30, 0, '15881002233', 'test@gmail.com');
ERROR 1644 (99001): 用户姓名为空

使用 SIGNAL，我们可以让用户或调用程序完全清楚地看到存储程序执行失败。

除了使用命名条件，还可以使用 SQLSTATE，例如：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user_ext`(
   IN in_id INT,
   IN in_name VARCHAR(50),
   IN in_age INT,
   IN in_sex VARCHAR(1),
   IN in_phone VARCHAR(200),
   IN in_email VARCHAR(200))
BEGIN
   IF in_id IS NULL THEN
       SIGNAL SQLSTATE '99001' SET MESSAGE_TEXT = '用户ID为空';
   END IF;
   
   IF in_name IS NULL THEN
       SIGNAL SQLSTATE '99001' SET MESSAGE_TEXT = '用户姓名为空';
   END IF;
   
   -- 插入用户信息
   INSERT INTO `user`(
       `id`, `name`, `age`, `sex`, `phone`, `email`
   ) VALUES (
       in_id, in_name, in_age, in_name, in_phone, in_email
   );
END