MySQL - User-Defined Variables

In MySQL, you can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.

Syntax

The syntax for creating a user-defined variable in MySQL is given below:

SET @var_name = expr|value;

The variable name var_name consists of alphanumeric characters, ., _, and $. It can contain other characters also if it is quoted as a string or identifier, for example: @'my-var', @"my-var", or @`my-var`.

For SET, either = or := can be used as the assignment operator.

Example 1:

The example below demonstrates how to create a variable in MySQL:

mysql> SET @name = "John";
mysql> SELECT @name;
+-------+
| @name |
+-------+
| John  |
+-------+

mysql> SET @name = "Marry";
mysql> SELECT @name;
+-------+
| @name |
+-------+
| Marry |
+-------+

Example 2:

Consider a database table called Employee with the following records:

In the query below, a user defined variable is used to select and filter the data:

SET @min_salary = 2800, @max_age = 30;
SELECT * FROM Employee
WHERE Salary > @min_salary AND Age < @max_age;

This result of the following code will be:

Declare the type of user-defined variable

In MySQL, type of the user-defined variables cannot be declared. The only way to force their type is using CAST() or CONVERT():

SET @str = CAST(123 AS CHAR(5));

If a variable has not been used yet, its value will be NULL:

SELECT @x IS NULL;
+------------+
| @x IS NULL |
+------------+
|          1 |
+------------+