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:
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:
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: