Using user-defined variables in MySQL

#Let us understand what are user variables and how to use them in MySQL. We will also see that the rule -

• user variable is written as @var_name. Here, "var_name" refers to the variable name, consisting of alphanumeric characters, ., _ and $.

• User variable names can contain other characters (if enclosed in quotes) as strings or identifiers.

• User-defined variables are session-specific.

• User variables defined by one client cannot be seen or used by other clients.

• The only exception is if the user has access to the performance schema user_variables_by_thread table, the user can view all user variables for all sessions.

  All variables for a given client session are automatically released when that client exits.

  User variable names are not visible and case sensitive. The maximum length of the name is 64 character.

Syntax

One way to set a user-defined variable is to issue a SET statement. Let us see how it can be done using the following command -

SET @var_name = expr [, @var_name = expr] ...

When using SET, use = or := as assignment operator.

Assignment

User variables can be assigned values ​​from a limited set of data types. These include integers, decimals, floating point, binary or non-binary strings, or NULL values.

• Assignment of decimal and real numbers does not preserve precision or the number of decimal places of the value.

• Values ​​of non-allowed types will be converted to allowed types.

• This is the same cast used for table column values.

• MySQL user variables can be used to remember results without storing them in temporary variables on the client side.

• They are used to store data without explicitly allocating a memory location for it.

The above is the detailed content of Using user-defined variables in MySQL. For more information, please follow other related articles on the PHP Chinese website!