
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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 Articles
- 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
- How can I return a record from a table nearest to a user-defined variables value in MySQL?
- Select into a user-defined variable with MySQL
- MySQL ORDER BY with numeric user-defined variable?
- Using TreeMap to sort User-defined Objects in Java
- In MySQL, why a client cannot use a user-defined variable defined by another client?
- How to display grant defined for a MySQL user?
- User-Defined Exceptions in Python
- User Defined Literals in C++
- Set user-defined variable with table name in MySQL prepare statement?
- Add user defined value to a column in a MySQL query?
