MySQL – Variables

In this tutorial, we will discuss declaring variables in MySQL, such as user-defined, local, and system variables. You’ll learn the complete syntax with simple examples.

In MySQL, you can access data in three different ways:

1.Declare a user-defined variable
2.Declare a local variable
3.Declare a system variable

Let’s take a look at them one by one.

1. Declare a User-defined Variable

The SET statement in MySQL can be used for declaring variables and initializing them. Once the value is set, it can be accessed anywhere in the script.

The @ sign always precedes a user-defined variable. Below is the syntax:

mysql> SET @my_var1 = expr1 [, @my_var2 = expr2] ...

When initializing the variable, we can use either the “=” or “:=” sign. If possible, avoid mixing conventions and stick with one.

A consumer variable identify is an alpha-numeric and can have characters like (., _, $). We can even have a hyphen (-) if we enclose the title in quotes, for instance – @”my-var1″.

An choice way to declare variables is by using the usage of the SELECT statement.

mysql> SELECT @my_var1, @my_var2, @my_var3;

The variables will take on NULL values since we haven’t assigned any values. The following is the result of running the aforementioned statement:

+————–+—————+————————–+

| @my_var1 | @my_var2 | @my_var3             |

+————–+—————+————————–+

|   NULL       |   NULL        |   NULL        |

+————–+—————+————————–+

We can write a user variable name regardless of case. It can be in uppercase, lowercase, or both.

However, the variables have a maximum length of 64 characters. We can also save the values of the following types:

Data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value

Example:

You can see how we use the SET statement to declare a variable. We also printed the value using the SELECT command.

SET @web_site = 'TechBeamers.com';
SELECT @web_site;

SET @"web-site" = 'TechBeamers.com';
SELECT @"web-site";

The following is the result of executing the previous commands:

1 MySQL Workbench

TechBeamers.com

TechBeamers.com

Here’s another example:

create table Test(id integer, title varchar(100));
insert into Test(id, title) values(1, "MySQL Workbench");
select * from Test;
-- Your code here!
SET @countTotal = (SELECT COUNT(*) FROM Test);
SELECT "@countTotal =", @countTotal;

When you run the above statements, you will get the following result:

1 MySQL Workbench
@countTotal = 1

2. Local Variable Declaration

Local variables, like stored procedures, are defined in a program. To specify such a variable, MySQL provides the DECLARE statement.

We can also use it in combination with the DEFAULT clause to set an initial value. A local variable is NULL otherwise.

To create a local variable, use the following syntax:

DECLARE my_var1 [, my_var2] ... type [DEFAULT value]

Because MySQL treats them as stored proc parameters, we should declare them before the cursor. Furthermore, local variables are unaffected by the case. They also adhere to the same naming conventions as the user-defined ones.

Note: DECLARE can only be used within a BEGIN… END block statement.

After reading the preceding explanation, you can run the example below.

Example:

This example shows a stored procedure that performs an addition operation. It also employs four local variables.

DELIMITER // ;
Create Procedure MySQL_Test_Proc()
    BEGIN
        DECLARE L INT DEFAULT 99;
        DECLARE M INT;
        DECLARE N INT;
        DECLARE T INT;
        SET M = 75;
        SET N = 84;
        SET T = L + M + N;
        SELECT L, M, N, T;
    END //
DELIMITER ; //
CALL MySQL_Test_Proc();

The following is the outcome of running this example:

1 MySQL Workbench

99 75 84 258

3. Declare System Variables

Now let’s look at how to declare a MySQL system variable.

The MySQL server provides a variety of system variables with default values. They are classified as GLOBAL, SESSION, or MIX.

Global variables – These are consistent across the server’s lifecycle.

Session variables – These are only active for specific client sessions.

Furthermore, we can view the current status of a running server. SHOW VARIABLES or SELECT @@var name will do the trick.

Here’s an example of retrieving variables from a running MySQL instance.

SHOW VARIABLES LIKE '%wait_timeout%';
SELECT @@sort_buffer_size;

After running these, the result is as follows:

1 MySQL Workbench
innodb_lock_wait_timeout 50
lock_wait_timeout 31536000
wait_timeout 28800
262144

You can, however, change the system variables. Consider the following examples:

— Syntax to Declare/Set a Global variable

SET GLOBAL sort_buffer_size = 500000;
SET @@global.sort_buffer_size = 500000;

— Syntax to Declare/Set a Session variable

SET sort_buffer_size = 500000;
SET SESSION sort_buffer_size = 500000;
SET @@sort_buffer_size = 500000;
SET @@local.sort_buffer_size = 5000;