MariaDB Set Variable – Complete Guide

In this MariaDB tutorial, we will study the use of the MariaDB Set Variable statements and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB set variable
• MariaDB set variable from select
• MariaDB set global variable permanently
• MariaDB set environmental variable
• MariaDB set user variable
• MariaDB set server variable
• MariaDB set temporary variable
• MariaDB stored procedure set variable from select
• MariaDB declare variable and set value

MariaDB Set Variable

In this section, we will explain how to use MariaDB SET VARIABLE with syntax and examples.

A variable allows a programmer to declare a value temporarily store it and use it during the time of execution. The SET statement is an extended version of the general SET statement and the reference variable can be declared inside a stored program, global system variables, or user-defined variables.

The SET statement can be used with both local_variable and user-defined variables. When setting several variables using the columns and it returns a query then use SELECT INTO should be preferred.

If you want to set many variables with the same value then use LAST_VALUE() function.

The syntax of the SET VARIABLE is given below:

SYNTAX:

SET VARIABLE_NAME=expression [ var_name= expression]...

The sample example of the SET VARIABLE is given below:

EXAMPLE:

DELIMITER //
CREATE FUNCTION CalcValue ( start_value INT )
RETURNS INT DETERMINISTIC
BEGIN
   DECLARE total INT;
   SET total = 0;
   textbox: WHILE total <= 3000 DO
     SET total = total + start_value;
   END WHILE textbox;
   RETURN total;
END; //
DELIMITER ;

SELECT Calcvalue(1000);

In the above example, first, we have created a function. And within the function, first, we have declared a variable and then we are using the SET statement to assign 0 value to the variable. After this, we are using the variable for an operation.

Read: MariaDB Case Statement

MariaDB Set Variable from Select

In this section, we will explain how to use the Set Variable from Select in MariaDB with syntax and example:

SELECT INTO statement allows selected columns to be held directly into variables. The query returns only single rows. The INTO clause can be defined at the end of the query or statement. This statement can be used with both user-defined variables and local variables.

There is also another way to set value by using the SET statement. The syntax of the SELECT statement with the SET VARIABLE is given below:

SYNTAX:

SELECT @COLUMN_NAME:= current_Value FROM TABLE_NAME WHERE 
COLUMN_NAME= VALUE;

The sample example of the SET variable by using the SELECT statement is given below:

Showing the Marvel_characters table by using the SELECT statement:

SELECT * FROM MARVEL_CHARACTERS;

EXAMPLE:

SELECT @FIRST:=first_name AS 'First Name of Marvel Character'FROM marvel_characters WHERE marvel_id>=205;

Read: MariaDB Window functions

MariaDB Set Global Variable Permanently

In this section, we will learn how to set Global variables with the help of syntax and examples in MariaDB.

The SET statement assigns the different type of variable that impacts the operation of the server and the client. In the older version of MySQL, the SET OPTION was removed in favor of SET without OPTION and it is also being removed from MariaDB 10.5.

If we want to change the SET variable by using the SET statement, it will not change the system variable permanently. To make the changes, then do it configuration setting. To view the system server variable use the SHOW VARIABLES query.

SHOW VARIABLES;

If we want to do the setting the global variable to DEFAULT then it will change the server to default also and vice-versa.

EXAMPLES:

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES 
 WHERE  VARIABLE_NAME 
 IN ('max_error_count','skip_parallel_replication', 'innodb_sync_spin_loops');

In the above example, the SELECT statement is used to fetch the variable_name, global_value, session_value from the information_schema.system_variables. And with the help of WHERE clause in variable_name column with IN clause for max_error_count, skip_parallel_replication, and innodb_sync_spin_loop.

To SET the session variable, use the below query:

SET max_error_count=128;

SET global skip_parallel_replication=ON;

SET _sync_spin_loops=60;  

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES 
 WHERE VARIABLE_NAME 
 IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');

Note: If we want to the SET skip_parallel_replication to global then use the global keyword as the prefix of skip_parallel_replication variable otherwise it will throw an error.

To SET the global value, we will use the following query.

SET GLOBAL max_error_count=250;

SET GLOBAL skip_parallel_replication=ON;

SET GLOBAL innodb_sync_spin_loops=140;

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');

NOTE: If we SET the skip_parallel_replication variable as the GLOBAL variable it will throw an error cause it’s a session variable. So, just remove the GLOBAL keyword as a prefix from the skip_parallel_replication variable.

Using the SHOW VARIABLES query will return the session value unless the variable is global.

Showing the session variable as max_error_count by using the SHOW VARIABLES LIKE query and using the SET statement as the global variable for the session variable.

SHOW VARIABLES LIKE 'max_error_count';

Showing the session variable as skip_parallel_replication by using the SHOW VARIABLES LIKE query and using the SET statement as the global variable for the session variable.

