MYSQL VARIABLE

 MySQL Variables

Variables are utilized to hold data or information during a program's execution. They provide a means to label data with meaningful names, making the program easier to understand and maintain. The primary function of a variable is to store information in memory, which can then be accessed and used throughout the program.

In MySQL, variables can be employed in three distinct ways:

• User-Defined Variables: Custom variables created by users to store data temporarily during a session.
• Local Variables: Variables defined within a specific block of code or procedure, and accessible only within that scope.
• System Variables: Predefined variables that store system-level settings and configurations, used to control various aspects of the MySQL server's behavior.

User-Defined Variables:

Sometimes, it is necessary to pass values between different statements within a MySQL session. User-defined variables allow you to store a value in one statement and then reference it in subsequent statements. In MySQL, you can use the `SET` and `SELECT` statements to declare and initialize these variables. User-defined variable names are prefixed with the `@` symbol.

Here are some key points about user-defined variables:

• Case Insensitivity: User-defined variables are not case-sensitive, meaning `@name` and `@NAME` are treated as the same variable.
• Scope: A user-defined variable set by one user is not visible to other users. Each session maintains its own set of user-defined variables.
• Data Types: User-defined variables can store a variety of data types, including integers, floats, decimals, strings, or NULL.
• Length Limitation: The name of a user-defined variable can be up to 64 characters long.

Syntax:

You can declare a user-defined variable using the following methods:

• Using the SET Statement

          SET @var_name = value;

• Using the SELECT Statement

         SELECT @var_name := value;

Example1:Setting a User-Defined Variable

In this example:

• SET @myVariable = 10; assigns the value 10 to the variable @myVariable.
• SELECT @myVariable AS myValue; retrieves the value of @myVariable and labels it as myValue in the result set

Example2:

Step 1: Create the products Table

Step 2: Insert Sample Data

Step 3: Get the Maximum Price

SELECT @maxprice := Max(price)

Run the following statement to get the maximum price of the product in the products table and assign it to the user-defined variable @maxprice.

Result:

After executing the above statement, you should get a result like:

Local Variable

A local variable is a strongly typed variable that isn't prefixed with the @ symbol. Its scope is confined to the specific block of code, such as a stored procedure, where it is defined. In MySQL, you use the DECLARE keyword to create a local variable, and you can include a DEFAULT clause to assign an initial value. If the DEFAULT clause is omitted, the variable will automatically be initialized to NULL. Local variables are primarily utilized within stored procedures.

Syntax

The DECLARE statement can be written using this format:

DECLARE variable_name datatype(size) [DEFAULT default_value];

In other words, you define a variable by specifying its name, data type, and size, optionally including a DEFAULT value to initialize it.

Here’s an example of how to use local variables:

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

You can also declare multiple variables of the same data type in a single DECLARE statement:

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

The following example demonstrates how to use the DECLARE statement within a stored procedure:

DELIMITER //

CREATE PROCEDURE Test()

BEGIN

    DECLARE A INT DEFAULT 100;

    DECLARE B INT;

    DECLARE C INT;

    DECLARE D INT;

    

    SET B = 90;

    SET C = 45;

    SET D = A + B - C;

    

    SELECT A, B, C, D;

END //

DELIMITER ;

After successfully creating the stored procedure, you can execute it as follows:

mysql> CALL Test();

This will produce the output for the MySQL variables defined within the procedure.

It will give the following output:

System Variable

System variables are a specific set of predefined variables used across all program units in MySQL, which help configure its operations. Each system variable has a default value, and many can be modified dynamically at runtime with the SET statement. This allows adjustments to server behavior without needing to stop or restart it. System variables can also be incorporated into expressions.

MySQL categorizes system variables into types such as GLOBAL, SESSION, and MIX. GLOBAL variables are accessible throughout the server's entire lifecycle, while SESSION variables are valid only for the duration of a specific session.

To view the names and current values of system variables, you can use the following methods:

• To check the current values in use by the running server, execute:

                           mysql> SHOW VARIABLES;

                                               (or)

                             mysql> SELECT @@var_name; 

• To see the values based on the compiled-in defaults, you can use:

                           mysql> mysqld --verbose --help

Example1

To display system variables related to the wait timeout, you can execute:

mysql> SHOW VARIABLES LIKE '%wait_timeout%';

Output:

                                        

                  

Example2

To view all system variables related to the maximum allowed packet size, you can run:

mysql> SHOW VARIABLES LIKE '%max_allowed_packet%';

Output: