MySQL DECLARE 中的 SELECT INTO 变量导致语法错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3075147/
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
SELECT INTO Variable in MySQL DECLARE causes syntax error?
提问by Matt Bannert
I′d like to SELECT a single value into a variable. I′d tried to following:
我想将单个值 SELECT 到一个变量中。我试图遵循：
DECLARE myvar INT(4);
-- immediately returns some syntax error.
-- 立即返回一些语法错误。
SELECT myvalue
FROM mytable
WHERE anothervalue = 1;
-- returns a single integer
-- 返回一个整数
SELECT myvalue
INTO myvar
FROM mytable
WHERE anothervalue = 1;
-- does not work, also tried @myvar
-- 不工作，也试过@myvar
Is possible to use DECLARE outside of stored procedures or functions?
是否可以在存储过程或函数之外使用 DECLARE？
Maybe I just dont get the concept of user variables... I just tried:
也许我只是没有得到用户变量的概念......我只是试过：
SELECT myvalue INTO @var FROM `mytable` WHERE uid = 1;
SELECT @var;
...which worked just like it′s supposed to. But if I run each query at a time i just get @var NULL.
......就像它应该的那样工作。但是如果我一次运行每个查询，我只会得到@var NULL。
采纳答案by Matt Bannert
In the end a stored procedure was the solution for my problem. Here′s what helped:
最后，存储过程是我问题的解决方案。以下是帮助：
DELIMITER //
CREATE PROCEDURE test ()
BEGIN
DECLARE myvar DOUBLE;
SELECT somevalue INTO myvar FROM mytable WHERE uid=1;
SELECT myvar;
END
//
DELIMITER ;
call test ();
回答by Tim Gautier
I ran into this same issue, but I think I know what's causing the confusion. If you use MySql Query Analyzer, you can do this just fine:
我遇到了同样的问题，但我想我知道是什么导致了混乱。如果您使用 MySql 查询分析器，则可以很好地执行此操作：
SELECT myvalue
INTO @myvar
FROM mytable
WHERE anothervalue = 1;
However, if you put that same query in MySql Workbench, it will throw a syntax error. I don't know why they would be different, but they are. To work around the problem in MySql Workbench, you can rewrite the query like this:
但是，如果将相同的查询放在 MySql Workbench 中，则会引发语法错误。我不知道为什么他们会不同，但他们是。要解决 MySql Workbench 中的问题，您可以像这样重写查询：
SELECT @myvar:=myvalue
FROM mytable
WHERE anothervalue = 1;
回答by Garr Godfrey
These answers don't cover very well MULTIPLE variables.
这些答案并没有很好地涵盖多个变量。
Doing the inline assignment in a stored procedure causes those results to ALSO be sent back in the resultset. That can be confusing. To using the SELECT...INTO syntax with multiple variables you do:
在存储过程中执行内联分配会导致这些结果也被发送回结果集中。这可能会令人困惑。要对多个变量使用 SELECT...INTO 语法，您可以：
SELECT a, b INTO @a, @b FROM mytable LIMIT 1;
The SELECT must return only 1 row, hence LIMIT 1, although that isn't always necessary.
SELECT 必须只返回 1 行，因此 LIMIT 1，尽管这并不总是必要的。
回答by Somwang Souksavatd
You can also use SET instead of DECLARE
您也可以使用 SET 而不是 DECLARE
SET @myvar := (SELECT somevalue INTO myvar FROM mytable WHERE uid=1);
SELECT myvar;
回答by Dan U.
Per the MySQL docsDECLARE works only at the start of a BEGIN...END block as in a stored program.
根据MySQL 文档，DECLARE 仅在 BEGIN...END 块的开头工作，就像在存储程序中一样。
回答by Mike
You don't need to DECLARE a variable in MySQL. A variable's type is determined automatically when it is first assigned a value. Its type can be one of: integer, decimal, floating-point, binary or nonbinary string, or NULL value. See the User-Defined Variables documentation for more information:
您不需要在 MySQL 中声明变量。变量的类型在第一次赋值时自动确定。它的类型可以是以下之一：整数、十进制、浮点、二进制或非二进制字符串，或 NULL 值。有关更多信息，请参阅用户定义的变量文档：
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
You can use SELECT ... INTO to assign columns to a variable:
您可以使用 SELECT ... INTO 将列分配给变量：
http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html
http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html
Example:
例子：
mysql> SELECT 1 INTO @var;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @var;
+------+
| @var |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
回答by b.b3rn4rd
It is worth noting that despite the fact that you can SELECT INTOglobal variables like:
值得注意的是，尽管您可以使用SELECT INTO全局变量，例如：
SELECT ... INTO @XYZ ...
SELECT ... INTO @XYZ ...
You can NOTuse FETCH INTOglobal variables like:
您不能使用FETCH INTO全局变量，例如：
FETCH ... INTO @XYZ
FETCH ... INTO @XYZ
Looks like it's not a bug. I hope it will be helpful to someone...
看起来这不是一个错误。我希望它会对某人有所帮助......
回答by Tman
I am using version 6 (MySQL Workbench Community (GPL) for Windows version 6.0.9 revision 11421 build 1170) on Windows Vista. I have no problem with the following options. Probably they fixed it since these guys got the problems three years ago.
我在 Windows Vista 上使用版本 6（MySQL Workbench Community (GPL) for Windows version 6.0.9 revision 11421 build 1170）。我对以下选项没有问题。可能他们修复了它，因为这些人在三年前遇到了问题。
/* first option */
SELECT ID
INTO @myvar
FROM party
WHERE Type = 'individual';
-- get the result
select @myvar;
/* second option */
SELECT @myvar:=ID
FROM party
WHERE Type = 'individual';
/* third option. The same as SQL Server does */
SELECT @myvar = ID FROM party WHERE Type = 'individual';
All option above give me a correct result.
上面的所有选项都给了我一个正确的结果。
回答by emmanuel
For those running in such issues right now, just try to put an alias for the table, this should the trick, e.g:
对于那些现在遇到此类问题的人，只需尝试为表添加别名，这应该是诀窍，例如：
SELECT myvalue
INTO myvar
FROM mytable x
WHERE x.anothervalue = 1;
It worked for me.
它对我有用。
Cheers.
干杯。
回答by HiMan
You maybe miss the @ symbol before your value,like that select 'test' INTO @myValue;
您可能会错过值之前的 @ 符号，就像那样select 'test' INTO @myValue；
