How to declare a variable in SQL script?

Question

Just installed sql on ubuntu 21.04.

$ mysql -V
mysql  Ver 8.0.26-0ubuntu0.21.04.3 for Linux on x86_64 ((Ubuntu))

Wrote a test.sql file:

declare @a as int=4

On executing mysql> source /home/home/test.sql on the command line, the following error is returned:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @a as int=4' at line 1

adding begin and end to the script doesn't help either. What is the fix?

Answer 1

First you need to declare the variable

DECLARE @MyVariable INT;

and then you can set it

SET @MyVariable = 1;

Answer 2

You can only use declared variables in stored procedure. In an ordinary SQL script, use user variables, which are names beginning with @. You don't specify a type, they can hold any type of value.

SET @a = 4;