MySQL的SQL语句 -条件处理语句（3） - GET DIAGNOSTICS 语句

数据库杂货铺 2021-04-12

2750

GET DIAGNOSTICS 语句

GET [CURRENT | STACKED] DIAGNOSTICS {
    statement_information_item
    [, statement_information_item] ...
  | CONDITION condition_number
    condition_information_item
    [, condition_information_item] ...
}


statement_information_item:
    target = statement_information_item_name


condition_information_item:
    target = condition_information_item_name


statement_information_item_name: {
    NUMBER
  | ROW_COUNT
}


condition_information_item_name: {
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | RETURNED_SQLSTATE
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}


condition_number, target:
    (see following discussion)

SQL 语句生成填充到诊断区域的诊断信息。GET DIAGNOSTICS 语句使应用程序能够检查此信息。（也可以使用 SHOW WARNINGS 或 SHOW ERRORS 查看条件或错误。）

执行 GET DIAGNOSTICS 不需要任何权限。

关键字 CURRENT 表示从当前诊断区域检索信息。关键字 STACKED 意味着从第二个诊断区域检索信息，该区域仅在当前上下文是条件句柄时可用。如果两个关键字都未给定，则默认为使用当前诊断区域。

GET DIAGNOSTICS 语句通常用于存储程序中的句柄中。它是一个 MySQL 扩展，允许 GET [CURRENT] DIAGNOSTICS 在句柄上下文之外检查任何 SQL 语句的执行。例如，如果调用 mysql 客户端程序，可以在提示符处输入以下语句：

mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1
         @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
mysql> SELECT @p1, @p2;
+-------+------------------------------------+
| @p1   | @p2                                |
+-------+------------------------------------+
| 42S02 | Unknown table 'test.no_such_table' |
+-------+------------------------------------+

此扩展仅适用于当前诊断区域。它不适用于第二个诊断区域，因为仅当当前上下文是条件句柄时才允许 GET STACKED DIAGNOSTICS。如果不是这种情况，则会发生当句柄未激活时调用了 GET STACKED DIAGNOSTICS 的错误。

简而言之，它包含两种信息：

● 语句信息，例如发生的条件号码或受影响的行数。

● 条件信息，如错误代码和消息。如果一条语句引发多个条件，则诊断区域的这部分针对每个条件都有一个条件区域。如果语句没有引发任何条件，则诊断区域的这部分为空。

这个条件语句产生了三个条件，包括的语句和条件信息如下：

Statement information:
  row count
  ... other statement information items ...
Condition area list:
  Condition area 1:
    error code for condition 1
    error message for condition 1
    ... other condition information items ...
  Condition area 2:
    error code for condition 2:
    error message for condition 2
    ... other condition information items ...
  Condition area 3:
    error code for condition 3
    error message for condition 3
    ... other condition information items ...

GET DIAGNOSTICS 可以获取语句或条件信息，但不能在同一语句中同时获取这两种信息：

● 要获取语句信息，请将所需的语句项检索到目标变量中。GET DIAGNOSTICS 的这个实例将可用条件的号码和受影响的行数分配给用户变量 @p1 和 @p2：

GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;

● 要获取条件信息，请指定条件编号并将所需的条件项检索到目标变量中。GET DIAGNOSTICS 的这个实例将 SQLSTATE 值和错误消息分配给用户变量 @p3 和 @p4：

GET DIAGNOSTICS CONDITION 1
  @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;

检索列表指定一个或多个 target = item_name 赋值信息，用逗号分隔。每个赋值都命名一个目标变量和一个 statement_information_item_name 或 condition_information_item_name 指示符，具体取决于语句检索语句还是条件信息。

用于存储项信息的有效 target 指示符可以是存储过程或函数参数、用 DECLARE 声明的存储程序局部变量或用户定义的变量。

有效 condition_number 指示符可以是存储过程或函数参数、用 DECLARE 声明的存储程序局部变量、用户定义变量、系统变量或字面量。字符字面量可以包括 _charset 导引器。如果条件编号不在从 1 到包含信息的条件区域数量的范围内，则会出现警告。在这种情况下，警告将添加到诊断区域而不清除它。

