MySQL Session Variables

Summary: in this tutorial, you’ll learn about MySQL session variables, discover how to list all of them, filter specific ones, and set new values.

Introduction to MySQL session variables

MySQL session variables allow you to customize the behavior of a specific session such as setting the time zone, changing the character set, or adjusting the level of verbosity in the query output.

Session variables are similar to global variables but have a more limited scope. While global variables affect the entire MySQL server, session variables are specific to individual database session.

Once you set a session variable, it takes effect for the duration of the connection and is accessible only by your session.

Displaying all session variables

To display all session variables, you use the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES;Code language: SQL (Structured Query Language) (sql)

This command returns an output that consists of two columns Variable_name and Value. Here is the partial output:

Filtering session variables

To filter the session variable, you add the LIKE clause to the SHOW SESSION VARIABLES statement as follows:

SHOW SESSION VARIABLES LIKE 'variable_name';Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns the current value of the foreign_key_checks variable:

SHOW SESSION VARIABLES LIKE 'foreign_key_checks';Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that the foreign key check is currently ON.

If you don’t remember the variable name, you can use the wildcard character % and _ to find the variable.

For example, the following statement finds the variable that starts with f:

 SHOW SESSION VARIABLES LIKE 'f%';Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| flush                    | OFF            |
| flush_time               | 0              |
| foreign_key_checks       | ON             |
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
8 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

It returns 8 session variables.

Another way to return the current value of a variable is to use the SELECT statement:

SELECT @@variable_name;Code language: SQL (Structured Query Language) (sql)

For example:

SELECT @@foreign_key_checks;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Setting session variables

To set the value of a session variable, you use the SET SESSION statement:

SET SESSION variable_name = value;Code language: SQL (Structured Query Language) (sql)

In this statement, you replace the variable_name with the name of the session variable you want to modify and value with the new value you want to set.

For example, the following statement changes the value of the session variable foreign_key_checks to 'OFF':

SET foreign_key_checks = 'OFF';
Code language: SQL (Structured Query Language) (sql)

After changing the variable, you can check its current value using the SHOW SESSION VARIABLE statement:

SHOW SESSION VARIABLES LIKE 'foreign_key_checks';Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Upon disconnecting and reconnecting to the MySQL server, you’ll observe that the modification to the session variable has not taken effect in the new session.

Summary

• Use the SHOW SESSION VARIABLES statement to show all session variables.
• Use the SHOW SESSION VARIABLES LIKE variable_name or SELECT @@variable_name to display the current value of the variable_name;
• Use the SET SESSION variable_name = value to set a new value for the variable_name.