前言

最近项目中需要迁移数据，对旧表A 的每一行记录处理后插入 新表B。 Google一下，发现 MySQL Cursor 适合这种场景，但是 Cursor 中使用到了 handler，因此先学习一波 MySQL handler 的相关知识，下篇文章再学习 Cursor。

本文基于 MySQL 8.0

Handler的定义

MySQL 中可以针对一个或多个条件声明一个 handler，当满足其中一个条件时，就会执行这个 handler 相应的动作。语法如下：

1
2
3

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler 必须定义在变量或者条件声明之后。

我们需要关注的有三个地方：

  • handler_action：handler_action 用于控制执行完 handler 相应动作后，程序是继续运行还是退出；

  • condition_value：设定的条件；

  • statement：执行的动作，可以是一个简单的赋值语句 SET var_name = value，也可以是一段使用 BEGIN…END 包裹的复杂逻辑；

参数详解

handler_action

handler_action 有如下三个枚举值：

  • CONTINUE：程序继续运行
  • EXIT：退出 handler 所在的 BEGIN…END 代码块
  • UNDO：暂不支持

condition_value

condition_value 可以有如下取值：

  • mysql_error_code ： MySQL错误码，一个整数，例如 1051 代表 “unknown table”

    1
2
3
4

    DECLARE CONTINUE HANDLER FOR 1051
BEGIN
  -- body of handler
END;

    0 表示成功 Code，因此不要使用 0 作为 condition_value

    MySQL错误码列表见 https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

  • sqlstate_value: 表示 SQL 状态，长度为五个字母，例如 ‘42S01’ 代表 “unknown table”:

    1
2
3
4

    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
BEGIN
  -- body of handler
END

  • condition_name：一个已经定义好的条件。比如有些条件比较复杂，放在 handler 里面过于冗长；或者为了条件重用，可以通过如下方式定义：

    1
2
3
4
5
6

    DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

    我们先声明了一个条件 no_such_table，然后定义 handler 的时候使用这个条件:

    1
2
3
4
5
6

    DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
  BEGIN
    -- body of handler
  END;

  • SQLWARNING： 以 '01' 开头的 SQLSTATE 的别名

    1
2
3
4

    DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    -- body of handler
  END;

  • NOT FOUND： 以 '02' 开头的 SQLSTATE 的别名。当我们使用 cursor 遍历数据时，如果遍历到表末尾，没有数据可供遍历时，SQLSTATE 就是 ‘02000’，因此我们可以使用 NOT FOUND 来控制 handler 的行为。

    1
2
3
4

    DECLARE CONTINUE HANDLER FOR NOT FOUND
  BEGIN
    -- body of handler
  END;

  • SQLEXCEPTION：除 '00', '01'、 '02' 开头的 SQLSTATE 的别名。

    1
2
3
4

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    -- body of handler
  END;

condition 和 handler

定义 handler 处理 condition，就类似 Java 中使用 Try-Catch 捕获异常一样，如果一个 condition 没有 handler 处理，会发生什么呢？

  • 对于 SQLEXCEPTION 类型的 conditions，当前的程序会终止退出，就像定义了一个 EXIT handler 一样；如果当前程序是被另一个程序调用，那么这个 condition 会向上抛，交由另一个程序的来处理，以此类推。和 Java 中的 Exception 处理类似。

  • 对于 SQLWARNING 类型的 conditions，当前的程序会继续运行，就像定义了一个 CONTINUE handler 一样

  • 对于 NOT FOUND 类型的 conditions，如果这个 condition 是正常发生的，程序继续运行；如果是通过 SIGNAL 或 RESIGNAL 抛出的，程序退出

如下示例中，定义了针对 SQLSTATE ‘23000’ (duplicate-key 错误) 的 handler，当第二次插入数据时，会产生 duplicate-key 错误。

  1. 由于我们定义的是 CONTINUE handler，procedure 在错误处会继续运行，最终输出的 @x=3；
  2. 如果不定义 Handler，产生 duplicate-key时 procedure 就会退出，@x=2;
  3. 如果定义的是 EXIT handler，procedure 产生错误后会执行 handler 中的动作，最终输出 @x=1。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
       BEGIN
         DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
         SET @x = 1;
         INSERT INTO test.t VALUES (1);
         SET @x = 2;
         INSERT INTO test.t VALUES (1);
         SET @x = 3;
       END;
       //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

label 和 handler

标签（label）的可访问范围不包含 handler 中的代码，因此 handler 中的 statement 不能使用 ITERATE 或 LEAVE 来引用包含 自己的标签。

下面的例子中，REPEAT 的标签为 retry，retry 标签中定义了一个 handler，该 handler 不能使用 ITERATE 来引用包含自己的标签 retry；在 handler 外部的 IF…THEN 中引用是合法的。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            ITERATE retry;    # illegal
          END;
        IF i < 0 THEN
          LEAVE retry;        # legal
        END IF;
        SET i = i - 1;
      END;
    UNTIL FALSE END REPEAT;
END;

那么如何在不引用外部 label 的情况下退出呢，有如下两种方法：

  1. 定义一个 EXIT handler，如果不需要清理动作，将handler 的 statement 置空：

    1

    DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

  2. 定义一个 CONTINUE handler，修改变量的值，然后在 handler 外部通过变量判断来退出：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

    CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  DECLARE done INT DEFAULT FALSE;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            SET done = TRUE;
          END;
        IF done OR i < 0 THEN
          LEAVE retry;
        END IF;
        SET i = i - 1;
      END;
    UNTIL FALSE END REPEAT;
END;

总结

本文首先介绍了MySQL handler 的定义和参数详解，然后分析了在没有 handler时， condition 是如何被处理的，最后介绍了在 label 中 使用 handler 的注意点。

文章基本是按照官网内容进行翻译得来，并加入了一点自己的理解。写这篇文章，主要是希望之后需要使用 MySQL handler时，可以翻阅这篇笔记快速复习和使用，而不是再去翻阅官网和其他博客，增加了理解成本和学习时间。

本文是在使用 MySQL Cursor 时发现其中用到了 handler，学习并写下了这篇笔记，下篇文章我们就去学习 Cursor，并看看它是如何使用 handler 的。

更多

微信公众号：CodePlayer