What are the properties of MySQL user variables?

Followings are the properties of MySQL user variables −

Not Case-Sensitive

A user variable is not case-sensitive. They are case-sensitive before version MySQL 5. It can be illustrated in the following example −

Example

mysql> SET @A = 'MySQL';
Query OK, 0 rows affected (0.00 sec)

mysql> Select @A, @a;
+-------+-------+
| @A    | @a    |
+-------+-------+
| MySQL | MySQL |
+-------+-------+
1 row in set (0.00 sec)

Specific to client connection

Actually, user variables are specific to client connection within which they are used and exist only for the duration of that connection. When a connection ends, all its user variables are lost.

Works with expressions only

User variables can appear only where expressions are allowed. They can appear where constants or literals identifiers have appeared. Following the example, in which we are trying to use user variable for providing the table name, will illustrate it.

Example

mysql> Set @table_name = CONCAT('tbl_','Employee');
Query OK, 0 rows affected (0.00 sec)

mysql> Create Table @table_name(Id Int);
ERROR 1064 (42000): You have an error in your SQL syntax near '@table_name(Id Int)'

Lost when assigned new value

A user variable’s value will be lost when we assign a new value to it. In other words, a given user variable’s value persists until we assign it another value. Following example will demonstrate it −

Example

mysql> SET @name = 'Rahul';
Query OK, 0 rows affected (0.00 sec)

mysql> Select @name;
+-------+
| @name |
+-------+
| Rahul |
+-------+
1 row in set (0.00 sec)

At this time, the variable @name is having the value ‘Rahul’ but when we change the value to ‘Raman’, the previous value will be lost.

mysql> SET @name = 'Raman';
Query OK, 0 rows affected (0.00 sec)

mysql> Select @name;
+-------+
| @name |
+-------+
| Raman |
+-------+
1 row in set (0.00 sec)