Skip to content
Tutorials Center
My learning

Topics

Lesson 52 of 75 ยท Programmable Objects

User Variables and Prepared Statements

Use MySQL @user variables and run dynamic SQL safely with PREPARE, EXECUTE USING and DEALLOCATE PREPARE, including scope rules and injection pitfalls.

Not every piece of MySQL code lives inside a stored procedure with neat DECLAREd variables. In the command-line client, in a migration script or in a quick report you often need to remember a value between statements ("the id I just inserted", "the cutoff date") or to build a statement whose text isn't known until runtime ("count the rows in whichever table I name"). MySQL provides two features for this: user-defined variables, written @name, and server-side prepared statements, driven by PREPARE, EXECUTE and DEALLOCATE PREPARE.

This lesson covers both, plus the rules that trip people up. MySQL's @variable and PREPARE syntax is MySQL-specific, so those blocks are read-only. The runnable examples use SQLite to practice the underlying ideas (a parameter table standing in for variables).

User variables

A user variable starts with @. You don't declare it; assigning to it creates it. It lives for the current connection and disappears when that connection closes. Another connection can't see it.

SQL
SET @min_price = 20;
SET @label := 'big', @limit_rows = 5;      -- several at once; = or := both work in SET

SELECT @min_price;                         -- 20
SELECT title, price FROM books WHERE price >= @min_price;

Variables have no declared type. They take the type of the last value assigned (integer, decimal, string, binary string or NULL), and an unset variable is NULL, with no error.

Getting values into a variable

Besides SET, you can store a query result with SELECT ... INTO:

SQL
SELECT MAX(price) INTO @top_price FROM books;
SELECT COUNT(*), AVG(price) INTO @n, @avg FROM books;   -- the query must return one row

INSERT INTO books (title, price) VALUES ('New title', 12.50);
SET @new_id = LAST_INSERT_ID();           -- remember the generated key
INSERT INTO reviews (book_id, stars) VALUES (@new_id, 5);

That last pattern is the everyday use: capture an auto-increment id and use it in the follow-up inserts of a script. SELECT ... INTO @var errors out if the query returns more than one row, and leaves the variable unchanged (with a warning) when it returns none, so check ROW_COUNT() or FOUND_ROWS() if that matters.

The same idea, live

Standard SQL has no session variables, and SQLite has none either, but a one-row parameter table does the same job for practice. Here a "variable" table feeds several queries so the cutoff is written only once:

SQL A parameter table standing in for @variables

Press Run (or Ctrl + Enter) to see the result here.

In MySQL you would write SET @min_price = 20; once and use @min_price everywhere, which is shorter, but the idea is identical.

Rules and pitfalls

  • Scope is the connection. With connection pooling, a variable set by one request can still be there for the next one that reuses the connection. Don't rely on @x being NULL at the start of a request; set it explicitly.
  • They are not part of the schema. A view can't contain a user variable, and variables don't exist inside stored-program parameters or DECLARE (those are local variables, a separate feature covered in stored procedures).
  • Don't assign inside a query and read in the same one. Constructs such as SELECT @n := @n + 1, col FROM t were popular for row numbers, but the evaluation order is not guaranteed and assignment with := outside SET is deprecated. In MySQL 8.0, use ROW_NUMBER() and window functions instead.
  • Case rules. Variable names are not case-sensitive. A name can contain letters, digits, ., _ and $.
  • Replication. Statement-based replication has restrictions around user variables; row-based (the default) does not.

Prepared statements

Normally the server parses your text every time you send it. A prepared statement splits the work in two: you send the SQL once with ? placeholders, the server parses it, and then you execute it as often as you like with different values. That brings two advantages: the values are never parsed as SQL, which is the defense against SQL injection, and a repeated statement skips parsing.

Most applications use prepared statements through a driver (see MySQL from PHP), where ? or :name is handled for you. MySQL also exposes the same facility in plain SQL, which is useful in scripts and stored procedures:

SQL
PREPARE find_books FROM
  'SELECT id, title, price FROM books WHERE price >= ? AND stock > ? ORDER BY price';

SET @min = 20, @stock = 0;
EXECUTE find_books USING @min, @stock;

SET @min = 40;
EXECUTE find_books USING @min, @stock;

DEALLOCATE PREPARE find_books;

The three statements are:

Statement What it does
PREPARE name FROM 'sql' Parses the text (a string literal or a user variable) and stores it under name for this session.
EXECUTE name [USING @a, @b] Runs it, binding the listed user variables to the ? markers in order.
DEALLOCATE PREPARE name (or DROP PREPARE) Frees it. Prepared statements are also discarded when the connection ends.

