Variables and prepared statements: MySQL

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonStore values in variables, read and change server settings, and run parameterised queries with prepared statements.

Three kinds of variables

MySQL has three families of variables, and it helps to keep them apart.

Kind	Looks like	Lives	Set with
User-defined	`@total`	your connection	`SET @total = 5` or `SELECT ... INTO @total`
Local	`v_count`	inside one stored routine	`DECLARE`
System	`@@sql_mode`, `@@autocommit`	the server or your session	`SET GLOBAL` or `SET SESSION`

User variables

A user variable starts with @ and needs no declaration. Assign with SET or with := inside a query, and read it anywhere an expression is allowed:

sql

USE shop;
SET @min_price = 30;
SET @category = 1;
SELECT name, price FROM products WHERE price >= @min_price AND category_id = @category ORDER BY price;

result

+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| USB-C Hub                   |  39.00 |
| Mechanical Keyboard         |  89.50 |
| Noise-Cancelling Headphones | 199.99 |
+-----------------------------+--------+

Store a query result with SELECT ... INTO:

sql

SELECT MAX(price), COUNT(*) INTO @top_price, @product_count FROM products;
SELECT @top_price AS most_expensive, @product_count AS products;

result

+----------------+----------+
| most_expensive | products |
+----------------+----------+
|         199.99 |       15 |
+----------------+----------+

Variables are handy for scripts and for passing OUT values from procedures. They are private to your connection and disappear when it closes. An unset variable is NULL. Their type follows the last value assigned, and they are not safe for sharing logic between connections.

A classic use is numbering rows. (In MySQL 8, a window function is preferable.)

sql

SET @n = 0;
SELECT (@n := @n + 1) AS row_number_, name FROM products WHERE category_id = 4 ORDER BY name;

result

+-------------+------------------+
| row_number_ | name             |
+-------------+------------------+
|           1 | Resistance Bands |
|           2 | Water Bottle     |
|           3 | Yoga Mat         |
+-------------+------------------+

Local variables

Inside a stored procedure, function or trigger, you declare typed variables that disappear when the routine ends:

sql

DECLARE v_total DECIMAL(10,2) DEFAULT 0;
SET v_total = v_total + 10;

DECLARE statements must come at the start of the BEGIN ... END block, before any other statement.

System variables

System variables configure the server. Read them with @@:

sql

SELECT @@autocommit AS autocommit, @@transaction_isolation AS isolation;

result

+------------+-----------------+
| autocommit | isolation       |
+------------+-----------------+
|          1 | REPEATABLE-READ |
+------------+-----------------+

sql

SELECT @@sql_mode AS sql_mode;

result

+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

SET SESSION name = value changes a setting for your connection only. For example, relaxing the output of GROUP_CONCAT:

sql

SET SESSION group_concat_max_len = 4096;
SELECT @@session.group_concat_max_len AS limit_for_me;

result

+--------------+
| limit_for_me |
+--------------+
|         4096 |
+--------------+

SET GLOBAL changes the default for connections opened afterwards and needs administrator rights. It is lost when the server restarts, unless you use SET PERSIST, which also writes it to a configuration file. SHOW VARIABLES LIKE 'max_conn%'; lists variables by pattern, and SHOW STATUS reports counters (as you used in the indexes lesson).

Prepared statements

A prepared statement is a query compiled once and executed many times with different values. You write the statement with ? placeholders, PREPARE it, then EXECUTE it with values:

sql

PREPARE find_product FROM 'SELECT id, name, price FROM products WHERE category_id = ? AND price > ?';
SET @cat = 3, @min = 20;
EXECUTE find_product USING @cat, @min;
SET @cat = 4, @min = 10;
EXECUTE find_product USING @cat, @min;
DEALLOCATE PREPARE find_product;

result

+----+-------------------+-------+
| id | name              | price |
+----+-------------------+-------+
|  8 | Desk Lamp         | 32.00 |
| 10 | Standing Desk Mat | 55.00 |
+----+-------------------+-------+
+----+------------------+-------+
| id | name             | price |
+----+------------------+-------+
| 11 | Yoga Mat         | 27.50 |
| 12 | Resistance Bands | 14.99 |
| 13 | Water Bottle     | 12.00 |
+----+------------------+-------+

