Can I use assignment operator `:=` on a local variable in a SELECT statement?

Question

From my understanding the only difference between user variables @var and local variables like var is the latter are scoped between the BEGIN and END statements of a procedure whereas user variables exist for use in the connection as long as the connection remains open. So I would like to use local variables since I don't care if the variables exist after the procedure is done. Actually I would prefer they didn't exist outside of the procedure.

I am trying to use the following query:

DROP TABLE IF EXISTS my_test_table;
CREATE TABLE my_test_table(var INT DEFAULT 0);
INSERT INTO my_test_table VALUES (3);
DELIMITER //
DROP PROCEDURE IF EXISTS my_sp_test_function;
CREATE PROCEDURE my_sp_test_function() 
BEGIN
 DECLARE localVar INT DEFAULT 1;
 #SET @userVar = 1; ### If we swap this line for the one above the query works. ###
 SELECT
 localVar := t.var + 1 AS return_var
 #@userVar := t.var + 1 AS return_var
 FROM my_test_table AS t; 
END; //
DELIMITER ;
CALL my_sp_test_function();

but getting a syntax error near ":= t.var + 1". If we change the DECLARE localVar line to SET @userVar this exact query works. So is there a syntax fix I can make or a different technique I can use to save off a variable from a select statement while using local variables?

• Rick James: In the long run, := is being deprecated and subject to removal from the syntax.

Answer 1

Session variables are go

CREATE TABLE my_test_table(var INT DEFAULT 0);
INSERT INTO my_test_table VALUES (3);

CREATE PROCEDURE my_sp_test_function() 
BEGIN
 DECLARE localVar INT DEFAULT 1;
 #SET @userVar = 1; ### If we swap this line for the one above the query works. ###
 SELECT
 t.var + 1 AS return_var INTO localVar
 #@userVar := t.var + 1 AS return_var
 FROM my_test_table AS t;
 SELECT localVar;
END

CALL my_sp_test_function();

| localVar |
| -------: |
| 4 |
✓

db<>fiddle here

But Variables act different ly from @ sessionvaroables which you see when you add another row

CREATE TABLE my_test_table(var INT DEFAULT 0);
INSERT INTO my_test_table VALUES (3),(4);

CREATE PROCEDURE my_sp_test_function() 
BEGIN
 DECLARE localVar INT DEFAULT 1;
 SET @userVar = 1; ### If we swap this line for the one above the query works. ###
 SELECT
 @userVar := t.var + 1 AS return_var 
 #@userVar := t.var + 1 AS return_var
 FROM my_test_table AS t;
END

CALL my_sp_test_function();

| return_var |
| ---------: |
| 4 |
| 5 |

you need here a Limit as else you get an error and MySQL can't use tables as Datatype, because Oracle would loose a lot of paying customers, i f they really would add more functionslity

CREATE PROCEDURE my_sp_test_function2() 
BEGIN
 DECLARE localVar INT DEFAULT 1;
 #SET @userVar = 1; ### If we swap this line for the one above the query works. ###
 SELECT
 t.var + 1 AS return_var INTO localVar
 #@userVar := t.var + 1 AS return_var
 FROM my_test_table AS t
 ORDER BY var DESC
 LIMIT 1;
 SELECT localVar;
END

CALL my_sp_test_function2();

| localVar |
| -------: |
| 5 |

db<>fiddle here

• Michael Fulton: Thank you for the answer and the wonderful additional explanation.