SHOW VARIABLES LIKE 'skip_parallel_replication';

Showing the session variable as innodb_sync_spin_loops by using the SHOW VARIABLES LIKE query and using the SET statement as the global variable for the session variable.

SHOW VARIABLES LIKE 'innodb_sync_spin_loops';

Read: MariaDB Row_Number tutorial

MariaDB set Environmental Variable

MariaDB makes use of the various environmental variables that will suit your system. The environment variable has the lowest precedence, so any options set on the command line or in the options file will take priority.

It is usually better to not rely on environmental variables, and rather set the option that we need directly as it makes the system a little robust and an administrator.

To make it easier to invoke the MariaDB program, we can add the path of the MariaDB BIN directory to our Windows Environmental Variable.

• On the Windows Desktop, right click the My-Computer Icon, then click Properties.
• Next, select the Advanced System Properties Tab from the System Properties menu that appear and then click Environmental Variable button.
• Under the System Variable tab, click the NEW button. The Edit System Variable dialog should appear.
• Once a dialog box appear, just enter the text box of Variable Name as MariaDB Path [you can provide any input over there] and in Variable Value as F:\Program Files\MariaDB 10.5\bin.

Then dismiss the dialogue by clicking the OK button until all the dialogues that are opened have been dismissed. We should not add the MySQL BIN directory to your Windows Path but if we are running multiple MySQL servers on the same machine.

Read: MariaDB DateTime Tutorial

MariaDB Set User Variable

In this section, we will learn how to MariaDB SET user variables by using the syntax and the examples:

The syntax to assign the value to the user variable within the SELECT statement is @VARIABLE_NAME:= VALUE where @VARIABLE_NAME is the variable name and the VALUE is a value that we are recovering. The variable can be used in the following statements whenever the statement is allowed such as in the WHERE clause or the INSERT statement.

A common problem in which user variables come in handy is when we need to issue a successive statement on multiple tables that are related to a common key value.

The syntax example to set the User variable used by the SELECT statement is shown below:

SYNTAX:

SELECT @variable_name=value from TABLE_NAME WHERE
COLUMN_NAME= VALUE;

The sample example of the SET USER VARIABLE by using the SELECT statement is given below:

Showing the dc_characters table by using the SELECT statement:

SELECT * FROM DC_CHARACTERS;

SELECT @FIRST:=first_name AS 'First name of DC Character' FROM dc_characters
WHERE dc_id>=103;

Read: MariaDB Date + Examples

MariaDB Set Server Variable

The MariaDB server maintains many server variables that spell how its configured. Each system variable has its value. It can be set at server startup using the command line or in the options file.

Most of them can be changed dynamically while the server is running by the SET statement which enables us to modify the operation without having to stop or restart it.

MariaDB has many server variables that can be changed according to our needs. If we want to see the list of server variables then run this below query on MariaDB:

SHOW VARIABLES;

Setting the global value:

SET max_error_count=128;

SET innodb_sync_spin_loops=NO;

SET skip_parallel_replication=OFF;

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 
'innodb_sync_spin_loops');

SHOW VARIABLE will default return session value unless the variable is global only.

SHOW VARIABLES LIKE 'max_error_count';

Read: MariaDB DATEDIFF Function

MariaDB Set Temporary Variable

In this section, we will learn how to the MariaDB SET temporary variables with syntax and examples.

In MariaDB, just remember that temporary variables are also known as user-defined variables. The Temporary Variable allows a programmer to store data temporarily and use it during the execution of code.

The syntax to the SET temporary variable is given below:

SYNTAX:

DECLARE variable_name datatype [default inital value]

The syntax explanation:

• variable_name: assign the name to the variable.
• datatype: assign the datatype to the variable.
• DEFAULT inital_value: [optional] It is the value assigned to the variable when it gets declared. If the INITIAL_VALUE is not specified, the variable assigned to inital value of NULL.

The sample example to declare and the SET temporary variable is given below:

DECLARE website_name varchar(40) default 'mariadbtips.com';

EXAMPLE:

DELIMITER //
CREATE PROCEDURE TEMP_VAR()
BEGIN
DECLARE TEMP_VARIABLE VARCHAR(40);
SET TEMP_VARIABLE= 'mariadbtips.com';
SELECT CONCAT('www.',TEMP_VARIABLE);
END//
DELIMITER //

CALL TEMP_VAR();

Read: MariaDB Create Sequence + Examples

MariaDB Stored Procedure Set Variable From Select

In this section, we will learn how to use the MariaDB store procedure set variable from the select statements with syntax and examples.

The syntax of stored procedure set variable from select using the FUNCTION is given below:

CREATE OR REPLACE
    DEFINER = <Mention the user role>
    PROCEDURE sp_name ([procedure_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ INOUT | OUT | IN ] parameter_name <datatype>

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA | NO SQL }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    SQL procedure statement

