mysql - IF ELSE 语句在 MySQL 中产生无效语法

Question

不确定为什么会返回无效语法:

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddColor`(
                            IN hexvalue varchar(7),
                          IN notes varchar(50))
    BEGIN
    IF hexvalue REGEXP '^#[0-9A-F]{6}$' THEN

       INSERT INTO data.colors(hexvalue,notes) VALUES (hexvalue,notes);

    ELSE

       SIGNAL SQLSTATE '400'
       SET MESSAGE_TEXT = 'Invalid hex value specified';

    END IF;
    END

写进去好像没有什么错误。

Answer 1

基于以下 official documentation:

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”).

尝试以下语法并将 SIGNAL SQLSTATE 值更改为 04000:

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddColor`(
                    IN hexvalue varchar(7),
                    IN notes varchar(50))
BEGIN
IF  (hexvalue REGEXP '^#[0-9A-F]{6}$') THEN

        INSERT INTO data.colors(`hexvalue`,`notes`) VALUES (hexvalue,notes);

    ELSE

        SIGNAL SQLSTATE '04000' SET MESSAGE_TEXT = 'Invalid hex value specified';

    END IF;
END;