MySQL variable

The MySQL variable is essential for storing data in the table. The variable declares data with a specific name or label to avoid confusion. This data label is used in the execution to get proper value from a table.  You can share table values from one SQL query to another query.

The variable uses only the execution time to work on data. The one variable used in the entire program for query execution and modification table information. MySQL variable works in three different ways. This method declares as per variable use and limitations.

The MySQL variable works on the three methods below. You pass values from one query to another query using the below variable types.

1. User-defined variable
2. System variable
3. Local variable

SYNTAX

The basic syntax of the variable is below. It helps to assign value. The variable works to declare and update the respective value.

@variable_name;

Most variable names come with @ ("at") symbol. This symbol is placed before a variable name.

@variable_name = value;

You assign variables to the table data. If you want to work on the respective data, then use the given variable.

DECLARE variable_name data type (size);

The local variable does not need to use @ symbol before the variable name. You use the "declare" keyword for the local variable.

User-defined variable

The user-defined variable is used to pass table values from the initial query to another query. The initial query stores value in the table. The other MySQL query refers to the value from MySQL initial query.  The user-defined variable does not see from one user to another user.

The user-defined variable stores all kinds of data. The variable assigns integer, float, decimal, string, and null values. This variable declares a name using any sentence case such as an uppercase and a lowercase.

@VARIABLE_NAME and @variable_name have the same meaning because of not case-sensitive properties. The user-defined variable contains 64 characters in any format.

MySQL user-defined variable works with SET and SELECT statements. This statement is used to initialize the value and declares the required value. The user-defined variable comes before @ symbol to assign the variable name.

SYNTAX

The user-defined variable uses the SET statement. This variable helps to initialize the value. The variable uses either the "=" or ":=" symbol to assign value.

If the variable uses the "=" symbol, then syntax is below.

SET @variable_name = value;

If the variable uses the ":=" symbol, then syntax is below.

SET @variable_name := value;

The user-defined variable uses the SELECT statement. This variable helps to declare value. The syntax is below.

SELECT @variable_name;

Examples of the user-defined variable

1) Example: the user-defined variable with a SET statement.

Execute below query to set user define variable.

 mysql> set @mark = 54;
 Query OK, 0 rows affected (0.13 sec) 

OUTPUT

Execute below query to get output.

mysql> select @mark;

You see the mark variable and its value in the output.

2) Example: the user-defined variable with SELECT statement.

Execute the below query to get the user to define a variable.

mysql> select @mark;

OUTPUT

You see the mark variable and its value in the output.

The variable declared value in the output. This local variable value is set from the user and declared as an output.

3) Example: the user defined a variable with SELECT statement and value.

mysql> select @mark := 54;

OUTPUT

You see the mark variable and its value in the output.

If a value is available, then output shows the Boolean value "1." You must initialize the variable with the required value.

4) Example: the user defined a variable with SELECT statement and value.

mysql> select @mark := 54;

OUTPUT

You see the mark variable and its value in the output.

If the value does not include available, then output shows a null value. If the variable does not initialize, then output displays a null value.

The user-defined variable is the most usable in the MySQL data. This variable does not need to declare any table. It is a temporary variable to execute a program and manage data.

System variable

The system variable is predefined in the MySQL data management system. The system variable stores several variables with a default value. If you want to initialize other values, then use the SET statement.

The system variable contains global, session, and mix types variables. The global variable works the entire lifecycle of the MySQL server. The session variable works for a specific session of the MySQL server.

SYNTAX

Use the below query to get many stored variables in the MySQL system.

SHOW VARIABLES;

If you use the above query, then the MySQL command-line client executes continuously. If you want to show output, then apply any condition.

Show variable LIKE pattern;

The MySQL system variable uses the "LIKE" operator with the required pattern.

Examples of the system variable.

MySQL system variable shows using "WHERE" and "LIKE" with the necessary condition.

1) Example: the system variable with the "LIKE" operator.

You use the "LIKE" operator with a pattern to get a particular variable from the MySQL system. Execute the below query to get the required system variable.

