How to declare a variable in MySQL?
Declare a variable in MySQL with SET
like this:
Or use SELECT
to do the same:
Either way, you've got @var
storing 'value'
.
Scope: Session vs Local Variables
In MySQL, variable scope matters. There are session and local variables.
Session Variables: @var
Session variables are user-defined and are specific to the session they're created in. Use the SET
or SELECT
statement to declare and assign values.
Remember to precede the variable name with @
.
Local Variables: var
Local variables are declared within stored procedures or BEGIN ... END
blocks. They use DECLARE
and don't need the @
prefix.
Local variables scope are bounded to the block they're declared in.
Use of Server Settings with SET GLOBAL/SESSION
If you need to dynamically adjust server settings, use SET GLOBAL
or SET SESSION
.
You can view these settings using SHOW VARIABLES
or by selecting a specific variable SELECT @@var_name
.
Working with User-Defined Variables
User-defined variables are not only session-bound, but also loosely typed, making them flexible for various data manipulations.
Directly Assigning Values
You can directly assign values using SET
:
Select Values into Variables
Assign values via SELECT
:
Storing Query Results
Save results of SELECT queries into a variable:
Using Variables in WHERE Clause
variables can be used in the WHERE
clause for dynamic filtering.
Handling NULLs
Handle potential NULL
s by using IFNULL(@var, default_value);
.
Variables in Triggers
To update fields in triggers, variables are there for you:
Advanced Tips
While the fundamentals are widely known, mastering the nuances can give you an edge in handling complex situations.
Multiple Parameter Assignment
Assign values to an entire list variables in a single SET
statement:
Prepared Statements
Take advantage of user variables in dynamic PREPARE
statements:
Mastering Joins
Using variables in JOIN conditions requires care to avoid full table scans:
Was this article helpful?