SELECT INTO Variable in MySQL DECLARE causing a syntax error?

Question

I would like to select one value in a variable. I have tried:

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

Can I use DECLARE outside stored procedures or functions?

Maybe I just don’t understand the concept of user variables ... I just tried:

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

... who worked the way he should. But if I run each request at a time, I just get @var NULL.

Answer 1

In the end, the stored procedure was the solution to my problem. Here is 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 (); 

Answer 2

I ran into the same problem, but I think I know what causes confusion. If you use MySql Query Analyzer, you can do it just fine:

 SELECT myvalue INTO @myvar FROM mytable WHERE anothervalue = 1; 

However, if you put the same query in the MySql Workbench, it will throw a syntax error. I do not know why they will be different, but they are. To work around the problem in MySql Workbench, you can rewrite the query as follows:

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

Answer 3

These answers do not reflect MULTIPLE variables very well.

Executing the built-in job in the stored procedure causes these results to be sent back to the result set. This can be confusing. To use the SELECT ... INTO syntax with multiple variables, you do:

 SELECT a, b INTO @a, @b FROM mytable LIMIT 1; 

SELECT should only return 1 row, so LIMIT is 1, although this is not always necessary.

Answer 4

You can also use SET instead of DECLARE

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

Answer 5

Per documents docs DECLARE only works at the beginning of the BEGIN ... END block, as in a saved program.

Answer 6

You do not need a declare variable in MySQL. The type of a variable is determined automatically when it is first assigned a value. Its type can be one of the following: integer, decimal, floating, binary or non-binary string, or NULL value. See the documentation for user variables 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) 

Answer 7

It is worth noting that, despite the fact that you can SELECT INTO global variables, such as:

SELECT ... INTO @XYZ ...

You can use NOT global FETCH INTO variables, such as:

FETCH ... INTO @XYZ

This doesn't seem to be a bug . I hope this will be useful to someone ...

Answer 8

I am using version 6 (MySQL Workbench Community (GPL) for Windows version 6.0.9, version 11421 build 1170) on Windows Vista. I have no problem with the following options. They probably fixed it, as these guys got 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 parameters above give me the correct result.

Answer 9

You may be missing the @ symbol in front of your value, for example select 'test' INTO @myValue ;

Answer 10

For those working on such issues right now, just try putting an alias for the table, this should be a trick, for example:

SELECT myvalue INTO myvar FROM mytable x WHERE x.anothervalue = 1;

It worked for me.

Greetings.