MySQL User Variables: SET @variable, SELECT INTO

A user-defined variable stores a value for the duration of your connection. Its name starts with @.

query.sql

SQL

1
2
3

SET @tax_rate = 0.18;
SET @shop = 'Paper & Co';
SELECT @shop AS shop, ROUND(100 * (1 + @tax_rate), 2) AS price_with_tax;

Output

OUTPUT

+------------+----------------+
| shop       | price_with_tax |
+------------+----------------+
| Paper & Co |         118.00 |
+------------+----------------+
1 row in set

You do not declare it. It exists once you assign it.

Assigning

query.sql

SQL

SET @a = 10, @b = 20;
SET @sum = @a + @b;
SELECT @sum AS sum_value;
SELECT COUNT(*) INTO @product_count FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS t;
SELECT @product_count AS counted;

Output

OUTPUT

+-----------+
| sum_value |
+-----------+
|        30 |
+-----------+
1 row in set

+---------+
| counted |
+---------+
|       3 |
+---------+
1 row in set

SET @x = ... assigns a value. SELECT ... INTO @x stores the result of a query that returns one row. A query that returns more than one row gives an error.

Using variables in queries

query.sql

SQL

CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), price DECIMAL(8,2));
INSERT INTO products (name, price) VALUES ('Notebook', 80), ('Pen', 15), ('Bag', 999);

SET @limit_price = 100;
SELECT name, price FROM products WHERE price < @limit_price ORDER BY price;

Output

OUTPUT

+----------+-------+
| name     | price |
+----------+-------+
| Pen      | 15.00 |
| Notebook | 80.00 |
+----------+-------+
2 rows in set

The scope: your session only

A user variable belongs to your connection. Another connection cannot see it, and it disappears when you disconnect. With connection pooling, a variable set by one request may still be there when the connection is reused by the next request, so set what you need at the start and never rely on a leftover value.

Types and NULL

A variable takes the type of the value it holds. An unset variable is NULL:

query.sql

SQL

1
2
3

SELECT @never_set AS unset_value, @never_set IS NULL AS is_null;
SET @text = '5';
SELECT @text + 1 AS text_as_number, CONCAT(@text, 1) AS text_joined;

Output

OUTPUT

+-------------+---------+
| unset_value | is_null |
+-------------+---------+
| NULL        |       1 |
+-------------+---------+
1 row in set

+----------------+-------------+
| text_as_number | text_joined |
+----------------+-------------+
|              6 | 51          |
+----------------+-------------+
1 row in set

Variables in LIMIT and in prepared statements

Plain LIMIT @n does not work in MySQL (variables are not allowed there in a normal statement). A prepared statement can use them:

query.sql

SQL

SET @n = 2;
PREPARE stmt FROM 'SELECT name, price FROM products ORDER BY price LIMIT ?';
EXECUTE stmt USING @n;
DEALLOCATE PREPARE stmt;

Output

OUTPUT

Statement prepared

+----------+-------+
| name     | price |
+----------+-------+
| Pen      | 15.00 |
| Notebook | 80.00 |
+----------+-------+
2 rows in set

(Prepared Statements covers PREPARE properly.)

Assigning inside a SELECT with := (deprecated)

Older code writes a row counter like this:

query.sql

SQL

1
2

SET @row = 0;
SELECT @row := @row + 1 AS row_no, name FROM products ORDER BY id;

Output

OUTPUT

+--------+----------+
| row_no | name     |
+--------+----------+
|      1 | Notebook |
|      2 | Pen      |
|      3 | Bag      |
+--------+----------+
3 rows in set

It worked, but the order of evaluation was not guaranteed, and MySQL 8.0.13 deprecated setting a variable inside a statement other than SET. Use the window function instead:

query.sql

SQL

SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_number_value, name FROM products ORDER BY id;

Output

OUTPUT

+------------------+----------+
| row_number_value | name     |
+------------------+----------+
|                1 | Notebook |
|                2 | Pen      |
|                3 | Bag      |
+------------------+----------+
3 rows in set

Local variables vs user variables

Inside a stored procedure you declare local variables with DECLARE. They have a type and a scope limited to that block. They do not start with @ (Stored Procedures).

System variables

@@name (two at-signs) is a system variable, a server setting (System Functions). @name (one at-sign) is your own.

When to use them

Quick scripts and one-off work in the client.
Passing a value to a prepared statement.
Keeping LAST_INSERT_ID() for several following inserts (Results of an INSERT).

Application code usually keeps values in the application’s own variables and passes them as parameters.

warning

Typing @ twice

SELECT @@my_value looks for a system variable and fails. A user variable has one @.