MySQL Variables and Prepared Statements

MySQL distinguishes server system variables, local routine variables, and session user variables. Their scope and evaluation rules differ, so they should not be used interchangeably.

System and User Variables #

System variables use @@ and may have global, session, or both scopes:

SELECT @@GLOBAL.transaction_isolation, @@SESSION.sql_mode;
SET SESSION sql_safe_updates = 1;

Changing a global variable affects new sessions when the variable also has a session scope; it is not a way to change an existing connection’s transaction policy. A dynamic variable can change at runtime, whereas a read-only variable requires startup configuration. SET PERSIST changes the runtime global value and writes mysqld-auto.cnf; SET PERSIST_ONLY writes the persisted value without changing runtime state. Verify that a variable is persistable before using either form, and keep file ownership with the server rather than editing that generated file.

User variables use @name, are connection-local, and do not have a declared type:

SET @minimum_total = 100;
SELECT id FROM orders WHERE total >= @minimum_total;

Do not assign and read the same user variable within one expression or query to simulate procedural evaluation. MySQL does not guarantee an evaluation order that makes such queries reliable. Use window functions, common table expressions, stored-program local variables, or application code instead. The value’s type is derived when assigned, and prepared-statement references retain the variable type established when the statement is first prepared. Explicitly CAST values when the required SQL type is part of the contract.

Prepared Statements #

Client library prepared statements bind data separately from SQL syntax and should be the default for application queries. They prevent a value from being interpreted as injected SQL and allow the server to parse the statement structure separately.

Server-side SQL prepared statements follow a connection-scoped lifecycle: PREPARE parses one statement, EXECUTE runs it repeatedly, and DEALLOCATE PREPARE releases it. A second PREPARE with the same name first deallocates the old statement. Prepared statements are also discarded when the session ends. They are useful for administrative dynamic SQL when an identifier must be assembled. Parameter markers bind data values, not identifiers, keywords, or SQL fragments:

SET @sql = 'SELECT id FROM orders WHERE customer_id = ?';
PREPARE stmt FROM @sql;
SET @customer_id = 42;
EXECUTE stmt USING @customer_id;
DEALLOCATE PREPARE stmt;

Validate or allow-list any dynamic identifier before concatenating it into SQL. Parameter markers cannot stand for an arbitrary expression in every grammar position, and prepared statements are limited by max_prepared_stmt_count. Deallocate short-lived administrative statements and let application drivers manage their own prepared-statement handles.

References #

• Using System Variables
• User-Defined Variables
• PREPARE Statement