Variables in MySQL

There are mainly three types of variables in MySQL.
  • User variables (prefixed with @)
  • System Variables (prefixed with @@)
  • Local Variables (No prefix)
Example on User variables are given below.
 
SET @myvar = 1;

select @myvar;

Example on System variables are given below.
 
select @@autocommit;

We can view all System variables using below syntax.
 
SHOW VARIABLES
SHOW VARIABLES LIKE ‘%a%’;

System variables are further categorized in Global and Session. Global variables impact entire server. While session variables impact only session.
 
SET GLOBAL sort_buffer_size=200000;
SET SESSION sort_buffer_size=200000;

Example on Local variables are given below.
 
DECLARE myid INT DEFAULT 0;

Web development and Automation testing

solutions delivered!!