EXECUTE ... USING takes user variables only (not literals). DEALLOCATE PREPARE frees the statement; it also disappears when the connection closes.

There are two reasons to prefer prepared statements:

Safety. The values are sent separately from the SQL text, so the server treats them strictly as data. A value such as '; DROP TABLE products; -- stays a harmless string. Application drivers (PDO, mysql2, Python connectors) use the same mechanism through their own parameter syntax. The last lesson of this course demonstrates it.
Speed for repeats. The statement is parsed and planned once, then run many times.

Dynamic SQL

Placeholders can stand for values only. They cannot stand for a table name, column name or keyword. When those must vary, you have to build the SQL text yourself and PREPARE it from a string, which is dangerous with untrusted input:

sql

SET @col = 'price';
SET @sql = CONCAT('SELECT name, ', @col, ' FROM products ORDER BY ', @col, ' DESC LIMIT 3');
PREPARE dyn FROM @sql;
EXECUTE dyn;
DEALLOCATE PREPARE dyn;

result

+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
| Mechanical Keyboard         |  89.50 |
| Standing Desk Mat           |  55.00 |
+-----------------------------+--------+

Only do this with names you control or validate against an allow-list. Never concatenate user input into the string. (If you must accept an identifier, check it against a fixed list or quote it with QUOTE-like logic in application code.)

LIMIT with a variable

In plain SQL, LIMIT cannot take a variable, but a prepared statement can:

sql

PREPARE page FROM 'SELECT id, name FROM products ORDER BY id LIMIT ? OFFSET ?';
SET @size = 4, @skip = 8;
EXECUTE page USING @size, @skip;
DEALLOCATE PREPARE page;

result

+----+-------------------+
| id | name              |
+----+-------------------+
|  9 | Ceramic Mug Set   |
| 10 | Standing Desk Mat |
| 11 | Yoga Mat          |
| 12 | Resistance Bands  |
+----+-------------------+

Common mistakes

Expecting user variables to be shared between connections or to survive a reconnect.
Assuming a variable has a declared type. @x can hold a number and then a string.
Using a user variable and reading it in the same statement where it is also changed. The evaluation order is not guaranteed.
Putting a table name in a ? placeholder.
Building dynamic SQL from user input.
Forgetting DEALLOCATE PREPARE in long-lived connections.

Exercise

Using variables, find the average product price into @avg_price, then select the products priced above it. Then write a prepared statement that finds customers by country (?) and run it for 'India' and 'Japan'.

Show one possible answer

sql

SELECT AVG(price) INTO @avg_price FROM products;
SELECT name, price FROM products WHERE price > @avg_price ORDER BY price DESC;

PREPARE by_country FROM 'SELECT first_name, city FROM customers WHERE country = ?';
SET @c = 'India';
EXECUTE by_country USING @c;
SET @c = 'Japan';
EXECUTE by_country USING @c;
DEALLOCATE PREPARE by_country;

result

+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
| Mechanical Keyboard         |  89.50 |
| Standing Desk Mat           |  55.00 |
| Building Blocks Set         |  49.99 |
+-----------------------------+--------+
+------------+-----------+
| first_name | city      |
+------------+-----------+
| Asha       | Delhi     |
| Farid      | Mumbai    |
| Kavya      | Bengaluru |
+------------+-----------+
+------------+-------+
| first_name | city  |
+------------+-------+
| Hiro       | Tokyo |
+------------+-------+

Key points

User variables (@name) live for your connection and need no declaration. Local variables (DECLARE) exist only inside a stored routine.
System variables control the server and the session: SELECT @@sql_mode, SET SESSION ... or SET GLOBAL ... (global changes need privileges and are lost on restart unless persisted).
PREPARE stores a statement with ? placeholders, EXECUTE runs it with values, and DEALLOCATE PREPARE frees it.
Placeholders carry data, never SQL text. This is the foundation of protection against SQL injection.

Three kinds of variables

User variables

Local variables

System variables

Prepared statements

Dynamic SQL

LIMIT with a variable

Common mistakes

Exercise

Key points

Check yourself