What properties do MySQL user variables have?

The following are the properties of MySQL user variables-

Case-insensitive

User variables are not case-sensitive Write. Prior to MySQL 5 versions, they were case sensitive. This can be illustrated by 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 the client using them connection, and exists only for the duration of that connection. When a connection ends, all its user variables are lost.

Applies only to expressions

User variables can only appear where expressions are allowed. They can appear where constants or literal identifiers appear. This will be illustrated in the following example where we try to use a user variable to provide the table name.

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 assigning new value

The value of user variable will be lost when we assign new value giving it new value. In other words, the value of a given user variable will exist until we assign another value to it. The 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 value of variable @name is "Rahul", but when we change the value to "Raman", the previous value will 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)

The above is the detailed content of What properties do MySQL user variables have?. For more information, please follow other related articles on the PHP Chinese website!