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.

AmitDiwan

Published on 09-Mar-2021 13:14:16

Previous Page Print Page