MySQL Variables

Variables have been used to store data or information while a program runs. It is a method of labelling data with a meaningful term that aids the reader’s understanding of the code. The variable’s primary function is to store data in memory so that it can be used within the system.

MySQL use variable in three different ways i.e.

  • User Defined Variable.
  • Local Variable.
  • System variable.
User-Defined variable

We occasionally need to transmit values from one statement to another. We can utilize the user-defined variable to store a value in one statement and then refer to it in another. To declare and initialize a variable in MySQL, use the SET and SELECT statements. The @ sign precedes the name of the user-defined variable.

User-defined variables such as @name and @NAME are case-insensitive; both are the same. A user-defined variable that is declared by one person cannot be seen by another. The user-defined variable can be assigned to only a few data types: integer, float, decimal, string, or NULL. The length of the user-defined variable is limited to 64 characters.


Following are the Syntax that are used to declare user-defined variables:

SET Statement

SET @var_name = value;

SELECT Statement

SELECT @var_name := value;

Local Varaiable

It’s a variable that isn’t preceded by the @ symbol. A strongly typed variable is the local variable. The local variable’s scope is defined in the saved programme block in which it is declared. The DECLARE keyword is used by MySQL to declare the local variable. A DEFAULT clause is also included in the DECLARE statement to specify a default value for a variable. If you don’t include the DEFAULT clause, the default value is NULL. It’s most commonly utilized in stored procedure programmes.


Following are the Syntax that are used to DECLARE statement:

DECLARE variable_name datatype(size) [DEFAULT default_value];

Let us see the following example to use the local variable.


mysql> DECLARE total_price Oct(8,2) DEFAULT 0.0;

We can also define two or more variables with the same data type by using a single DECLARE statement.

mysql> DECLARE a,b,c INT DEFAULT 0;

Here is the example explains how we can use the DECLARE statement in a stored procedure.

Create Procedure Test()  
        DECLARE Q INT;  
        DECLARE R INT;  
        DECLARE S INT;  
        SET Q = 20;  
        SET R = 25;  
        SET S = P + Q - R;  
        SELECT P, Q, R, S;  
    END //  
System variable

All program units belong to a particular class called system variables, which contains preset variables. MySQL has a number of system variables that control how it works, and each of these variables has a default value. Using the SET statement at runtime, we can alter several system variables dynamically. It allows us to make changes to the server’s functionality without having to stop and restart it. In addition, the system variable can be used in expressions.

The MySQL server provides a variety of system variables, including the GLOBAL, SESSION, and MIX types. The GLOBAL variable is visible throughout the server’s lifecycle, but the SESSION variable is only active during a single session.

The names and values of the system variable can be viewed using the following methods:

  1. To see the current values used by the running server, execute the following command.

    mysql> SHOW VARIABLES;


    Mysql> SELECT @@var_name;

  2. When we want to see the values based on its compiled-in defaults, use the following command.

    mysql> mysqld –verbose –help


    mysql> SHOW VARIABLES LIKE ‘%wait_timeout%’;

    Example 2

    mysql> SELECT @@key_buffer_size;

Copyright 1999- Ducat Creative, All rights reserved.

Anda bisa mendapatkan server slot online resmi dan terpercaya tentu saja di sini. Sebagai salah satu provider yang menyediakan banyak pilihan permainan.