The value stored in a user-defined variable can be referenced by another statement. This way, you can pass values from one statement to another.
User variables are written in the @var_name following format. var_name indicates the variable name and can contain letters, digits, periods (.), underscores (_), and dollar signs ($). If you quote a user variable name as a string or identifier, such as @'my-obvar', @"my-obvar", or @`my-obvar`, the name can contain other characters. User variable names are case-insensitive and can contain a maximum of 64 characters in length.
User-defined variables are session-specific. A user variable defined by a client is invisible or inaccessible to other clients. When a client exits, the client automatically releases all variables for sessions of the client.
You can execute the SET statement to set user-defined variables:
SET @var_name = expr [, @var_name = expr] ...
For more information, see SET.
You can use = or := as an assignment operator in a SET statement.
The following data types are supported for assigning values to user variables: integer, decimal, floating point, binary or non-binary string, or NULL. The precision or scale of the value is not retained in the assignment of decimal and real values. Values of other data types are converted to the supported data types. For example, values of the temporal or spatial data type are converted to binary strings.
To read a user-defined variable, execute the SELECT statement.
If a user variable is assigned a non-binary (character) string value, the variable has the same character set and collation as the string. A hexadecimal or bit value assigned to a user variable is processed as a binary string. To assign a hexadecimal or bit value to a user variable as a number, add a zero or use the CAST(... AS UNSIGNED) function:
obclient> SET @v1 = X'43';
obclient> SET @v2 = X'43'+0;
obclient> SET @v3 = CAST(X'43' AS UNSIGNED);
obclient> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| C | 67 | 67 |
+------+------+------+
1 row in set
obclient> SET @v1 = b'1000011';
obclient> SET @v2 = b'1000011'+0;
obclient> SET @v3 = CAST(b'1000011' AS UNSIGNED);
obclient> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| C | 67 | 67 |
+------+------+------+
1 row in set
If the value of a user variable is selected in a result set, the value is returned to the client as a string. If an uninitialized variable is referenced, its value is NULL and the data type is string.
We recommend that you do not assign a value to a user variable in a statement and read the value in the same statement, except for the SET statement.