在 MySQL Workbench 中声明变量语法无效？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21464786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declare variable syntax invalid in MySQL Workbench?
提问by Roy Hinkley
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?
怎么了？
采纳答案by Angelo Saleh
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');
回答by mrDjouk
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','[email protected]','password','ROLE_USER');
SELECT
u.*
FROM
tbl_user u;
END &&
DELIMITER ;
