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)

Vanithasree

Published on 20-Feb-2018 11:00:33

• Related Questions & Answers
• User-defined variables vs Local Variables in MySQL?
• System variables vs User-defined variables in MySQL?
• What is the similarity between prepared statements and MySQL user variables?
• Using User-Defined Variables in MySQL
• What are the closure properties of Regular languages?
• What are the Properties of Data Encryption Standard?
• What is MySQL Cursor? What are its main properties?
• What are the principles of user-interface design?
• What are the options and variables deprecated in MySQL 8.0?
• What are the options and variables introduced in MySQL 8.0?
• What are variables and types of variables in C++?
• What are the document properties of IE4 DOM method?
• What are the properties of window.screen object in JavaScript?
• What are the properties of array class in C#?
• What are the basic properties of products in TOC?