在MySQL Workbench中声明变量语法无效?

时间:2021-08-27 02:24:21

I am trying to create and set a variable:

我正在尝试创建并设置变量:

DECLARE myId INT;
SET myId = 5;

However, I am getting invalid syntax complaint in MySQL Workbench:

但是,我在MySQL Workbench中收到了无效的语法投诉:

SQL syntax error near 'DECLARE myId INT;'

'DECLARE myId INT'附近的SQL语法错误

I have tried the following variants:

我尝试了以下变种:

DECLARE myId INT(4);
SET myId = 5;

DECLARE @myId INT;
SET @myId = 5;

DECLARE @myId INT(4);
SET @myId = 5;

What is wrong?

哪里不对?

2 个解决方案

#1


4  

As in the comment says Declare is only valid into stored programs like procedures, functions. here you have an example of a store procedure and its call.

正如在评论中所说,Declare仅对程序,函数等存储程序有效。这里有一个存储过程及其调用的示例。

DELIMITER $$

CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE myId INT;


  SET myId = 5;
  SELECT CONCAT(xname,' -- ',myId);
END;
$$

DELIMITER ;

call sp1('MY NAME');

#2


1  

I experienced the same problem. The variables must be declared at the beginning of the script.

我遇到了同样的问题。必须在脚本的开头声明变量。

DELIMITER &&

DROP PROCEDURE IF EXISTS PS_HANDLERS;

CREATE PROCEDURE PS_HANDLERS(IN ID_USER INT, OUT isError INT)
    BEGIN
        DECLARE USER_EMAIL VARCHAR(50);
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
            BEGIN
                SET IsError = 1;
            END;

        SET USER_EMAIL = CONCAT(RAND(),'@',RAND(),'.com');
        SET isError = 0;



        INSERT INTO tbl_user VALUES(ID_USER, 'ipsum','lorem','ipsum@lorem.com','password','ROLE_USER');

        SELECT
            u.*
        FROM 
            tbl_user u;
    END &&

DELIMITER ;

#1


4  

As in the comment says Declare is only valid into stored programs like procedures, functions. here you have an example of a store procedure and its call.

正如在评论中所说,Declare仅对程序,函数等存储程序有效。这里有一个存储过程及其调用的示例。

DELIMITER $$

CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE myId INT;


  SET myId = 5;
  SELECT CONCAT(xname,' -- ',myId);
END;
$$

DELIMITER ;

call sp1('MY NAME');

#2


1  

I experienced the same problem. The variables must be declared at the beginning of the script.

我遇到了同样的问题。必须在脚本的开头声明变量。

DELIMITER &&

DROP PROCEDURE IF EXISTS PS_HANDLERS;

CREATE PROCEDURE PS_HANDLERS(IN ID_USER INT, OUT isError INT)
    BEGIN
        DECLARE USER_EMAIL VARCHAR(50);
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
            BEGIN
                SET IsError = 1;
            END;

        SET USER_EMAIL = CONCAT(RAND(),'@',RAND(),'.com');
        SET isError = 0;



        INSERT INTO tbl_user VALUES(ID_USER, 'ipsum','lorem','ipsum@lorem.com','password','ROLE_USER');

        SELECT
            u.*
        FROM 
            tbl_user u;
    END &&

DELIMITER ;