Notes:

  • USING accepts only user variables, not literals or expressions. That's why you SET @min = 40 first.
  • The number of ? must equal the number of variables in USING, otherwise error 1210.
  • Preparing a name that already exists silently replaces the old statement.
  • Each session may hold a limited number of prepared statements (max_prepared_stmt_count, 16,382 by default across the server), so deallocate what you don't reuse.
  • Most statements can be prepared (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, CALL, and more), but not every one, and a prepared statement is a single statement, not a script.

Where placeholders are allowed

A placeholder stands for a value, never for a name. This is the rule that forces people into dynamic SQL:

SQL
-- Fine: values
SELECT * FROM books WHERE price > ? AND title LIKE ? LIMIT ?;

-- Not allowed: table names, column names, keywords, ORDER BY direction
SELECT * FROM ? WHERE ...;
SELECT * FROM books ORDER BY ?;      -- sorts by the constant, which does nothing useful

Since MySQL 5.5, LIMIT ? and OFFSET ? are allowed in prepared statements, which makes pagination straightforward.

Dynamic SQL: building the statement text

When the structure of the query changes, you build the string yourself with CONCAT, then prepare it. Here is a classic: count the rows of a table whose name arrives as a variable.

SQL
SET @table = 'books';
SET @sql = CONCAT('SELECT COUNT(*) AS n FROM `', @table, '`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The PREPARE statement accepts a user variable holding the SQL text, which is how dynamic SQL works in MySQL. Combine the two techniques when you need both a dynamic name and safe values:

SQL
SET @sql = CONCAT('SELECT id, title FROM `', @table, '` WHERE price > ?');
PREPARE stmt FROM @sql;
SET @p = 20;
EXECUTE stmt USING @p;
DEALLOCATE PREPARE stmt;

Names are not parameters, so you must validate them yourself. Concatenating an identifier from user input is injection waiting to happen. Never concatenate a table or column name that came from outside. Check it against a whitelist (for example information_schema.TABLES), or use QUOTE() for values and REPLACE(name, '', '``')for identifiers. Values should always go through?`.

Dynamic SQL in a stored procedure

The most common home for PREPARE is a stored procedure that takes a table or column name. Inside a procedure, PREPARE still needs a user variable (or a literal) for the statement text, not a local variable:

SQL
DELIMITER $$
CREATE PROCEDURE count_rows(IN tbl VARCHAR(64))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown table';
  END IF;

  SET @sql = CONCAT('SELECT COUNT(*) AS n FROM `', tbl, '`');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

CALL count_rows('books');

The whitelist check against information_schema guards the one place a value is turned into an identifier. Note that dynamic SQL can't be used in stored functions or triggers, only in procedures.

Prepared statements and the plan

A prepared statement is parsed once, but MySQL does not freeze its plan. The optimizer re-checks the plan each time you execute if table definitions or statistics changed (the statement is "re-prepared" automatically). So a prepared statement is never slower to plan than the same text sent directly, and it avoids repeated parsing, which matters in tight loops. The bigger win is safety: with ? the data can never change the shape of the SQL. Drivers often emulate preparation on the client (PDO does this by default), so check your driver's setting if you rely on true server-side preparation.

When to use which

  • User variables for glue in scripts and the command-line client: remembering ids, thresholds and dates between statements.
  • Prepared statements through a driver for application code: the normal, safe path for every query with outside data.
  • PREPARE in SQL only when you truly need dynamic SQL inside the database, for example an administrative procedure that loops over table names.
  • Local variables (DECLARE) inside stored programs for anything that lives only within that program.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How long does a MySQL user variable such as @total live?

    User variables are per connection. They disappear when it closes and are invisible to other connections.
  2. 2. Which line correctly binds a value in EXECUTE?

    USING takes only user variables, so assign the value with SET first.
  3. 3. Which of these can a ? placeholder NOT replace?

    Placeholders stand for values. Table and column names must be concatenated into the SQL text and validated yourself.
  4. 4. What should you do before concatenating a table name into dynamic SQL?

    Identifiers cannot be bound as parameters, so the only protection is validating them against known names.
  5. 5. What does DEALLOCATE PREPARE do?

    It releases the stored statement, which counts against max_prepared_stmt_count while it exists.

Summary

  • @name variables are per-connection, untyped, created on first assignment and NULL when unset; fill them with SET, SELECT ... INTO or LAST_INSERT_ID().
  • Avoid assigning and reading variables in the same query; use window functions for row numbering.
  • PREPARE, EXECUTE ... USING @vars and DEALLOCATE PREPARE give plain SQL access to server-side prepared statements.
  • Placeholders replace values only; for dynamic table or column names, build the text with CONCAT and validate the name first.
  • Dynamic SQL belongs in procedures, not functions or triggers, and prepared statements are the safe default for application queries.

Next: Cursors and Error Handling, where stored programs loop over rows and react to failures.