The sample example to store procedure set variable from select is given below:

Using the SELECT statement to see the full details of USA_basketball table:

SELECT * FROM USA_BASKETBALL;

EXAMPLE:

DELIMITER //
CREATE PROCEDURE basketball_Age( OUT count_Age INT)             
 BEGIN    
   SELECT COUNT(current_age) INTO count_Age FROM usa_basketball ;                
   END ; 
// 
DELIMITER ;
   
CALL basketball_Age(@a);

SELECT @a;

As we see in the above image, we have created a function basketball_Age with inside parameter as count _age. And in the BEGIN statement, we have used the SELECT statement with count clause to count the age of variable count_age from the usa_basketball table.

Then we call the function basketball_Age() and use the SELECT statement to call variable @a. So as the output it showed 8 rows of the usa_basketball table.

Read: MariaDB AUTO_INCREMENT + Examples

MariaDB Declare Variable and Set Value

In this section, we will explain how to declare a variable and set the value inside a MariaDB function with syntax and examples.

A function is a stored program where the user can store a parameter in it and returns its value. Let’s have a quick look:

The syntax to declare variable and set value by using the function is given below:

SYNTAX:

CREATE 
[ DEFINER = { CURRENT_USER | user_name } ] 
FUNCTION func_name [ (parameter type [, parameter type]) ]
RETURNS return_datatype [ LANGUAGE SQL
                        | DETERMINISTIC
                        | NOT DETERMINISTIC
                        | { CONTAINS SQL 
                          | NO SQL
                          | READS SQL DATA
                          | MODIFIES SQL DATA }
                        | SQL SECURITY { DEFINER | INVOKER }
                        | COMMENT 'comment_value'
BEGIN
   declaration_section
   executable_section
END;

The syntax explanation:

• DEFINER clause: [optional] For the creation of the function, definer specifies it as a user. If we want to indicate a different definer, we must use the DEFINER clause, where USER NAME is the function’s definer.
• FUNC_NAME: The name that the user has given to this method in MariaDB.
• Parameter: In a function, there might be multiple parameters. When establishing a function, we must define all parameters that are considered parameters ( not OUT or INOUT parameters).
• Return_datatype: The datatype that returns the function’s data value.
• Language sql: It’s the probability syntax, which has no bearing on the function.
• Deterministic: It indicates that given a set of parameters, a function will always return one result.
• Non-deterministic: It means that given a set of parameters, a function may produce a different outcome. Table data, random numbers, and server data may all have an impact on the outcome.
• Contains SQL: It is a default and informational clause that informs MariaDB that the database contains SQL, but the database will not validate this.
• No SQL: It’s an informational clause that has no effect on the function.
• Reads SQL Data: It’s a helpful clause that tells MariaDB that it’ll read data from the SELECT statement but won’t change anything in the table.
• Modifies SQL Data: It’s an instructive clause that informs MariaDB that the function will modify SQL data via INSERT, DELETE, UPDATE, or other statements.
• Declaration_section: The position in the function where we can declare a variable.
• Executable_section: This is the part of the function where we can write code.

The sample example of declaring set variable and set value inside the function is given below:

EXAMPLE:

DELIMITER //
CREATE FUNCTION Calculate ( current_value INT )
RETURNS INT DETERMINISTIC
BEGIN
   DECLARE final_value INT;
   SET final_value = 0;
   label: WHILE final_value <= 300 DO
     SET final_value = final_value + current_value;
   END WHILE label;
   RETURN final_value;
END; //
DELIMITER;

SELECT Calculate(200) AS New_Calculation;

• In the above code, we have created a function as Calculate() with a parameter as current_value.
• So, under the BEGIN statement, we have created a do-while loop with a variable final_Value and put in a condition of while loop first.
• If the parameters value of the current_value variable is 200 which is greater than the final_Value variable then it will do addition with it but it didn’t do such things.

So, it put as final_value variable as 200 of current_Value variable put in the output by using the SELECT statement put it under a new name by using AS keyword (alias name for the function) as NEW_Calculation function and gave output as 400.

Also, take a look at some more tutorials.

• How to Add Column in MariaDB
• How to Create Table in MariaDB
• MariaDB Select Statement
• How to Create Database in MariaDB
• How to create a user in MariaDB
• MariaDB Join with Examples

In this tutorial, we have learned about the MariaDB Set Variable using some examples. Moreover, we have also covered the following topics in this tutorial as given below:

• MariaDB set variable
• MariaDB set variable from select
• MariaDB set global variable
• MariaDB set global variable permanently
• MariaDB set environmental variable
• MariaDB set user variable
• MariaDB set server variable
• MariaDB set temporary variable
• MariaDB stored procedure set variable from select
• MariaDB declare variable and set value