SET Variable in MariaDB

In MariaDB, the SET statement is a main tool in variable handling. Users can assign values to variables, operate with them, and control database operations in various respects. This article includes a look at the SET variable usage in MariaDB and its syntax together with some examples.

SET Variable in MariaDB

The SET statement in MariaDB is used to set values to variables under the session. The variables declared by SET remain in the session unless modified, reset, or unset explicitly. These variables can store a variety of types of data such as integers, strings, etc, and even more complex data structures.

Syntax:

SET @variable_name = value;

• @variable_name: This is the name of the variable to be specified. It should obey the logical name identifier conventions in MariaDB.
• value: The value to be the variable. Can be a constant, an expression, or a function result.

Note: In MariaDB, the “@” symbol is used to denote user-defined variables.

Examples of SET Variable

Let’s look at some examples of using SET variable in MariaDB.

Example 1: Setting Integer Variables

Query:

Assigning the value 10 to the variable my_integer. Now my_integer holds the value 10.

SET @my_integer = 10;

We can see the output by executing the below statement.

Query:

SELECT @my_integer;

Output:

Setting Integer Variable

Explanation: We can clearly saw the my_integer hold 10 values.

Example 2: Setting String Variables

Query:

Assigning the value Hello GFG to the variable my_string. Now, my_string holds the value Hello GFG.

SET @my_string = 'Hello, GFG!';

Check output by executing the below command.

Query:

SELECT @my_string;

Output:

Setting String Variable

Explanation: We can clearly set the string in the my_string.

Example 3: Setting Decimal Variables

Query:

Assigning the value 8.5 to the variable my_decimal. Now, my_decimal holds the value 8.5.

SET my_decimal =8.5;

Check Output:

SELECT @my_decimal;

Output:

Setting Decimal Variable

Explanation: We have successfully Set the Decimal Variables

Example 4: Calculating with SET Variables

You can also perform calculations and store the result in a SET variable:

Query:

SET @num1 = 15;
SET @num2 = 20;
SET @result = @num1 + @num2;

In this query, num1 and num2 are two variables which are sets to 15 and 20 respectively, and then we are calculating the sum of these two variables which will get stored in the result variable.

See the result by executing the below statement.

Query:

SELECT @result;

Output:

We can see below that the output is sum of both variables.

Calculating with SET Variables

Explanation: We have successfully Calculate with SET Variables as a 40 output.

Example 5: Concatenating Strings

SET variables can concatenate strings for various purposes:

Query:

SET @first_name = 'Minal';
SET @last_name = 'Pandey';
SET @full_name = CONCAT(@first_name, ' ', @last_name);

In this query, we have two variables first_name and last_name which are sets to Minal and Pandey respectively. CONCAT keyword is use to concatenate the string. Here also we concatenates the variables with a space in between to form the full name, which is stored in the variable @full_name.

You can see the output by executing the below command.

Query:

SELECT @full_name;

Output:

Concatenating Strings

Explanation: We have successfully Concatenate the two Strings.

Example 6: Retrieving Data from Queries

You can store query results in SET variables for further processing:

Query:

SET @max_salary = (SELECT MAX(salary) FROM employees);

The query SET @max_salary is assigning the maximum value of the salary column from the employees_a table to the user-defined variable @max_salary. This query finds the maximum salary from the employees_a table and stores it in the variable @max_salary. After executing this query, @max_salary will hold the maximum salary value from the employees_a table.

You can see the output by executing the below command.

Query:

SELECT @max_salary;

Output:

Retrieving Data from Queries

Explanation: We have successfully Retrieving the Data from Queries

Example 7: Incrementing a Counter

SET variables can be used as counters:

Query:

SET @counter = 0;
SET @counter = @counter + 1;

Here we have set the counter variable to 0 and then we are increments it by 1.

Check Output:

SELECT @counter;

Output:

Incrementing a Counter

Explanation: We have successfully set the Incrementing a Counter.

Conclusion

SET variable in MariaDB is a strong function that permits the users to handle and do various tasks with data in a session well. Through an analysis of its syntax and practical applications, developers can exploit the way variables are treated in the SELECT statement for working with databases more efficiently, working with dynamic SQL statements, incorporating conditional logic and obtaining a more flexible database programming. Whatever your level of experience with databases, be it as a beginner or an expert, exploiting the use of SET variables is crucial in extracting the most benefits from MariaDB within the different contexts of use.