MariaDB Variables Tutorial

In this MariaDB tutorial, we will learn about the “MariaDB Variables” which is the type of variables, and how to set the variable. Additionally, cover the following topics.

• MariaDB variables type
• MariaDB variables in query
• MariaDB variables in procedure
• MariaDB variables set
• MariaDB variables list
• MariaDB variables array
• MariaDB variables table
• MariaDB variables in script
• MariaDB system variables
• MariaDB environment variables

MariaDB variables type

First, we need to know 'What is variable?', a variable is used to store different kinds of values or information that can change over time. The data type defines the type of variable in our program, if a variable is defined as an integer data type, then the variable type is an integer and it will store the integer value only.

The syntax is given below ‘How to declare variables in MariaDB’.

DECLARE VARIABLE_NAME DATATYPE (DEFAULT VALUE)

Where,

• DECLARE: It is the section where the variable is defined.
• VARIABLE_NAME: It is the name of the variable.
• DATATYPE: It is the datatype of variable that defines what kind of value a variable can store like integer, character, float, etc. Also it is optional.

DEFAULT VALUE: It is a value that is assigned while declaring the variables, If the variable isn’t set to any value, then the default value will be the value of a variable, or if the default value is not assigned then a variable contain the null value.

Let’s define a variable with datatype using the below code.

DECLARE USA VARCHAR(50)

Where,

1. DECLARE: It is the section where we define the variable, as we can in the code variable USA is defined after this keyword DECLARE.
2. USA: It is the name of the new variable that we are creating in the above code.
3. VARCHAR(50): It is a variable datatype that defines a variable, that can store the string containing a maximum of 50 characters.

Read: MariaDB Drop Table

MariaDB variables in query

In MariaDB, the variables can be used within queries like SELECT statements, We assign the result returned by the SELECT statement to a variable that can be used anywhere in the current session.

The syntax is given below to define the new variable within the query.

@VARIABLE_NAME := VALUE

Where,

• @VARIABLE_NAME: It is the name of the variable that we are defining.
• VALUE: It is a value that we retrieve using a SELECT statement.
• (:=): It acts as an assignment operator to assign the value to a variable.

So for demonstration, here we will use the two tables Employee_country and Person. The description of the table is shown in the below picture.

Let’s create a new variable name @country with a SELECT statement and assign the value.

SELECT @country := emp_country FROM employee_country WHERE emp_country ='United State';

Now, we will use the created variable @country that contains the value as United State of the column emp_country of the table employee_country to filter the person name, birth_date, and birth_place of the table Person.

SELECT * FROM person WHERE birth_place = @country;

In the above code, we are viewing the person whose birth_place is equal to a value within a variable @country.

From the output, we can see that only one person Kenneth S. Covington whose birth_place is the United State.

Here we have concluded that a variable can be created within the SELECT statement and used in another SELECT statement.

Read: MariaDB Foreign Key

MariaDB variables set

The SET the command is used to set the value of a newly created variable in MariaDB, so here in this section, we will set the value of a variable.

The syntax is given below.

SET variable_name = value;

Where,

• SET: It command to set the value of a variable.
• variable_name: It is the name of a variable whose value we want to set.
• value: It is the value that is assigned to a variable.

Let’s declare a new variable using the below code.

DECLARE @city VARCHAR(30);

Now, set the value of a variable @city.

SET @city = 'New York';

View the value of the above-created variable.

SELECT @city;

The output is the string ‘New York’, this is the value that we assigned to the variable @city.

Read: MariaDB Temporary Table

MariaDB variables in procedure

Most of the time we use variables in the procedure to store the value of the calculation or operation within the procedure, a procedure is a stored program that acts as a function in other programming languages.

If you don’t know about how the procedure works, then refer to our tutorial “MariaDB create procedure – Complete Tutorial”.

Here in this section, we will create a procedure Counting_sum containing variable Count_num that stores the integer value for counting the numbers. Use the below code to see the demonstration of how the variable is used within the procedure.

DELIMITER //
CREATE PROCEDURE Counting_sum (OUT count_sum INT )
BEGIN 
DECLARE count_num INT;
SET count_num = 1;
  while count_num < 10 DO
  SET count_num = count_num + 1;
  END while;
  SET count_sum = count_num;
END; //
DELIMITER ;

Call the above procedure and store the result returned from the procedure into a variable name @counting_value.

CALL Counting_sum(@counting_value);

Now view the value of a variable @counting_value using the SELECT statement.

SELECT @counting_value;

From the output, we can see that the result is 10.

Read: MariaDB Backup Database

MariaDB variables list

To list or view the variables in MariaDB, the SHOW VARIABLES command is used, which means if we want to check the variable’s name and their values then the command a SHOW VARIABLES is very helpful.

The syntax is given below how to use the SHOW VARIABLES.

SHOW VARIABLES LIKE '%variable_name%';

Where,