mysql> show variables LIKE '%out';

OUTPUT

You see the system variable and its value using a pattern.  You see, the system variable includes the "timeout" keyword. You get the default size of the MySQL system variables.

2) Example: the system variable with the "WHERE" operator.

You use the "WHERE" clause with the condition to get a particular variable from the MySQL system. Execute the below query to get the required system variable with a specific value.

mysql> show variables where value = 30;

OUTPUT

You see the above output of the system variable with value = 30.

3) Example: the system variable displays size.

The double "at" (@) symbol is used to declare the size of the variable. Execute the below query to get the size of the system variable.

mysql> select @@wait_timeout;

OUTPUT

You see the respective variable and its size in the output image. The "wait_timout" variable has 28800 values.

Local variable

The local variable works in the "store procedure" programs. This variable does not use @ symbol to assign a variable. The local variable stores value with a data type. The local variable declares data and executes operation using stored procedure.

The local variable uses the "DECLARE" keyword to specify the local variable. This variable uses the "DEFAULT" clause. The default clause gives a specific value to a local variable. If the default value is not assigned then, the variable makes the default value null.

SYNTAX  

The "DECLARE" keyword comes before the variable name. The data type is placed after the variable name.  The default value must be assigned for a variable. The local variable with default value syntax shows below.

 DECLARE variable_name data_type(size) DEFAULT default_value;

The local variable with default value syntax shows below.

DECLARE variable_name data_type(size);

The local variable with similar data type syntax shows below.

DECLARE variable1, variable2, variable3 data_type(size) DEFAULT default_value;

Examples of the local variable

1) Example: the local variable with similar data type example shows below.

Execute the below query to know the working procedure of the local variable.

 DELIMITER $$
 USE `tutorial`$$
 CREATE PROCEDURE `local_variable` ()
 BEGIN
         DECLARE vfirst INT DEFAULT 40; 
         DECLARE vsecond INT DEFAULT 30; 
         DECLARE vthird INT;  
         DECLARE vfourth INT; 
         SET vfirst = 30; 
         SET vthird = 40; 
         SET vfourth = vfirst + vthird ; 
         SELECT vfirst, vsecond, vthird, vfourth;
 END$$
 DELIMITER ;
 ; 

OUTPUT

Execute below query to get output.

mysql> call local_variable();

You can see the above output with variable values. All variable values are set with numbers and operations.

2) Example: the local variable with similar data type example shows below.

Execute the below query to know the working procedure of the local variable.

 DELIMITER $$
 USE `tutorial`$$
 CREATE PROCEDURE `local_variable` ()
 BEGIN
        DECLARE vfirst INT DEFAULT 40; 
         DECLARE vsecond INT; 
         DECLARE vthird INT; 
         DECLARE vfourth INT;
         SET vthird = 40; 
         SET vfourth = vfirst + vsecond; 
         SELECT vfirst, vsecond, vthird, vfourth;
 END$$
 DELIMITER ;
 ; 

OUTPUT

Execute below query to get output.

mysql> call local_variable();

The first variable declares the default value. The second variable declares the null value. The third variable shows a set value. If you set both values, then the fourth variable operates; otherwise, it becomes null.

3) Example: the local variable with different data type example shows below.

Execute the below query to know the working procedure of the local variable.

 DELIMITER $$
 USE `tutorial`$$
 CREATE PROCEDURE `local_variable` ()
 BEGIN
        DECLARE vfirst INT DEFAULT 40; 
         DECLARE vsecond float default 35.8; 
         DECLARE vthird char default "m"; 
         DECLARE vfourth decimal;
         SET vfirst = 5;
         SET vsecond = 22.6; 
         SET vfourth = vfirst + vsecond; 
         SELECT vfirst, vsecond, vthird, vfourth;
 END$$
 DELIMITER ;
 ; 

OUTPUT

Execute below query to get output.

mysql> call local_variable();

The first variable declares the default integer value. The second variable declares the default "float" value. The third variable shows the default "char" value. If you set both values, then the fourth variable operates; otherwise, it becomes null.