How to declare a variable in MySQL?

We can declare a variable in MySQL with the help of SELECT and SET command. Before declaring a variable we need to prefix the symbol ‘@’

The syntax is as follows −

SELECT @ yourVariableName;

The symbol ‘@’ tells that it is a user defined variable or not. If there is no symbol, that would mean it is a local variable. Prefix with two ‘@@’ tells about the server system variables.

Here, we will learn about user defined variable. We can set some value to the variable with the help of SET command.

The syntax is as follows −

SET @yourVariableName=value;

Note − In the SELECT statement, the “yourVariableName” contains the NULL value and after using the SET command it contains the value which we have given.

Now, we will check by applying the above query. Firstly, let us declare a variable.

mysql> SELECT @engine;

After executing the above query, the variable gets NULL value initially. The following is the output −

+---------+
| @engine |
+---------+
| NULL    |
+---------+
1 row in set (0.00 sec)

Set some value to the variable with the help of SET command −

mysql> SET @engine='start';
Query OK, 0 rows affected (0.00 sec)

After that, we can check the value we have given above. For that, use the SELECT statement. The following is the query −

mysql> SELECT @engine;

After executing the above query, we will get the updated value.

The following is the output −

+---------+
| @engine |
+---------+
| start   |
+---------+
1 row in set (0.00 sec)

Now, we can update the variable value like this −

mysql> set @engine='close';
Query OK, 0 rows affected (0.00 sec)

Check the result is updated or not with the help of SELECT command::

mysql> select @engine;

After executing the above query, we will get the output as shown below −

+---------+
| @engine |
+---------+
| close   |
+---------+
1 row in set (0.00 sec)

From the above output it is clear that the @engine variable is updated with value ‘close’.