过程参数上的 MySql 语法错误 [英] MySql syntax error on procedure parameter

问题描述

我正在尝试编写一个简单的过程，但在第一个参数处遇到语法错误.尽我所知，我正在遵循 CREATE PROCEDURE 的语法 正确.

I am trying to write a simple procedure but am encountering a syntax error at the first parameter. As best I can tell I'm following the syntax of CREATE PROCEDURE correctly.

我只能使用 phpMyAdmin 访问我的数据库.这是我尝试运行的创建脚本:

I am limited to accessing my database with phpMyAdmin. Here is the create script I'm trying to run:

DROP PROCEDURE IF EXISTS product_index_swap/
CREATE PROCEDURE product_index_swap (@id INT, @oldIndex INT, @newIndex INT)
BEGIN
    DECLARE @swapID;
    SET @swapID = (SELECT `id` FROM `product` WHERE `order_index` = @newIndex LIMIT 1);
    UPDATE `products` SET `order_index` = (CASE WHEN `id` = @id THEN @newIndex
                                                WHEN `id` = @swapID THEN @oldIndex END)
    WHERE `id` IN (@id, @swapID);
END

我正在使用 phpMyAdmin 上的选项将分隔符更改为/.

I am using the option on phpMyAdmin to change the delimiter to /.

我收到一个语法错误near '@id INT, @oldIndex INT....".我想我可能会遇到更多分隔符错误，因为我不完全清楚它们的范围.我相信如果这是问题所在，那么当它无法理解分号时，错误将出现在过程中的新行上，而不是在参数声明处.

I receive a syntax error "near '@id INT, @oldIndex INT....". I thought I may encounter more delimiter errors since I'm not entirely clear on the scope of them. I believe if that was the problem the error would be on a new line in the procedure when it failed to understand a semicolon, not at the parameters declaration.

推荐答案

您正在使用 Microsoft SQL Server 约定，将 @ 放在所有参数和局部变量之前.MySQL 不会这样做.

You're using the Microsoft SQL Server convention of putting @ before all the parameters and local variables. MySQL doesn't do this.

在 MySQL 语法中，过程参数没有符号.

In MySQL syntax, procedure parameters have no sigil.

参数通常声明为 IN 或 OUT 或 INOUT.

Also parameters are typically declared IN or OUT or INOUT.

CREATE PROCEDURE product_index_swap (IN id INT, IN oldIndex INT, IN newIndex INT)
BEGIN
    DECLARE swapID;
    ...

具有 @ 符号的 MySQL 变量是 会话变量.

MySQL variables that have the @ sigil are session variables.

另见:

• https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
• https://dev.mysql.com/doc/refman/5.7/en/declare-local-variable.html
• https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

这篇关于过程参数上的 MySql 语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！