MySQL - Variables

Table of content

In general, variables are the containers that store some information in a program. The value of a variable can be changed as many times as required. Each variable has a datatype specifying the type of data we can store in it such as integer, string, float etc.

  • In some programming languages such as Java, C, C++ etc., we need to declare the datatype of a variable before assigning values to it.

  • In languages like python the datatype of a variable is presumed based on the values assigned to it. There is no need of declaring the datatype separately.

  • In MySQL there is no need to declare the datatype we can simply define a variable with a value using the SET statement.

Variables in MySQL

The main purpose of a variable is to label a memory location(s) and store data in it so that it can be used throughout the program.

The characters we use to declare and define a variables are called literals and a literal can be anything other than special characters, numbers and, reserved keywords.

In MySQL, there are three types of variables. The same is described below −

  • User-Defined Variable

  • Local Variable

  • System Variable

User-Defined Variables

The User-Defined variable allows us to store a value in one statement and subsequently refer to it in another. To do so, MySQL provides SET and SELECT commands to declare a variable. These variable names will have the symbol "@" as a prefix. We can use either = or := symbols depending on the situation. The user-defined data type can be any of the following: integer, decimal, Boolean, etc.

Syntax

Following is the syntax to declare a user-defined variable in MySQL using the SET statement −

SELECT @variable_name = value

Example

In the following query, we are assigning a value to a variable using the SET statement as follows −

SET @Name = 'Michael';

Using the SELECT statement, we can display the value of @name variable −

SELECT @Name;

Output

The output for the query above is produced as given below −

@Name
Michael

Example

Here, we are assigning a value to a variable using the SELECT statement −

SELECT @test := 10;

Output

On executing the given query, the output is displayed as follows −

@test := 10
10

Example

Let us create table with the name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT AUTO_INCREMENT PRIMARY KEY,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Now, let us insert values into the above-created table using the INSERT INTO statement −

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES 
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

The CUSTOMERS table is created as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, let us declare a variable with the name @max_salary using the SELECT statement to display the maximum salary value from the CUSTOMERS table −

SELECT @max_salary := MAX(salary) FROM CUSTOMERS;

Then, we will select records from the table where the salary is equal to @max_salary variable −

SELECT * FROM CUSTOMERS WHERE SALARY = @max_salary;

Output

The output for the query above is produced as given below −

ID NAME AGE ADDRESS SALARY
7 Muffy 24 Indore 10000.00

Local Variables

The MySQL local variable can be declared using the DECLARE keyword. When we declare the local variable, the @ symbol is not used as prefix. This variable is a strongly typed variable, which means that we definitely need to declare a data type.

The MySQL DEFAULT keyword can be used while declaring a variable to set the default value of the variable. This is an optional parameter, if we do not define this, the initial value will be NULL.

Syntax

Following is the syntax to declare a local variable in MySQL −

DECLARE variable_name1, variabale_name2, ...

data_type [DEFAULT default_value];

Example

In the following example, we are using the DECLARE statement in a stored procedure.

DELIMITER //
CREATE PROCEDURE salaries()
BEGIN
   DECLARE Ramesh INT;
   DECLARE Khilan INT DEFAULT 30000;
   DECLARE Kaushik INT;
   DECLARE Chaitali INT;
   DECLARE Total INT;
   SET Ramesh = 20000;
   SET Kaushik = 25000;
   SET Chaitali = 29000;
   SET Total = Ramesh+Khilan+Kaushik+Chaitali;
   SELECT Total,Ramesh,Khilan,Kaushik,Chaitali;
END //

Now, let us call the stored procedure using the following query −

CALL salaries() //;

Output

Following is the output −

Total Ramesh Khilan Kaushik Chaitali
104000 20000 30000 25000 29000

System Variables

The system variables are predefined by the MySQL. These contains the data we need, to work with the database. Each MySQL system variable has a default value.

The SET command in MySQL can be used at the runtime to dynamically change the values of the system variables.

There are two variable scope modifiers available for the SHOW VARIABLES command. They are GLOBAL and SESSION.

  • The GLOBAL variables are active throughout the lifecycle.

  • The SESSION variables can be available only in the current session.

Following is the command to display all the system variables in MySQL −

SHOW [GLOBAL | SESSION] VARIABLES;

Example

In the following example, let us display the existing global system variables using the SHOW VARIABLES query −

SHOW VARIABLES LIKE '%table%';

The variables are displayed in the table format as follows −

Variable_name Value
big_tables OFF
default_table_encryption OFF
innodb_file_per_table ON
innodb_ft_aux_table
innodb_ft_server_stopword_table
innodb_ft_user_stopword_table
innodb_table_locks ON
innodb_temp_tablespaces_dir .\#innodb_temp\
innodb_undo_tablespaces 2
innodb_validate_tablespace_paths ON
lower_case_table_names 1
max_heap_table_size 16777216
old_alter_table OFF
performance_schema_max_table_handles -1
performance_schema_max_table_instances -1
performance_schema_max_table_lock_stat -1
show_create_table_skip_secondary_engine OFF
show_create_table_verbosity OFF
table_definition_cache 2000
table_encryption_privilege_check OFF
table_open_cache 4000
table_open_cache_instances 16
tablespace_definition_cache 256
temptable_max_mmap 1073741824
temptable_max_ram 1073741824
temptable_use_mmap ON
tmp_table_size 99614720
updatable_views_with_limit YES

Now, using the query below, we will fetch the current value of the MySQL "key_buffer_size" variable −

SELECT @@key_buffer_size;

Output

Following is the output of the above query −

@@key_buffer_size
8388608
Advertisements