An Overview Of MySQL User-Defined Variable

This article explains the variables in the MySQL database. Like all relational database management systems, MySQL also supports the concept of variables. The variables are used to store the static or dynamic values. You can also store the single value output generated by a SQL query. MySQL database servers support the following types of variables.

User-defined variables
Local Variables
System Variables

Let us understand their usage with examples.

IMAGE: PEXELS

User-Defined Variables

The User-defined variables are used to store the values generated from one SQL statement. The values stored in a user-defined variable can be used in another SQL statement.

Following are the characteristics of the User-defined variables.

User-defined variables are not case-sensitive, meaning variable names @ID and @id are the same.
User-defined variables are session specific. It is visible to the session which had declared it. If you want to share a variable across multiple sessions, you can use a global variable.
User-defined variables can be used with INTEGER, FLOAT, DECIMAL, STRING, and NULL data types.
The maximum length of the user-defined variable is 64 characters.

Initialize User-Defined Variable

We can use the SET or SELECT keyword to initialize a user-defined variable and = or:= to assign the value. We can initialize multiple variables in one SET statement. The syntax is following

SET @variable_name:=[Some_value];

In the above syntax,

@variable_name: Specify the variable name.
Value: Specify the desired value you want to assign to the variable. It can be a static value or a single-valued query output.

Let us understand the concept of user-defined variables using some examples.

Example 1

First, let us learn it with a simple example. Suppose we want to create a variable and assign a value to it. The query should be written as follows:

SET @Quantity:=10

We can use the SELECT query to view the value stored in a variable. See the following query.

set @Quantity:=10; Select @Quantity;

Output

Example 2

This example explains the use the user-defined variables as a field. To do that, we can use the SELECT statement. To demonstrate it, I created a database named world, which has a table named city. The city table contains the total population of the city. Let us review the data within the table.

use world; select * from city where countrycode=’IND’ and district =’Gujarat’ Limit 10;

Query output

Now, we want to store the highest population of any city in a variable named @MaxPopulation. To do that, the query should be written as follows:

Example 3

Suppose we want to include the row number with the output of the SELECT query. We can do that using the window function of MySQL.

We want to retrieve the records from the CITY table whose country code is IND, and the district is Gujarat. Also, we want to include the row numbers. To do that using the window function, the query should be as follows:

select row_number() over(order by ID) RowNumber, Name,countrycode,district,population from city where countrycode=’IND’ and district=’Gujarat’;

Query output

The windows function shows the sequential numbers starting with one. Also, it has been supported since MySQL version 8.0, so if you are using an older version, you must use a defined variable which also gives you the ability to use the alternative numbers.

Let us populate all records from the CITY table whose country code is IND, and the district is Gujarat with the row number, but the row number must start with 800 and be increased by 50. To do that, the query should be written as follows:

SET @RowNumber := 800; select @RowNumber := @RowNumber + 50 RowNumber, Name,countrycode,district,population from city where countrycode=’IND’ and district=’Gujarat’;

Query output

MySQL System Variable

The MySQL server has a special variable known as system or server system variables. The MySQL system variables are pre-defined server initialization parameters that control the behavior of the MySQL database engine and sessions. The system variable can be global or session-specific. When we change the value of the global system variable, the changes affect the behavior of the entire server. When we change the value of session-specific MySQL variables, the changes affect the current session’s behavior. Mainly, there are two types of system variables.

Dynamic system variables.
Persisted system variables.

Dynamic System Variables

As the name suggests, the dynamic system variables’ values can be changed while the MySQL server is running. The values of dynamic system variables can be changed by using the SET statement. The scope of the dynamic system variables can be global or session-specific, or both.

Note: When MySQL restarts, the values of the Dynamic system variable reset; therefore, to persist the system parameter values, we can use Persisted system variables.

Persisted System Variables

The persisted system variables are the parameters stored in my.cnf file. Changing the values of the persisted system variables can change the behavior of the MySQL database engine. We can change the value of the persisted system variables by using any of the following methods:

Change the values of parameters in my.cnf file.
Use PERSIST SET keyword. The PERSIST SET keyword changes the parameter value and updates my.cnf accordingly so that when we restart the MySQL, the system parameter values do not change.

Let us see few examples to learn how to manage the system variables.

First, we can use the following statement to view all system variables.

mysql> show variables;

As query output is longer, therefore, I have exported the result in a text file. Following is the query output.

You can use the LIKE keyword to filter the name of the parameter. Suppose we want to see the list parameters whose name contains the dir word. Below is the query

mysql> Show variables like ‘%dir%’;

Output

In the SHOW VARIABLE statement, we can add the GLOBAL keyword to view the global variables. Below is the query:

Mysql> Show Global variables

As the query output is longer, I have exported the result in a text file. Following is the query output.

Note that the command will display only the session-specific variables if you do not specify the GLOBAL keyword in the SHOW VARIABLE statement.

Change the values of the system parameter.

Suppose we want to change the value of the max_connection parameter. To do that, run the following statement.

Query To View Current Value

mysql> show variables like “max_connections”;

Screenshot

Query To Update The Value Of The Parameter

mysql> set global max_connections=500;

Query OK, 0 rows affected (0.00 sec)

Once the command is executed successfully, re-run the SHOW VARIABLES command.

Query

mysql> show variables like “max_connections”;

Output

As you can see, the value of the max_connections parameter is changed.

Suppose you want to increase the InnoDB buffer pool size. Also, they make the changes permanently. To do that, run the following queries.

Query To View The Current Value

mysql> show variables like ‘innodb_buffer_pool_size’;

Screenshot

Query To Update The Value Of A Parameter

mysql> set persist innodb_buffer_pool_size= 16777216;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

To verify, restart MySQL and check the value of the parameter.

mysql> shutdown;

Once MySQL is restarted, run the SHOW PARAMETER statement to confirm the changes.

Query

mysql> show variables like ‘innodb_buffer_pool_size’;

Screenshot

As you can see, the value is not changed.

Change System Parameters Using dbforge Mysql Studio

We can view and change the system variables and status parameters of the MySQL Server using the dbForge MySQL studio. To do that, Open dbForge Studio for MySQL Click on Database Open Server variables.

The list of the server variables will open. On the system parameter tab, you can view the system parameter list. The grid view looks like the following image:

The following are the benefits of dbForge Studio for MySQL that helps DBAs properly configure the system variables.

You can see the name of the server parameters with its description. This helps us to get some basic idea of the system parameter and its usage.
We can export the name and values of all MySQL server parameters to various formats like MS Excel, PDF, or HTML.
It is difficult to display all system and status variables in the server variable tab. To make the process easier, dbForge studio provides an option to group the server parameters based on their category.
You can view the values of different MySQL Server system variables from one page. To view the value of the system variable of different instances, to do that, all you have to do is to change the connection name from the connection drop-down box.

We can also change the value of the system variable from the View Server variable screen. Note that only those parameters can be modified whose name is in bold fonts.

Suppose you want to enable the autocommit on the MySQL database server. To do that, click on autocommit and change the value to ON. See the following image for reference.

As you can see, changing the value of system variables is much easier with GUI tool for MySQL – dbForge Studio for MySQL.

Summary

In this article, we learned about MySQL user-defined variables and their usage. Moreover, we learned how to use dbForge Studio for MySQL to view and manipulate the system variables.