MySQL 存储过程错误处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7764887/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Stored Procedure Error Handling
提问by Tom Mac
I believe there is nothing currently available in MySQL that allows access to the SQLSTATEof the last executed statement within a MySQL stored procedure. This means that when a generic SQLExceptionis raised within a stored procedure it is hard/impossible to derive the exact nature of the error.
我相信 MySQL 中目前没有任何可用的东西允许访问SQLSTATEMySQL 存储过程中最后执行的语句。这意味着当SQLException在存储过程中引发泛型时，很难/不可能得出错误的确切性质。
Does anybody have a workaround for deriving the SQLSTATEof an error in a MySQL stored procedure that does not involve declaring a handler for every possible SQLSTATE?
有没有人有一种解决方法来导出SQLSTATEMySQL 存储过程中的错误，而不涉及为每个可能的 SQLSTATE 声明处理程序？
For example - imagine that I am trying to return an error_status that goes beyond the generic "SQLException happened somewhere in this BEGIN....ENDblock" in the following:
例如 - 想象一下，我试图返回一个 error_status ，它超出了以下通用的“SQLException发生在这个BEGIN....END块中的某处”：
DELIMITER $$
CREATE PROCEDURE `myProcedure`(OUT o_error_status varchar(50))
MY_BLOCK: BEGIN
DECLARE EXIT handler for 1062 set o_error_status := "Duplicate entry in table";
DECLARE EXIT handler for 1048 set o_error_status := "Trying to populate a non-null column with null value";
-- declare handlers ad nauseum here....
DECLARE EXIT handler for sqlexception set o_error_status:= "Generic SQLException. You'll just have to figure out the SQLSTATE yourself...." ;
-- Procedure logic that might error to follow here...
END MY_BLOCK$$
Any tips?
有小费吗？
PS I am running MySQL 5.1.49
PS 我正在运行 MySQL 5.1.49
采纳答案by Marc Alff
GET DIAGNOSTICS is available in 5.6.4
GET DIAGNOSTICS 在 5.6.4 中可用
See http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html
见 http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html
回答by Johan
I believe there is nothing currently available in MySQL that allows access to the SQLSTATE of the last executed statement within a MySQL stored procedure. This means that ... it is hard/impossible to derive the exact nature of the error.
我相信 MySQL 中目前没有任何可用的东西允许访问 MySQL 存储过程中最后执行的语句的 SQLSTATE。这意味着......很难/不可能得出错误的确切性质。
Luckily that is not true.
幸运的是，事实并非如此。
SHOW ERRORS LIMIT 1 -- for SQL-state > 2
SHOW WARNINGS LIMIT 1 -- for SQL-state 1,2
Will show the last error or warning.
将显示最后一个错误或警告。
In order to prevent listing each and every error, you can handle a class of SQL-errors like so:
为了防止列出每个错误，您可以像这样处理一类 SQL 错误：
SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). An example is shown in Section 12.7.5, “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.
SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
SQLWARNING 是以“01”开头的 SQLSTATE 值类的简写。
NOT FOUND 是以“02”开头的 SQLSTATE 值类的简写。这仅与游标上下文相关，用于控制游标到达数据集末尾时发生的情况。如果没有更多行可用，则会出现 SQLSTATE 值为 02000 的 No Data 条件。要检测此条件，您可以为其设置处理程序（或 NOT FOUND 条件）。第 12.7.5 节“游标”中显示了一个示例。对于不检索任何行的 SELECT ... INTO var_list 语句，也会出现这种情况。
SQLEXCEPTION 是不以“00”、“01”或“02”开头的 SQLSTATE 值类的简写。
So to handle an exception, you need to onlydo:
因此，要处理异常，您只需要执行以下操作：
DECLARE EXIT HANDLER FOR SQLSTATE SQLEXCEPTION .....;
Links:
http://dev.mysql.com/doc/refman/5.5/en/signal.html
http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
链接：
http : //dev.mysql.com/doc/refman/5.5/en/signal.html
http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
回答by aurora
I am doing the following workaround: using a SELECT to provocate an error. For example:
我正在执行以下解决方法：使用 SELECT 来引发错误。例如：
SELECT RAISE_ERROR_unable_to_update_basket;
This will result in the following error message (example):
这将导致以下错误消息（示例）：
ERROR 1054 (42S22): Unknown column 'RAISE_ERROR_unable_to_update_basket' in 'field list'
I am wrapping my call to a stored procedure in a try { ... } catch { ... } and now can handle this error. This will of course only work for provocating custom error messages from inside your stored procedure and will not handle any SQL or database errors, that might occur (because of duplicate-key entry). In the latter case, you might be able to workaround this using the solution of Johan.
我正在将我对存储过程的调用包装在 try { ... } catch { ... } 中，现在可以处理此错误。这当然仅适用于从存储过程内部引发自定义错误消息，并且不会处理可能发生的任何 SQL 或数据库错误（由于重复键条目）。在后一种情况下，您可以使用 Johan 的解决方案来解决此问题。
