在 MySQL DECLARE 中 SELECT INTO 变量会导致语法错误吗？

我想选择一个单独的值到一个变量中:

DECLARE myvar INT(4);

——立即返回一些语法错误。

SELECT myvalue
FROM mytable
WHERE anothervalue = 1;

——返回一个整数

SELECT myvalue
INTO myvar
FROM mytable
WHERE anothervalue = 1;

——不工作，也尝试@myvar

可以在存储过程或函数之外使用 DECLARE 吗？

也许我只是没有得到用户变量的概念... 我只是尝试:

SELECT myvalue INTO @var FROM `mytable` WHERE uid = 1;
SELECT @var;

... 就像它应该工作的那样。但是如果我一次运行每个查询，我只得到@var NULL。

388865 次浏览
小开

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

小开

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:

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

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

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)
小开
最佳答案

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();
小开

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:

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:

SELECT @myvar:=myvalue
FROM mytable
WHERE anothervalue = 1;
小开

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

SELECT ... INTO @XYZ ...

You can NOT use FETCH INTO global variables like:

FETCH ... INTO @XYZ

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

小开

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. 

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

小开

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

小开

You can also use SET instead of DECLARE

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


SELECT myvar;
小开

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

小开

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.

小开

SELECT c1, c2, c3, ... INTO @v1, @v2, @v3,... FROM table_name WHERE condition;


提交答案