mysql - 未调用存储过程 SQLWARNING 退出处理程序

Question

(第一次发帖。如果我违反任何习俗或协议(protocol)，请道歉。)

我想为我的存储过程中抛出的任何 SQLWARNING 定义一个退出处理程序。但是处理程序被忽略了，它永远不会触发。我很困惑。

我正在使用 MariaDB v. 10.0.23。不过，我已经在 MariaDB v. 10.1.14 上对此进行了测试，并得到了相同的结果。

下面是创建测试数据库的SQL。

CREATE DATABASE testerrorhandling;

USE testerrorhandling;

CREATE TABLE test
(
    int_notnull         INT NOT NULL
);


DELIMITER //
CREATE DEFINER=CURRENT_USER PROCEDURE create_record
(
    IN p INT
)

BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SELECT 'Handler for SQLEXCEPTION fired.';

    DECLARE EXIT HANDLER FOR SQLWARNING
        SELECT 'Handler for SQLWARNING fired.';

    INSERT INTO test (int_notnull) VALUES (p);

END;
//

DELIMITER ;

如果我尝试添加空值，该过程会调用正确的处理程序。

MariaDB [testerrorhandling]> call create_record(null);
+---------------------------------+
| Handler for SQLEXCEPTION fired. |
+---------------------------------+
| Handler for SQLEXCEPTION fired. |
+---------------------------------+
1 row in set (0.00 sec)

但现在如果我尝试添加一个非整数值，则会引发警告，但 SQLWARNING 错误处理程序永远不会触发。值 0 存储在 test 表中。

MariaDB [testerrorhandling]> call create_record('this is not an integer');
Query OK, 1 row affected, 1 warning (0.04 sec)

MariaDB [testerrorhandling]> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'this is not an integer' for column 'p' at row 1 |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

这不是我期望或想要的。我一定是做错了什么。我在这里缺少什么？

Answer 1

警告发生在存储过程调用中，而不是在存储过程中，因此不会触发警告(在存储过程中)。

在以下示例中，您可以看到参数 p 在存储过程中的值为零 (0)，因此没有警告:

MariaDB [_]> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DROP PROCEDURE IF EXISTS create_record;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS test (
    ->   int_notnull INT NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER //

MariaDB [_]> CREATE PROCEDURE create_record (
    ->   IN p INT
    -> )
    -> BEGIN
    ->     DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->         SELECT 'Handler for SQLEXCEPTION fired.';
    -> 
    ->     DECLARE EXIT HANDLER FOR SQLWARNING
    ->         SELECT 'Handler for SQLWARNING fired.';
    -> 
    ->     SELECT CONCAT('VALUE OF p: ', p);
    ->     INSERT INTO test (int_notnull) VALUES (p);
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER ;

MariaDB [_]> call create_record('this is not an integer');
+---------------------------+
| CONCAT('VALUE OF p: ', p) |
+---------------------------+
| VALUE OF p: 0             |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [_]> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'this is not an integer' for column 'p' at row 1 |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

在下面的示例中，警告在存储过程的调用中和存储过程中触发:

MariaDB [_]> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DROP PROCEDURE IF EXISTS create_record;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS test (
    ->   int_notnull INT NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER //

MariaDB [_]> CREATE PROCEDURE create_record (
    ->   IN p INT
    -> )
    -> BEGIN
    ->     DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->         SELECT 'Handler for SQLEXCEPTION fired.';
    -> 
    ->     DECLARE EXIT HANDLER FOR SQLWARNING
    ->         SELECT 'Handler for SQLWARNING fired.';
    -> 
    ->     SELECT CONCAT('VALUE OF p: ', p);
    ->     SET p := 'this is not an integer';
    ->     INSERT INTO test (int_notnull) VALUES (p);
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER ;

MariaDB [_]> call create_record('this is not an integer');
+---------------------------+
| CONCAT('VALUE OF p: ', p) |
+---------------------------+
| VALUE OF p: 0             |
+---------------------------+
1 row in set (0.00 sec)

+-------------------------------+
| Handler for SQLWARNING fired. |
+-------------------------------+
| Handler for SQLWARNING fired. |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [_]> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'this is not an integer' for column 'p' at row 1 |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)