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.


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.

Updated on: 09-Mar-2021


Kickstart Your Career

Get certified by completing the course

Get Started