mysql trigger signal_MySQL-[SIGNAL/RESIGNAL/GET DIAGNOSTICS]的使用

最近在做 SQL Server 到 MySQL 的迁移(migration)，相较于对表和数据的迁移，最令人犯难的还是在功能性存储过程脚本的改写转换(convert)，虽说 MySQL 如今是蓬勃发展，不断的更新迭代的优化，但是在存储过程等脚本方面与 Oracle、SQL Server 相比，个人感觉是有所欠缺的，无论是灵活性还是实用性，有时真的是很难达到自己想要的效果，或许这就是为什么存储过程在 MySQL 中使用较少的原因吧……

承接上一篇关于MySQL的异常处理，继续异常处理的扩展性用法：

一、常规声明的异常处理

1、条件声明

DECLARE condition_name CONDITION FORcondition_value

condition_value: {

mysql_error_code| SQLSTATE [VALUE]sqlstate_value

}

condition_name：标准的变量命名；

condition_value：SQLSTATE 值或者 MySQL 自身的 ERROR CODE ；

注：单独的 condition 语句不能直接运行，只能作为【条件处理】的一部分。

2、条件处理

DECLAREhandler_action HANDLERFOR condition_value [, condition_value]...

statement

handler_action: {CONTINUE

| EXIT

|UNDO

}

condition_value: {

mysql_error_code| SQLSTATE [VALUE]sqlstate_value|condition_name|SQLWARNING| NOTFOUND|SQLEXCEPTION

}

handler_action：代表处理的动作，常用的是继续(CONTIUE)和直接退出(EXIT)；

condition_value：异常处理捕获条件或情况，包括【条件声明】里的 SQLSTATE, MYSQL EEROR CODE, condition_name 以及范围混淆的其他两种；

注：SQLWARNING、SQLEXCEPTION、NOT FOUND 表示任何不存在的 WARNING 或者 ERROR。

mysql> DESCtab7;

ERROR 1146 (42S02): Table 'TestDB.tab7' doesn't exist

DELIMITER //

CREATE PROCEDUREPROC_1()BEGIN

DECLARE CONTINUE HANDLER FOR 1146

BEGIN

--body of handler

END;DECLARE not_exist_table CONDITION FOR 1146;DECLARE CONTINUE HANDLER FORnot_exist_tableBEGIN

--body of handler

END;DECLARE not_exist_table CONDITION FOR SQLSTATE '42S02';DECLARE CONTINUE HANDLER FORnot_exist_tableBEGIN

--body of handler

END;END //DELIMITER ;

二、SIGNAL 与 RESIGNAL

SIGNAL 与 RESIGNAL 可以通过自定义伪装系统的错误信息以及代码，刷新当前警告缓冲区域。

1、SIGNAL

SIGNAL是“返回”错误的方法，向处理程序，应用程序的外部部分或客户端提供错误信息。

此外，它还可以控制错误的特征(错误号，SQLSTATE值，消息)。 如果没有SIGNAL，则必须采用诸如故意引用不存在的表之类的解决方法来导致例程返回错误。

SIGNAL condition_value[SET signal_information_item

[, signal_information_item]...]

condition_value: {

SQLSTATE[VALUE]sqlstate_value|condition_name

}

signal_information_item:

condition_information_item_name=simple_value_specification

condition_information_item_name: {

CLASS_ORIGIN|SUBCLASS_ORIGIN|MESSAGE_TEXT|MYSQL_ERRNO|CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME|CATALOG_NAME|SCHEMA_NAME|TABLE_NAME|COLUMN_NAME|CURSOR_NAME

}

2、RESIGNAL

同样的，RESIGNAL 也可以异常处理并返回错误信息。

RESIGNAL [condition_value]

[SET signal_information_item

[, signal_information_item]...]

condition_value: {

SQLSTATE[VALUE]sqlstate_value|condition_name

}

signal_information_item:

condition_information_item_name=simple_value_specification

