如何在 MySQL 函数中引发错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/465727/
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
How to raise an error within a MySQL function
提问by Dónal
I've created a MySQL function and would like to raise an error if the values passed for the parameters are invalid. What are my options for raising an error within a MySQL function?
我创建了一个 MySQL 函数，如果为参数传递的值无效，我想引发一个错误。在 MySQL 函数中引发错误的选项有哪些？
回答by Austin Hyde
MySQL 5.5 introduces signals, which are similar to exceptions in other languages:
MySQL 5.5 引入了信号，类似于其他语言中的异常：
http://dev.mysql.com/doc/refman/5.5/en/signal.html
http://dev.mysql.com/doc/refman/5.5/en/signal.html
For example, in the mysqlcommand line client:
例如，在mysql命令行客户端中：
mysql> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error';
ERROR 1644 (45000): Custom error
回答by Ryan M
It's actually a combination of all three answers. You call a non-existent procedure to raise the error, and then declare an exit handler that catches the error you generated. Here's an example, using SQLSTATE 42000 (procedure does not exist) to throw an error before deletion if the row to be deleted has a foreign key id set:
它实际上是所有三个答案的组合。您调用一个不存在的过程来引发错误，然后声明一个退出处理程序来捕获您生成的错误。下面是一个例子，如果要删除的行设置了外键 id，则使用 SQLSTATE 42000（过程不存在）在删除前抛出错误：
DROP PROCEDURE IF EXISTS decount_test;
DELIMITER //
CREATE DEFINER = 'root'@'localhost' PROCEDURE decount_test ( p_id bigint )
DETERMINISTIC MODIFIES SQL DATA
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'Invoiced barcodes may not have accounting removed.';
IF (SELECT invoice_id
FROM accounted_barcodes
WHERE id = p_id
) THEN
CALL raise_error;
END IF;
DELETE FROM accounted_barcodes WHERE id = p_id;
END //
DELIMITER ;
Output:
输出：
call decount_test(123456);
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
回答by Ryan M
Why not just store a VARCHARin a declared INTEGERvariable?
为什么不将 a 存储VARCHAR在声明的INTEGER变量中？
DELIMITER $$ DROP FUNCTION IF EXISTS `raise_error` $$
CREATE FUNCTION `raise_error`(MESSAGE VARCHAR(255))
RETURNS INTEGER DETERMINISTIC BEGIN
DECLARE ERROR INTEGER;
set ERROR := MESSAGE;
RETURN 0;
END $$ DELIMITER ;
-- set @foo := raise_error('something failed'); -- or within a query
Error message is:
错误信息是：
Incorrect integer value: 'something failed' for column 'ERROR' at row 1
不正确的整数值：第 1 行的“ERROR”列的“某事失败”
It's not perfect, but it gives a pretty descriptive message and you don't have to write any extension DLLs.
它并不完美，但它提供了一个非常具有描述性的消息，并且您不必编写任何扩展 DLL。
回答by Patrick de Kleijn
In MySQL 5 you may raise an error by calling a stored procedure that does not exist (CALL raise_error) or passing an invalid value to a query (like null to a NOT NULL contrained field). Here is an interesting post by Roland Bouman on raising errors from within a MySQL function:
在 MySQL 5 中，您可能会通过调用不存在的存储过程 (CALL raise_error) 或将无效值传递给查询（如 null 到 NOT NULL 受限字段）来引发错误。这是 Roland Bouman 的一篇关于从 MySQL 函数中引发错误的有趣帖子：
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html
回答by Patrick de Kleijn
You can also call an existing function with an invalid number of arguments.
您还可以使用无效数量的参数调用现有函数。
回答by Jorge Niedbalski R.
You have to define exception handlers . Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
您必须定义异常处理程序。看看http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
