Variables in MySQL

Sometimes we need to store some data that we will need during the program execution. In this case, variables are going to help us. A variable is like a small box where you can store some number or string or other data of any type. Variables are an important part of any programming language, and SQL is no exception. In this topic, we will talk about variables in MySQL, their declaration and practical usage.

Variable types

There are three types of variables in MySQL:

1. User-Defined Variable

2. Local Variable

3. System Variable

Each of them has its own peculiar properties. In the next paragraphs, we will discuss each of these types and their features.

User-defined variables

We may occasionally need to transmit values from one SQL statement to another. In order to do this, we can save a value in one statement and subsequently refer to it in another statement thanks to the user-defined variable.

In MySQL, we can use both SET and SELECT commands to declare the variable. The assignment operator also has no restrictions: we can use either = or :=, depending on the situation. The user-defined variable can be any data type: integer, decimal, string, boolean, and so on.

Now let's talk about the syntax of the user-defined variable declaration:

SET @variable_name = <value>;

Or you can do it using the SELECT statement:

SELECT @variable_name = <value>;

Time for the examples. We will start with a simple one, and create a variable called name to store the "MySQL" line:

SET @name = "MySQL";

Then we can call it using the SELECT statement:

SELECT @name;

As you can see, it is pretty simple to create a variable in MySQL. Now let's take a look at a more complicated example. Imagine, we have a table called products that has the price column. We will get the maximum price from this table and insert it into the max_price variable.

SELECT @max_price := MAX(price) FROM products;

Remember that when using any functions (MAX, MIN, etc.) in a variable, you should use the SELECT operator and the := assignment operator.

Now we can use this variable in another query. For example, we will select the product with the highest price:

SELECT * FROM products
WHERE price = @max_price;

As you may see, variables are really useful in development, and it is very easy to use them.

Local variables

The variables we discussed in the previous paragraph can be used in any part of the program. In contrast to that, local variables can only be used in a specific block of code, and cannot be accessed outside of this block of code. Local variables also have their own peculiarities:

• When declaring variables of this type, the @ prefix is not used.

• The local variable is a strongly typed variable. We absolutely need to declare a data type.

• In MySQL, we need to use a DECLARE keyword to declare a local variable.

• We can also use the DEFAULT keyword when declaring a variable to set the default value of the variable. This is optional, so if you did not do this, the initial value will be NULL.

As you may have noticed, the syntax for declaring local variables is different. Now we will review this line of code:

DECLARE variable_name <data_type> [DEFAULT <default_value>];

Let's take a look at the example.

DECLARE a INT;
DECLARE b INT;

SET a = 5;
SET b = 10;

SELECT a + b;

After calling it, we will get the following result:

Now let's create a new local variable c and set the default value to it. Then we will add this number to the other two.

DECLARE a INT;
DECLARE b INT;
DECLARE c INT DEFAULT 30;

SET a = 5;
SET b = 10;

SELECT a + b + c;

We will get a result equal to 45. As you can see, we didn't set the value to our new variable in the section where we were setting the values to the a and b variables. The default value was used while the addition.

System variables

System variables are variables that have already been created by the DBMS itself. They store the data we need to work with the database. Each system variable in MySQL has a default value, and these system variables are used to configure the way the database operates. The SET command can be used at runtime to dynamically change various system variables' values.

In MySQL, you can view all the system variables with this command:

SHOW VARIABLES;

There are also GLOBAL or SESSION variable scope modifiers available for the SHOW VARIABLEScommand.

SHOW [GLOBAL | SESSION] VARIABLES;

• Global variables stores the values that are used to set up new connections to MySQL's relevant session variables.

• The statement shows the system variable values of the current connection when a SESSION modifier is used. The SESSION modifier is also used by default unless one of the modifiers is specified.

You can also access the specific variable using the @@ prefix. For example, we can find out the name of the host on which the MySQL server is running:

SELECT @@hostname;

Conclusion

All right, in this topic, you have learned about variables in MySQL. Let's repeat the main points, and you will be ready to practice:

• There are three types of variables in MySQL: user-defined, local, and system.

• User-defined variables can be used in any part of the SQL query.

• Local variables can be used in the specific blocks of code only.

• System variables are already predefined by MySQL. They store the information necessary for the DBMS to operate and can be changed by the user.