condition_information_item_name: {

CLASS_ORIGIN|SUBCLASS_ORIGIN|MESSAGE_TEXT|MYSQL_ERRNO|CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME|CATALOG_NAME|SCHEMA_NAME|TABLE_NAME|COLUMN_NAME|CURSOR_NAME

}

通过对比 SIGNAL 与 RESIGNAL 的语法，在使用 SIGNAL 方法的时候必须指定 condition_value ，也就是说其不能单独使用，需要先定义异常处理，可以在存储过程中的任何位置使用 SIGNAL 语句。

而 RESIGNAL 可以省略RESIGNAL语句的所有属性，甚至可以省略SQLSTATE值，但必须在错误或警告处理程序中使用 RESIGNAL 语句，否则将收到一条错误消息，指出“RESIGNAL when handler is not active”。如果单独使用RESIGNAL语句，则所有属性与传递给条件处理程序的属性相同。

3、常见对比使用实例

DELIMITER //

CREATE PROCEDURE `test_proc`(var1 int,var2 int)BEGIN

declare ErrorMessage varchar(255) ;--SIGNAL Declarations

declare EXP_CONDITION condition for sqlstate 'EX000';declare exit handler for sqlstate 'EX000' beginsignal EXP_CONDITIONset message_text =ErrorMessage ;end;--RESIGNAL Declarations

declare exit handler for sqlstate '42S02' beginresignalset message_text = 'Unknown tables appear in the process body.';end;--Processing

if( var1 <> var2 ) then

set ErrorMessage = 'The first number input does not equal the second number.';

signal EXP_CONDITIONset message_text =ErrorMessage ;end if;select * from xxx ; --unknow table xxx

END //DELIMITER ;

mysql> call test_proc(1,1);

ERROR 1146 (42S02): Unknown tables appear inthe process body.

mysql> call test_proc(1,2);

ERROR 1644 (EX000): The first number input does not equal the second number.

推荐使用SIGNAL，灵活随机，在定义好后即可将 SIGNAL 语句放到任何你想放的地方进行判断预警处理。

三、GET DIAGNOSTICS

5.6开始支持的语法，从而获取错误缓冲区的内容，然后把这些内容输出到不同范围域的变量里，以便后续灵活操作处理。

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

}

statement_information_item：statment 执行情况信息捕获反馈，包括 NUMBER、ROW_COUNT；

condition_information_item：捕获异常情况信息；

当条件发生，可以通过变量去接收条件项目信息，但也不是说有的 MySQL 都会进行填充赋值，也会出现空值的(例如：SCHEMA_NAME and TABLE_NAME is null when drop table)。

mysql> delete fromt5;

Query OK,3 rows affected (0.04sec)

mysql> GET DIAGNOSTICS @p3 = NUMBER, @p4 =ROW_COUNT;

Query OK,0 rows affected (0.00sec)

mysql> select @p3,@p4;+------+------+

| @p3 | @p4 |

+------+------+

| 0 | 3 |

+------+------+

1 row in set (0.00sec)

mysql> drop tablexxx;

ERROR1051 (42S02): Unknown table 'TestDB.xxx'mysql> show warnings; --or show error

+-------+------+----------------------------+

| Level | Code | Message |

+-------+------+----------------------------+

| Error | 1051 | Unknown table 'TestDB.xxx' |

+-------+------+----------------------------+

1 row in set (0.00sec)

mysql> GET DIAGNOSTICS CONDITION 1

-> @p1 = RETURNED_SQLSTATE, @p2 =MESSAGE_TEXT;

Query OK,0 rows affected (0.00sec)

mysql> select @p1,@p2;+-------+----------------------------+

| @p1 | @p2 |

+-------+----------------------------+

| 42S02 | Unknown table 'TestDB.xxx' |

+-------+----------------------------+

1 row in set (0.00 sec)

注：个人认为，因为使用 GET DIAGNOSTICS 略有些鸡肋，使用选择上更多的会是用 SIGNAL 语句进行异常处理，所以在此不做深究 GET DIAGNOSTICS 的使用。