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

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;