• SHOW VARIABLES: It is the command to list the variables in MariaDB.
• LIKE ‘%variable_name%’: To get the specific variables, the LIKE is used to match those specific variables, where variable_name is the name of a variable that we want to view.

Sometimes, if we want all the variables which have some common pattern, then we can view all these variables by using the wildcard character "%" with the LIKE clause to match the pattern. If you don’t know how to use the LIKE clause, then refer to our tutorial “MariaDB LIKE Operator”.

Let’s view all the variables that contain the word 'log' in their names.

SHOW VARIABLES LIKE '%log%';

The above code shows all the variables whose name contains the word ‘log’.

From the output, all the variable name contains the word ‘log’ in their names.

Read: MariaDB Truncate Table

MariaDB variables table

In MariaDB, there is no concept of variable in tables but we can declare a variable whose data type will be the data type of column that exists in another table. For that, we use the two keywords that are TYPE OF and ROW TYPE OF.

• Remember, we can create this kind of variable with the procedure only.

The syntax is given below.

DECLARE VARIABLE_NAME TYPE OF | ROW TYPE OF  TABLE_NAME | COLUMN_NAME

Where,

• DECLARE: It is the command that we are going to declare a variable.
• VARIABLE_NAME: It is the name of the variable that we want to create.
• TYPE OF | ROW TYPE OF: It is a way to specify that we want data type of variable based on a column of another table (in case of TYPE OF) or want data type of the variable from a table (in case of ROW TYPE OF).

So, here we will use the same table 'Person' that we have used in the above sub-section ‘MariaDB variables in query‘.

Let’s take an example to know how to create a variable whose data type is defined from another table or column in a procedure.

DELIMITER //
CREATE PROCEDURE state(OUT state_n VARCHAR(30) )
BEGIN 
 DECLARE state_name TYPE OF person.birth_place;
SET state_name = 'United State';
SET state_n = state_name;
END; //
DELIMITER ;

Look at the above code at line DECLARE state_name TYPE OF person.birth_place;, here we haven’t declared the data type of variable directly by specifying VARCHAR instead, the data type is declared from column birth_place of table Person.

Call the above procedure and store the result returned from the procedure into a variable name @name_state.

CALL state(@name_state);

Now view the value of a variable @name_state using the SELECT statement.

SELECT @name_state;

Read: MariaDB Rename Column

MariaDB variables in script

In MariaDB, we can create a variable using the script which means we will write SQL query within the script like a SQL file that script will contain the command to create a variable.

Open any text editor on your system, write the below line and save the file with name and extension (.sql) like variable.sql. The below line is the same code that we have used in the above sub-section.

Open the terminal or command-line and login into the MariaDB server.

mysql -u root -p

Select any database.

use mysql;

Run the below code to execute the script file variable.sql that we have created.

source C:\Users\Saurabh\Documents\variable.sql

Read: MariaDB Union Operator

MariaDB system variables

The MariaDB server has many system variables that help in the configuration of its operation. The system variable contains the default value and these default values are the same for all users in MariaDB.

The system variable can be changed at the startup time of the MariaDB server using the option in terminal or command-line but some of the system variables can be changed while the server is running using the statement called SET.

It is not possible to mention all the system variables here, please refer to the official documentation of MariaDB, if you want to know more about the system variables.

Now we are going to know about how to show and set the system variables in MariaDB.

SHOW VARIABLES;

The above code shows all the variables in MariaDB.

In the above output, all are system variables.

we can also these system variables according to our needs using the statement called SET but this variable can change in three ways.

Using command-line, run the below code to set the system variable in command-line.

./mysqld_safe --general_log="1";

Using the file my.cnf in the MariaDB folder. we add the below line in the file my.cnf in MariaDB to set the variable value.

general_log = "1"

Using the SET statement, this statement is used to set the variable value at runtime and the value set while runtime doesn’t persist when we restart the server.

SET GLOBAL general_log = 1;

Read: MariaDB Select Statement

MariaDB environment variables

MariaDB has also many environment variables that can be set according to user needs. The precedence of the environment variable is lowest in MariaDB.

• In general, It is not better to set environment variables usually rather set the variable directly.

Here is given some of the environment variables DBI_USER, HOME and MYSQL_DEBUG, etc. To know more about environment variables visit the official documentation of MariaDB.

We can also set the environment variables as we have learned in the above sub-section ‘MariaDB system variable‘.

You may also like to read the following MariaDB tutorials.

• MariaDB Set Variable
• MariaDB Select Into
• MariaDB Row_Number
• MariaDB Window functions

So, in this tutorial, we have learned about the “MariaDB Variables” and covered the following topics.

• MariaDB variables type
• MariaDB variables in query
• MariaDB variables in procedure
• MariaDB variables set
• MariaDB variables list
• MariaDB variables array
• MariaDB variables table
• MariaDB variables in script
• MariaDB system variables
• MariaDB environment variables