SELECT INTO Variable in MySQL DECLARE causes syntax error?

Question

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

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

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.

Answer 1

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

Answer 2

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)

Answer 3

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