MySQL存储过程 - 区分注释和错误

I have a stored procedure that executes stored SQL.

我有一个执行存储的SQL的存储过程。

However, the error-handler kicks-in and exits if the user attempts to execute

但是,如果用户尝试执行错误处理程序,则会启动并退出

drop temporary table if exists t_person;

and 't_person' doesn't exist. I'm perfectly happy to generate an error when 'if exists' is not given, but how do I avoid an error for the earlier case (the error-code is unchanged)?

并且't_person'不存在。我很高兴在没有给出'if exists'时产生错误,但是如何避免前一种情况的错误(错误代码没有改变)?

Here's my error handler:

这是我的错误处理程序:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  set @sql = 'select \'Invalid SQL or bad parameter resulting in invalid SQL\' as `Error`';
  prepare stmt from @sql;
  execute stmt;
END;

1 个解决方案

#1

You could use a CONTINUE handler rather an an EXIT handler that catches MySQL error 1051 "Unknown table"...

您可以使用CONTINUE处理程序而不是捕获MySQL错误1051“未知表”的EXIT处理程序...

DECLARE CONTINUE HANDLER FOR 1051 BEGIN END;

-or-

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END;

EDIT

To catch a MySQL error in an exception handler, you need to specify the MySQL error number or the corresponding SQLSTATE to be caught. (You could specify a named condition, but that named condition has to resolve to a MySQL error number or SQLSTATE).

要在异常处理程序中捕获MySQL错误,您需要指定MySQL错误号或要捕获的相应SQLSTATE。 (您可以指定命名条件,但该命名条件必须解析为MySQL错误号或SQLSTATE)。

A syntax error would throw MySQL error 1064.

语法错误会抛出MySQL错误1064。

If a table foo exists, and you issue a

如果表foo存在,则发出一个

CREATE TEMPORARY TABLE IF NOT EXISTS `foo` (id INT);

That would throw MySQL error 1050.

这会抛出MySQL错误1050。

To catch that error, declare another handler for that. Assuming you want to "swallow" the exception and continue processing...

要捕获该错误,请为此声明另一个处理程序。假设你想“吞下”异常并继续处理......

DECLARE CONTINUE HANDLER FOR 1050 BEGIN END;

Reference: https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

~~The like p_person in the drop temporary table statement looks wrong to me; at least, I'm not familiar with using the LIKE keyword in a DROP TABLE statement.~~

drop临时表语句中的p_person似乎对我不合适;至少,我不熟悉在DROP TABLE语句中使用LIKE关键字。

#1