User Defined Variables

User-defined variables refer to values that users can store in custom variables using SQL statements, and these variables can be referenced by other SQL statements. This approach facilitates value passing and simplifies SQL writing.

Usage

User-defined variable takes the form: @varname, where the variable name consists of letters, numbers, ".", "", "¥", and "$". However, when referenced as a string or identifier, it can also include other characters (e.g. @`var-name`), excluding pure numbers and the standalone ".".

Grammer

User-defined variables can be defined using the SET statement:

SET @var_name = expr [, @var_name = expr ...];

Or, using := as the assignment operator:

SET @var_name := expr [, @var_name = expr ...];

• When declaring a user-defined variable, the @ prefix is mandatory.
• Multiple user-defined variables can be declared simultaneously, separated by commas (,).
• Multiple declarations of the same variable are allowed, and the newly declared value will override the original value.
• The expr currently does not support expressions.
• If an undeclared variable is referenced in a SQL statement, its value defaults to NULL, and its type is STRING.

User-defined variables can be queried using the SELECT statement:

SELECT @var_name [, @var_name ...];

Use restrictions

• Viewing existing user-defined variables is not currently supported.
• Assigning a variable to another variable is not currently supported.
• Declaring BITMAP, HLL, PERCENTILE, and ARRAY type variables is not currently supported. JSON type variables are converted to STRING for storage.
• User-defined variables are session-level variables, and all session variables are released when the client disconnects.

Example

mysql> SET @v1=1, @v2:=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @v1,@v2;
+------+------+
| @v1  | @v2  |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT @v1+@v2;
+-------------+
| (@v1 + @v2) |
+-------------+
|           3 |
+-------------+
1 row in set (0.01 sec)

mysql> SET @`var-name`=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @`var-name`;
+-----------+
| @var-name |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql> SET @j := '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j;
+---------------------------------+
| @j                              |
+---------------------------------+
| {"a": 1, "b": 2, "c": {"d": 4}} |
+---------------------------------+
1 row in set (0.00 sec)