16.1. User Variable Syntax

User variables are written as @var_name and may be set to an integer, real, string, or NULL value. In a SET statement, you can assign a value to a variable using either = or := as the assignment operator:

 mysql> SET @var1 = 'USA'; mysql> SET @var2 := 'GBR'; 

In other contexts, such as in a SELECT statement, use the := assignment operator (not the = operator):

 mysql> SELECT @var3 := 'CAN'; +----------------+ | @var3 := 'CAN' | +----------------+ | CAN            | +----------------+ 

A SET statement can perform multiple variable assignments, separated by commas:

 mysql> SET @var1 = 'USA', @var2 = 'GBR', @var3 = 'CAN'; 

If you refer to an uninitialized variable that has not been assigned a value explicitly, its value is NULL. In the following statement, @var4 is referenced without having been set previously:

 mysql> SELECT @var1, @var2, @var3, @var4; +-------+-------+-------+-------+ | @var1 | @var2 | @var3 | @var4 | +-------+-------+-------+-------+ | USA   | GBR   | CAN   | NULL  | +-------+-------+-------+-------+ 

User variables can be used in most contexts where expressions are allowed. However, they cannot be used where a literal value is required. Examples of this restriction include LIMIT, which requires literal integer arguments, and the filename in LOAD DATA INFILE, which must be a literal string.

User variables are specifically required when using EXECUTE to execute a prepared statement. Each data value given as a parameter to EXECUTE must be passed as a user variable. User variables also are used in LOAD DATA INFILE to hold data values read from a file that are to be transformed before being loaded into a table.

User variables are not the same as local variables that you declare in stored routines. The latter are created with a DECLARE statement and are not referred to with a leading '@' character. For more information, see Section 18.5.4, "Variables in Stored Routines."