- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Using User-Defined Variables in MySQL
Let us understand what user variables are and how they can be used in MySQL. We will also see the rules −
User variables are written as @var_name. Here, the ‘var_name’ refers to variable name, which consists of alphanumeric characters, ., _, and $.
A user variable name can contain other characters if they are quoted as a string or identifier.
User-defined variables are session specific.
A user variable which is defined by one client can’t be seen or used by other clients.
But the only exception is that if a user has access to the Performance Schema user_variables_by_thread table, then that user can see all user variables for all sessions.
All variables for a given client session are automatically made free when that client exits.
User variable names are not case-sensitive. The names have a maximum length of 64 characters.
Syntax
One way to set a user-defined variable is by issuing a SET statement. Let us see how it can be done using the below command −
SET @var_name = expr [, @var_name = expr] ...
While using SET, use either = or := as the assignment operator.
Assigning Values
User variables can be assigned a value from a limited set of data types. These include integer, decimal, floating-point, binary or nonbinary string, or NULL value.
Assignment of decimal and real values won’t preserve the precision or scale of the value.
A value of a type other than which is permissible gets converted to a permissible type.
This is the same coercibility that is used for table column values.
MySQL user variables can be used to remember results without storing them in temporary variables in the client.
They are used to store data without explicitly assigning a memory location to them.
- Related Questions & Answers
- User-defined variables vs Local Variables in MySQL?
- System variables vs User-defined variables in MySQL?
- Increase database field value by specified percentage using user-defined variables in MySQL
- Show a MySQL user-defined variables values in the result table?
- Perform MySQL SELECT INTO user-defined variable
- PHP User-defined functions
- Select into a user-defined variable with MySQL
- MySQL ORDER BY with numeric user-defined variable?
- User-Defined Exceptions in Python
- User Defined Literals in C++
- Using TreeMap to sort User-defined Objects in Java
- How to display grant defined for a MySQL user?
- How can I return a record from a table nearest to a user-defined variables value in MySQL?
- User-defined Custom Exception in C#
- In MySQL, why a client cannot use a user-defined variable defined by another client?