当一个条件发生时，MySQL 不会填充 GET DIAGNOSTICS 识别的所有条件项。例如：

mysql> GET DIAGNOSTICS CONDITION 1
         @p5 = SCHEMA_NAME, @p6 = TABLE_NAME;
mysql> SELECT @p5, @p6;
+------+------+
| @p5  | @p6  |
+------+------+
|      |      |
+------+------+

在标准 SQL 中，如果有多个条件，则第一个条件与前一个 SQL 语句返回的 SQLSTATE 值相关。在 MySQL 中，这是不能保证的。要获取主要错误，不能执行以下操作：

GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;

相反，首先检索条件计数，然后使用它指定要检查的条件编号：

GET DIAGNOSTICS @cno = NUMBER;
GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

下面是一个示例，它在存储过程上下文中使用 GET DIAGNOSTICS 和异常句柄来评估插入操作的结果。如果插入成功，该过程将使用 GET DIAGNOSTICS 获取受影响的行数。这表明，只要当前诊断区域尚未清除，就可以多次使用 GET DIAGNOSTICS 来检索有关语句的信息。

CREATE PROCEDURE do_insert(value INT)
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE nrows INT;
  DECLARE result TEXT;
  -- Declare exception handler for failed insert
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;


  -- Perform the insert
  INSERT INTO t1 (int_col) VALUES(value);
  -- Check whether the insert was successful
  IF code = '00000' THEN
    GET DIAGNOSTICS nrows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ',nrows);
  ELSE
    SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
  END IF;
  -- Say what happened
  SELECT result;
END;

假设 t1.int_col 是声明为 NOT NULL 的整数列。调用该过程分别插入非空值和空值时，会生成以下结果：

mysql> CALL do_insert(1);
+---------------------------------+
| result                          |
+---------------------------------+
| insert succeeded, row count = 1 |
+---------------------------------+


mysql> CALL do_insert(NULL);
+-------------------------------------------------------------------------+
| result                                                                  |
+-------------------------------------------------------------------------+
| insert failed, error = 23000, message = Column 'int_col' cannot be null |
+-------------------------------------------------------------------------+

当条件句柄激活时，会推送到诊断区域堆栈：

● 第一个（当前）诊断区域变为第二个（堆栈）诊断区域，并创建一个新的当前诊断区域作为其副本。

● 可在句柄中使用 GET [CURRENT] DIAGNOSTICS 和 GET STACKED DIAGNOSTICS 访问当前和堆栈诊断区域的内容。

● 最初，两个诊断区域返回相同的结果，因此只要在句柄中不执行更改其当前诊断区域的语句，就可能从当前诊断区域获取有关已激活句柄的条件的信息。

● 但是，在句柄中执行的语句可以修改当前诊断区域，根据常规规则清除和设置其内容。

获取有关激活句柄条件的信息的更可靠的方法是使用堆栈诊断区域，除了 RESIGNAL 之外，句柄内执行的语句不能修改该区域。

下一个示例显示如何在句柄中使用 GET STACKED DIAGNOSTICS 来获取有关已处理异常的信息，即使当前诊断区域已被句柄语句修改。

在存储过程 p() 中，我们尝试将两个值插入到包含 TEXT NOT NULL 列的表中。第一个值是非 NULL 字符串，第二个值是 NULL。该列禁止 NULL 值，因此第一次插入成功，但第二次插入导致异常。该过程包括一个异常句柄，该句柄将尝试插入 NULL 的操作映射到空字符串的插入：

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TEXT NOT NULL);
DROP PROCEDURE IF EXISTS p;
delimiter 
CREATE PROCEDURE p ()
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE errcount INT;
  DECLARE errno INT;
  DECLARE msg TEXT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Here the current DA is nonempty because no prior statements
    -- executing within the handler have cleared it
    GET CURRENT DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'current DA before mapped insert' AS op, errno, msg;
    GET STACKED DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'stacked DA before mapped insert' AS op, errno, msg;


    -- Map attempted NULL insert to empty string insert
    INSERT INTO t1 (c1) VALUES('');


    -- Here the current DA should be empty (if the INSERT succeeded),
    -- so check whether there are conditions before attempting to
    -- obtain condition information
    GET CURRENT DIAGNOSTICS errcount = NUMBER;
    IF errcount = 0
    THEN
      SELECT 'mapped insert succeeded, current DA is empty' AS op;
    ELSE
      GET CURRENT DIAGNOSTICS CONDITION 1
        errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
      SELECT 'current DA after mapped insert' AS op, errno, msg;
    END IF ;
    GET STACKED DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'stacked DA after mapped insert' AS op, errno, msg;
  END;
  INSERT INTO t1 (c1) VALUES('string 1');
  INSERT INTO t1 (c1) VALUES(NULL);
