mysql - 另一个 MySQL 语法错误

Question

这不是我通常遇到的问题，但我现在不明白。我尝试使用反引号、双引号、不带反引号……

您的 SQL 语法有误；查看与您的 MySQL 服务器版本对应的手册，了解在第 7 行的 '' 附近使用的正确语法

CREATE PROCEDURE mapping(
    p_object INT(10),
    brand VARCHAR(255),
    model VARCHAR(255))
BEGIN
    INSERT INTO `object_brand_model_mapping`
    SET `object`=p_object, `brandNameNormalized`=brand, `modelNameNormalized`=model;
END;

我也试过

INSERT INTO `object_brand_model_mapping`
    (`object`, `brandNameNormalized`, `modelNameNormalized`)
VALUES
    (p_object, brand, model);

产生完全相同的错误。

我不知道这是怎么回事。是否有任何特殊规则适用于过程中的 INSERT？

所有列都存在。

Answer 1

解析器因在定义过程和过程内容之间有一个共同的分隔符而感到困惑。要解决此问题，请在创建过程之前定义一个不同的定界符:

DELIMITER $$

CREATE PROCEDURE mapping(
    p_object INT(10),
    brand VARCHAR(255),
    model VARCHAR(255))
BEGIN
    INSERT INTO `object_brand_model_mapping` (`object`, `brandNameNormalized`, `modelNameNormalized`)
        VALUES (p_object, brand, model);
END$$

DELIMITER ;

来自 Alex Silverstein's article on the subject :

The next step is to change the default MySQL script parser’s delimiter from semicolon (;) to double-dollar sign ($$). The reason you do this is so that the semicolons after each statement in the body of the routine are not interpreted by the parser as meaning the end of the CREATE PROCEDURE statement. This is because the entire CREATE PROCEDURE block, from CREATE PROCEDURE to END is actually a single statement that must be executed by itself. Were it not for the delimiter change, the script would break, since there each statement inside BEGIN and END would execute individually. Note that you can use a variety of non-reserved characters to make your own custom delimiter.