Using the mysql Client Program - MySQL - page 24

+--------+
| NULL |
+--------+
To set a variable explicitly to a particular value, use a SET statement. SET syntax can use
either := or = as the assignment operator:
mysql> SET @sum = 4 + 7;
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 11 |
+------+
You can assign a SELECT result to a variable, provided that you write it as a scalar sub‐
query (a query within parentheses that returns a single value):
mysql> SET @max_limbs = (SELECT MAX(arms+legs) FROM limbs);
User variable names are not case sensitive:
mysql> SET @x = 1, @X = 2; SELECT @x, @X;
+------+------+
| @x | @X |
+------+------+
| 2 | 2 |
+------+------+
User variables can appear only where expressions are permitted, not where constants
or literal identifiers must be provided. It's tempting to attempt to use variables for such
things as table names, but it doesn't work. For example, if you try to generate a temporary
table name using a variable as follows, it fails:
mysql> SET @tbl_name = CONCAT('tmp_tbl_', CONNECTION_ID());
mysql> CREATE TABLE @tbl_name (int_col INT);
ERROR 1064: You have an error in your SQL syntax near '@tbl_name
(int_col INT)'
However, you can generate a prepared SQL statement that incorporates @tbl_name , then
execute the result. Recipe 4.4 shows how.
SET is also used to assign values to stored program parameters and local variables, and
to system variables. For examples, see Chapter 9 and Recipe 22.1 .