mysql - 在带有 SQLSTATE 的 MySQL 中使用变量作为错误代码

Question

我正在尝试使用名为“error_messages”的过程和错误消息表来管理我自己在 MySQL 中的错误。

我如何设法在该表中选择特定的错误消息？

我想做这样的事情。

DELIMITER $$

CREATE PROCEDURE throw_error_test (divisor INT)
BEGIN
  SET @errorCode = '45123';
  IF divisor = 0 THEN
    SIGNAL SQLSTATE @errorCode
    SET MESSAGE_TEXT = SELECT message 
                       FROM error_messages
                       WHERE id = @errorCode;
  END IF;
END;

感谢帮助

Answer 1

根据 SIGNAL Syntax 记录:

The condition_value in a SIGNAL statement indicates the error value to be returned. It can be an SQLSTATE value (a 5-character string literal) or a condition_name that refers to a named condition previously defined with DECLARE ... CONDITION (see Section 13.6.7.1, “DECLARE ... CONDITION Syntax”).

[ deletia ]

Valid simple_value_specification designators can be specified using stored procedure or function parameters, stored program local variables declared with DECLARE, user-defined variables, system variables, or literals.

因此，对于MESSAGE_TEXT，您可以使用用户变量——但我更喜欢存储的程序局部变量；但是不能用变量指定 SQLSTATE:

CREATE PROCEDURE throw_error_test (divisor INT)
BEGIN
  DECLARE _messageText VARCHAR(128);

  IF divisor = 0 THEN
    SELECT message
    INTO   _messageText
    FROM   error_messages
    WHERE  id = '45123';

    SIGNAL SQLSTATE '45123' SET MESSAGE_TEXT = _messageText
  END IF;

END

为避免疑义，下划线 _ 前缀纯粹是我为避免歧义而采用的约定——它们不是局部变量名称的必要部分。