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.