在MySQL中选择变量声明导致语法错误?

I´d like to SELECT a single value into a variable. I´d tried to following:

我´想选择一个值到一个变量中。我´d尝试:

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?

是否可以在存储过程或函数之外使用声明?

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.

…这就像它´s应该工作。但是如果我每次运行每个查询，我就会得到@var NULL。

10 个解决方案

#1

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;

#2

In the end a stored procedure was the solution for my problem. Here´s what helped:

最后，存储过程是解决问题的方法。这´s帮助:

DELIMITER //
CREATE PROCEDURE test ()
  BEGIN
  DECLARE myvar DOUBLE;
  SELECT somevalue INTO myvar FROM mytable WHERE uid=1;
  SELECT myvar;
  END
  //

DELIMITER ;

call test ();

#3

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:

在存储过程中执行内联赋值会导致这些结果也被返回到resultset中。可以让人困惑。使用选择……使用多个变量的语法:

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，尽管这并不总是必要的。

#4

You can also use SET instead of DECLARE

您也可以使用SET而不是DECLARE

SET @myvar := (SELECT somevalue INTO myvar FROM mytable WHERE uid=1);

SELECT myvar;

#5

Per the MySQL docs DECLARE works only at the start of a BEGIN...END block as in a stored program.

根据MySQL文档声明，只能在开始时工作……在存储程序中结束块。

#6

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中不需要声明变量。变量的类型是在第一次分配值时自动确定的。它的类型可以是:整数、十进制、浮点数、二进制或非二进制字符串或空值。有关更多信息，请参阅用户定义的变量文档:

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

You can use SELECT ... INTO to assign columns to a variable:

您可以使用SELECT…将列分配给一个变量:

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)

#7

It is worth noting that despite the fact that you can SELECT INTO global variables like:

值得注意的是，尽管您可以选择全局变量，比如:

SELECT ... INTO @XYZ ...

选择……到@XYZ……

You can NOT use FETCH INTO global variables like:

您不能使用FETCH到全局变量，比如:

FETCH ... INTO @XYZ

取……到@XYZ

Looks like it's not a bug. I hope it will be helpful to someone...

看起来不是虫子。我希望这对某人有帮助……

#8

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 (Windows版本6.0.9修订版11421 build 1170) (MySQL Workbench Community, GPL)。我对以下选项没有问题。也许他们三年前就解决了这个问题。

/* 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.

以上所有选项都给了我一个正确的结果。

#9

You maybe miss the @ symbol before your value,like that select 'test' INTO @myValue;

您可能会在您的值之前漏掉@符号，比如将“test”选择为@ myvalue;

#10

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.

欢呼。

#1