Variables in MySQL

What Are The Variables in MySQL?

During program execution, variables are utilized to store data or information. These data are labeled with appropriate names. The primary objective of variables is to store data in memory, making it accessible throughout the program.

There are three distinct methods in which MySQL can utilize variables, as outlined below:

User-Defined Variable

There are instances where values must be passed from one statement to another, and user-defined variables provide a means of storing a value in one statement and referencing it in another statement. MySQL offers a SET and SELECT statement for declaring and initializing a variable. User-defined variable names commence with the '@' symbol.

User-defined variables are not case-sensitive; for example, @name and @NAME are identical. Variables declared by one individual are not visible to another person. User-defined variables can be assigned limited data types such as integer, float, decimal, string, or NULL. The length of a user-defined variable cannot exceed 64 characters.

Syntax

To declare a user-defined variable, use the following syntax:

1. By using the SET statement

2. By using the SELECT statement

Local Variable

A local variable is a strongly typed variable that does not have an @ symbol prefix. Its scope is limited to the stored program block where it's declared, and MySQL employs the DECLARE keyword to specify it. The DECLARE statement can also include a DEFAULT clause to assign a default value to the variable. If no DEFAULT clause is provided, the variable's initial value will be NULL. Local variables are primarily utilized in stored procedure programs.

Syntax

The syntax for using the DECLARE statement is as follows:

System Variable

System variables are a distinctive category of variables that are common to all program units and come with predefined values. MySQL offers a range of system variables that determine how the server functions, and each of them has a default value. At runtime, we can modify some of these variables dynamically by using the SET statement, which allows us to alter server operations without having to stop or restart it. Additionally, system variables can be used in expressions.

MySQL server provides various types of system variables, including GLOBAL, SESSION, and MIX. The GLOBAL variable is available throughout the server's lifecycle, while the SESSION variable remains active only for a specific session.

There are different methods for viewing the names and values of system variables, such as:

• To view the values currently used by the running server, run the following command.

Running the command i.e given above will show a complete list of system variables, including their current values. To narrow down the results and view a specific variable, you can use the LIKE operator followed by the variable name. For example:

This will display only the wait_timeout variable and its current value.

• To view the values of system variables based on their compiled-in defaults, you can use the following command

How to Define a Variable in MySQL

In MySQL, it is essential to assign a value to a variable before using it. If you use a variable without assigning a value to it, the corresponding query will return a NULL value.

The values assigned to variables in MySQL can have various data type, such as:

• INTEGER
• STRING
• DECIMAL
• FLOATING
• NULL

If you attempt to assign a value to a variable that does not match the permissible data types, MySQL will automatically convert it into an appropriate type.

User-defined variables in MySQL are assigned a data type when the statement is first prepared and maintain that data type for all subsequent statements that use the variable. The same rule applies to variables used within stored procedures, where the data type is established during the first invocation of the procedure and remains constant for all subsequent calls.

Assigning Value to A Single Variable

In MySQL, you can set an initial value to a variable using either the SET or SELECT statement.

With the SET Statement

To assign a value to a variable in MySQL using the SET statement, you can use the following syntax:

The typical syntax of the SET command involves the use of the := operator. However, you can use either := or = to assign a value to a variable, and the choice of symbol does not affect the performance of the MySQL SET statement for user-defined variables.

For instance, suppose you have created a variable called @num, and you wish to assign a value of 300 to it. You can do so using the following command:

With the SELECT Statement

You can also set an initial value to a MySQL user-defined variable using the SELECT statement. The SELECT statement is one of the most widely used SQL commands.

The syntax for assigning a value to a variable using the SELECT statement is as follows:

When assigning a value to a variable using the SELECT command, it is essential to use the := operator.

Furthermore, it's worth noting that each user-defined variable can store only one value. If the SELECT command returns multiple values, the variable will be set to the last value in the result set.

Assigning Values to Multiple Variables

MySQL enables you to initialize multiple variables and assign specific values to them using a single command. To do so, you can separate the variables with commas, like so:

Once you have assigned a value to a variable, you can use it in various SQL statements against your MySQL databases, including INSERT and UPDATE statements, WHERE clauses, and other scenarios.

Applying User-Defined MySQL Variables – Practical Examples

In MySQL, it is possible to define and utilize user-defined variables that can prove advantageous in a variety of situations, such as holding intermediate results, simplifying complicated queries, or adjusting operations according to specific conditions.

Listed below are a few examples demonstrating the utilization of user-defined variables in MySQL:

1. Calculating Cumulative Sum

Assume you have a table named "orders," which contains columns like "order_id," "cust_id," and "amt." Your objective is to evaluate the cumulative sum of all the orders placed by customer with id 88, beginning from their first order.

You can accomplish this by employing a user-defined variable that can retain the current running total value, as demonstrated below:

The above SQL query will display a table with four columns: "cust_id," "order_id," "amt," and "cumulative_sum" . The rows returned will only be those where the "cust_id" is 88. The "order_id" will be sorted in ascending order. The variable @cumulative_sum is initialized to 0 using a subquery with an alias v.

For each row, the "amt" column value will be added to the value of the user-defined variable "@cumulative_sum," and the result will be displayed as the "cumulative_sum" value for that row. The variable "@cumulative_sum" will retain its value across rows as the query is executed, allowing for the calculation of cumulative sums.

2. Simulating row_number() function

Although MySQL lacks a built-in row_num() function that allocates a unique sequential number to each row in a result set, it is possible to mimic this functionality by employing a user-defined variable.

In this query, a variable named @row_num is defined and assigned an initial value of 0 through a subquery that has been given an alias 'v'. The SELECT statement then increments the variable by 1 for each row and returns the result as a column named row_number. Additionally, the original columns col1 and col2 are also included in the result set.

3. Customizing behavior in accordance with specific conditions.

Assuming you have a table named 'products', which consists of columns such as product_id, name, price, and discount_percentage. If you intend to compute the ultimate price of each product, factoring in the discount percentage while also providing further discounts for products with prices exceeding a certain limit, you can utilize a user-defined variable to monitor the added discount. The following is an example:

In this query, a variable named @discount is utilized to preserve the added discount. If the price is 100 or more, the variable is set to 10; otherwise, it is set to 0. The SELECT statement then determines the ultimate price of each product by multiplying the original price by the discount percentage, and by the additional discount (if any). The final result set includes all the original columns, along with the additional discount and the final price.