END;
//
delimiter ;
CALL p();
SELECT * FROM t1;

当句柄激活时，当前诊断区域的副本被推送到诊断区域堆栈。句柄首先显示当前和堆栈诊断区域的内容，这两个区域最初是相同的：

+---------------------------------+-------+----------------------------+
| op                              | errno | msg                        |
+---------------------------------+-------+----------------------------+
| current DA before mapped insert |  1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+


+---------------------------------+-------+----------------------------+
| op                              | errno | msg                        |
+---------------------------------+-------+----------------------------+
| stacked DA before mapped insert |  1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+

在 GET DIAGNOSTICS 语句之后执行的语句可能会重置当前诊断区域。语句可以重置当前诊断区域。例如，句柄将 NULL 插入映射到空字符串插入并显示结果。新插入成功并清除当前诊断区域，但堆栈的诊断区域保持不变，并且仍然包含有关激活句柄的条件的信息：

+----------------------------------------------+
| op                                           |
+----------------------------------------------+
| mapped insert succeeded, current DA is empty |
+----------------------------------------------+


+--------------------------------+-------+----------------------------+
| op                             | errno | msg                        |
+--------------------------------+-------+----------------------------+
| stacked DA after mapped insert |  1048 | Column 'c1' cannot be null |
+--------------------------------+-------+----------------------------+

当条件句柄结束时，其当前诊断区域将从堆栈中弹出，并且堆栈的诊断区域将成为存储过程中的当前诊断区域。

过程返回后，表包含两行。空行结果是由于尝试插入映射到空字符串插入的 NULL 而导致的：

+----------+
| c1       |
+----------+
| string 1 |
|          |
+----------+

在前面的示例中，条件句柄中从当前和堆栈诊断区域检索信息的前两个 GET DIAGNOSTICS 语句返回相同的值。如果重置当前诊断区域的语句更早地在句柄中执行，则不是这种情况。假设重写 p() 以将 DECLARE 语句放在句柄定义中，而不是放在其前面：

CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Declare variables to hold diagnostics area information
    DECLARE errcount INT;
    DECLARE errno INT;
    DECLARE msg TEXT;
    GET CURRENT DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'current DA before mapped insert' AS op, errno, msg;
    GET STACKED DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'stacked DA before mapped insert' AS op, errno, msg;
...

在这种情况下，结果取决于具体版本：

● 在 MySQL 5.7.2 之前，DECLARE 不会更改当前的诊断区域，因此前两个 GET DIAGNOSTICS 语句返回相同的结果，就像原始版本的 p() 一样。

● 在 MySQL 5.7.2 中，根据 SQL 标准，确保所有非诊断语句都填充在诊断区域中。DECLARE 是其中之一，因此在 5.7.2 及更高版本中，在句柄开头执行的 DECLARE 语句会清除当前诊断区域，GET DIAGNOSTICS 语句会产生不同的结果：

+---------------------------------+-------+------+
| op                              | errno | msg  |
+---------------------------------+-------+------+
| current DA before mapped insert |  NULL | NULL |
+---------------------------------+-------+------+


+---------------------------------+-------+----------------------------+
| op                              | errno | msg                        |
+---------------------------------+-------+----------------------------+
| stacked DA before mapped insert |  1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+

为避免在条件句柄中寻求获取激活句柄的条件信息时出现此问题，请确保访问堆栈诊断区域，而不是当前的诊断区域。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/get-diagnostics.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 -条件处理语句（3） - GET DIAGNOSTICS 语句

评论