MySQL variables store data, label data, and let developers create more accurate and efficient code by turning long and complicated strings of characters into one simple variable.
This article will explore user-defined variables.
User-defined variables let us execute various data sets with one command and use this data whenever needed. Mastering variables in MySQL is one of the necessary skills for any database developer.
Contents
- What is a MySQL user-defined variable?
- How to define a variable in MySQL
- Applying user-defined MySQL variables – practical examples
- Conclusion
What is a MySQL user-defined variable?
In MySQL, we can work with three types of variables:
- User-defined variables are the standard variables that allow you to pass a value from one statement to another.
- Local variables are those located in a program block where it is declared. Such variables are mostly used in stored procedures.
- System variables are MySQL server options that allow for configuring the system work.
Thus, the user-defined MySQL variable is a method of passing the value from one statement where you define and store it to the subsequent statement that will use it.
The name of this variable starts with @. The format is @name. It can contain alphabetic letters and numbers, and specific characters, such as $ or _, etc. The maximum name length is 64 characters.
It is better to give the variable an accurate, descriptive name that another user will easily understand. Besides, that user can be yourself. Developers often need to revise the code after a while. Making it clear and easy-readable at once is as essential as leaving comments. So, consider naming your variables thoroughly.
The names of the user-defined variables in MySQL are not case-sensitive. @name, @Name, and @NAME are the same variable.
Another important feature of user-defined variables is being session-specific. The variable defined by one client is not available to other clients – it is invisible to them. When the session ends, the variable expires too.
User-defined variables are specific to MySQL and should be used when working with a MySQL database only. Methods might not suit or be present at all in other database management systems.
How to define a variable in MySQL
You must assign a value to the particular variable to work with it. The query containing a variable without a value will return NULL.
These values can be of the following data types:
- integer
- string (binary or nonbinary)
- decimal
- floating-point
- NULL
If you try to designate a value that does not match the above-listed types, MySQL will convert it into the permitted type.
User-defined variable accepts the datatype on the first time the statement was prepared and keeps it for all further statements containing that variable. The same goes for variables used in stored procedures – the data type is determined on the first invocation of that stored procedure and remains the same for every further invocation.
Now, let us check how to set the value to a MySQL variable correctly.
Assigning value to a single variable
In MySQL, you can initialize variables using the SET or SELECT statements.
With the SET statement
The SET statement syntax for the value-assigning task is as follows:
SET @var_name := value;The standard syntax of the SET command includes the := operator, but you can use := or = to assign value to the variable. Using either symbol does not affect the MySQL SET statement performance for user-defined variables.
Assume you have created the variable called @number, and you want to designate 250 to is as a value. Use the below command:
SET @number := 250Or, you can apply the = operator in the same command, it will work successfully too:
SET @number = 250SET @total_payments = (SELECT SUM(payment) FROM successful_transactions);We assigned simple values to variables. However, they can be more complex. The value of the variable can be the result of a subquery, as shown below:
With the SELECT statement
The second method of assigning the value to a MySQL user-defined variable is through the SELECT statement – the most popular SQL command that is also used commonly with variables.
The SELECT statement syntax for the value assigning ask is as follows:
SELECT @variable_name := value;Always use := operator when you assign the value to a variable through the SELECT command.
Every user-defined variable can contain only one value. If the SELECT command returns several values, the variable will be equal to the last row’s value in the results.
For example, we want to retrieve the list of product prices that exceed $100:
SELECT
@product_price := purchase_price
FROM
products
WHERE
purchase_price > 100
ORDER BY purchase_price;The result is as follows:
| @product_price := purchase_price |
| 101 |
| 104.30 |
| 105.50 |
| 110 |
Now we want to check the @product_price value:
SELECT @product_price;| @product_price |
| 110 |
It has the value specified in the last row of the returned results table.
Assigning values to multiple variables
MySQL allows you to initialize several variables and assign specific values to them with one command, separating those variables by commas:
SET @var_first = value1, @var_second = value2, @var_third = value3;SET @product_number = 100, @product_year = 2022;After designating the necessary value to the variable, you can use it in SQL statements against your MySQL databases, such as in the INSERT and UPDATE statements, in the WHERE clause, and in other scenarios.
Applying user-defined MySQL variables – practical examples
The usage of variables in MySQL makes the code more flexible and gives the developer more options without writing excessive code lines.
To illustrate the usage of the user-defined variable, we have created a test database for car sales. Let us see how variables help us get the necessary information.
Example 1: Improving flexibility
The task is to calculate the profit from the different types of cars we sold.
We introduce the variable @car_model. By assigning different values – Electric, Gas, Petrol, Hybrid, Hydrogen, or LPG – we can limit the data selection according to the car type.
SET @car_model:='Electric'; -- Diesel, Gas, Petrol, Hybrid, Hydrogen, LPG
SELECT p.productname,c.categoryname, p.modelyear, SUM(p.price*oi.qty) AS SUMMA, MAX(o.orderdate)
FROM orders o
INNER JOIN orderitem oi
ON oi.orderid = o.orderid
INNER JOIN product p
ON oi.productid = p.productid
INNER JOIN brand
ON p.brandid = brand.brandid
INNER JOIN category c
ON p.categoryid = c.categoryid
INNER JOIN store s
ON o.storeid = s.storeid
WHERE c.categoryname=@car_model
GROUP BY p.productname, c.categoryname
ORDER BY SUMMA DESC;Note that we use the MySQL GUI solution – dbForge Studio for MySQL to demonstrate the work of user-defined variables. This IDE tool simplifies the creation of all codes. In particular, we are going to apply the Code Completion and Syntax Checker feature to accelerate code writing and ensure its correctness. Also, the Studio provides descriptions for every variable. Developers can focus on the code instead of referring to documentation continually.
This script is more flexible than variable-less alternatives. By changing the value of the variable, we can define the amount of money paid for every particular car type.
Example 2: Selecting data for multiple parameters
We can calculate the profit we’ve made on car sales according to their fuel type and manufacture year. We are interested in sales (profit) figures for cars run on Gas, Electric, and Diesel.
We can use several variables and apply them as filters to select the car type. There can be any number of parameters, and their order does not affect the selection.
The query will search for data on all the specified car types. Without variables, the fuel type parameter won’t impact the data selection.
SET @model1:='Electric';
SET @model2:='Gas';
SET @model3:='Diesel';
SELECT c.categoryname, p.modelyear, SUM(p.price*oi.qty) AS SUMMA
FROM orders o
INNER JOIN orderitem oi
ON oi.orderid = o.orderid
INNER JOIN product p
ON oi.productid = p.productid
INNER JOIN brand
ON p.brandid = brand.brandid
INNER JOIN category c
ON p.categoryid = c.categoryid
INNER JOIN store s
ON o.storeid = s.storeid
WHERE c.categoryname IN (@model1,@model2,@model3)
GROUP BY c.categoryname, p.modelyear
ORDER BY p.modelyear,SUMMA DESC
Example 3: Advanced filtering for the code blocks
MySQL variables can filter entire code blocks. This option is extremely helpful when we need to limit the data selection or we have to test scripts with multiple SELECT statements that are executed one by one.
In our example, we are going to limit the data selection to three different car brands – BMW, Audi, and Ford. Every variable enables or disables the code block that works independently.
SET @IsAudi:='Y';
SET @IsBMW:='Y';
SET @IsFord:='Y';
SELECT SUBSTRING(p.productname,1,4) , SUM(p.price*oi.qty) SUMMA
FROM product p
INNER JOIN brand b
ON p.brandid = b.brandid
INNER JOIN category c
ON p.categoryid = c.categoryid,
orders o
INNER JOIN store s
ON o.storeid = s.storeid
INNER JOIN orderitem oi
ON oi.orderid = o.orderid
WHERE 'Y'=@IsBMW AND p.productname LIKE '%BMW%'
UNION ALL
SELECT SUBSTRING(p.productname,1,4) , SUM(p.price*oi.qty) SUMMA
FROM product p
INNER JOIN brand b
ON p.brandid = b.brandid
INNER JOIN category c
ON p.categoryid = c.categoryid,
orders o
INNER JOIN store s
ON o.storeid = s.storeid
INNER JOIN orderitem oi
ON oi.orderid = o.orderid
WHERE 'Y'=@IsAudi AND p.productname LIKE '%Audi%'
UNION ALL
SELECT SUBSTRING(p.productname,1,4) AS Brand, SUM(p.price*oi.qty) SUMMA
FROM product p
INNER JOIN brand b
ON p.brandid = b.brandid
INNER JOIN category c
ON p.categoryid = c.categoryid,
orders o
INNER JOIN store s
ON o.storeid = s.storeid
INNER JOIN orderitem oi
ON oi.orderid = o.orderid
WHERE 'Y'=@IsFord AND p.productname LIKE '%Ford%' 
We can exclude results for some brands by changing the parameter value.
For instance, we don’t need Ford sales. To exclude the data from the selection, we assign the ‘N‘ value to the @IsFord variable. This parameter disables the code execution of the fragment responsible for the Ford car sales calculation.
SET @IsAudi:='Y';
SET @IsBMW:='Y';
SET @IsFord:='N'; The rest of the code remains the same. But we see NULL for the Ford sales:
This way, MySQL variables serve us excellently to achieve more with less effort.
Conclusion
MySQL variables are efficient means. They allow for producing cleaner and more concise code and getting more precise results. The current article should help you master variables to improve your MySQL skills.
Besides, you can apply your new knowledge more efficiently with the help of a professional MySQL IDE, using its graphical interface, MySQL Code Editor with the code snippets and formatting feature, and other options. We mentioned this IDE earlier in the article – dbForge Studio for MySQL. The 30-days fully-functional trial of the Studio is available for free.
