Variables in MySQL

There are mainly three types of variables in MySQL.

  1. User variables (prefixed with @)
  2. 2. System Variables (prefixed with @@)
  3. 3. 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;

You may also like...