- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- 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
- How to use user variables in MySQL LIKE clause?
- Show a MySQL user-defined variables values in the result table?
- How can user variables be used in MySQL stored procedure?
- What is MySQL Cursor? What are its main properties?
- What are the options and variables introduced in MySQL 8.0?
- What are the options and variables deprecated in MySQL 8.0?
- What do you mean MySQL user variables and how can we assign values to them?
- What are the properties of light?
- What are the properties of Materials
- What are the properties of subtraction?
- What is the default type of a bit value assigned to user variables?
