{"id":12763,"date":"2023-12-08T07:21:51","date_gmt":"2023-12-08T14:21:51","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12763"},"modified":"2023-12-08T07:23:17","modified_gmt":"2023-12-08T14:23:17","slug":"mysql-session-variables","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-session-variables\/","title":{"rendered":"MySQL Session Variables"},"content":{"rendered":"\n

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

Introduction to MySQL session variables<\/h2>\n\n\n\n

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

Session variables are similar to global variables<\/a> but have a more limited scope. While global variables affect the entire MySQL server, session variables are specific to individual database session<\/strong>.<\/p>\n\n\n\n

Once you set a session variable, it takes effect for the duration of the connection and is accessible only by your session.<\/p>\n\n\n\n

Displaying all session variables<\/h3>\n\n\n\n

To display all session variables, you use the SHOW<\/code> SESSION<\/code> VARIABLES<\/code> statement:<\/p>\n\n\n

SHOW<\/span> SESSION<\/span> VARIABLES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This command returns an output that consists of two columns Variable_name<\/code> and Value<\/code>. Here is the partial output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Filtering session variables<\/h3>\n\n\n\n
To filter the session variable, you add the LIKE<\/a><\/code> clause to the SHOW SESSION VARIABLES<\/code> statement as follows:<\/p>\n\n\n
SHOW<\/span> SESSION<\/span> VARIABLES<\/span> LIKE<\/span> 'variable_name'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement returns the current value of the foreign_key_checks<\/code> variable:<\/p>\n\n\n
SHOW<\/span> SESSION<\/span> VARIABLES<\/span> LIKE<\/span> 'foreign_key_checks'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------+-------+<\/span>\r\n| Variable_name      | Value |\r\n+--------------------+-------+<\/span>\r\n| foreign_key_checks | ON    |\r\n+--------------------+-------+<\/span>\r\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the foreign key check is currently ON.<\/p>\n\n\n\n
If you don’t remember the variable name, you can use the wildcard character %<\/code> and _<\/code> to find the variable.<\/p>\n\n\n\n
For example, the following statement finds the variable that starts with f<\/code>:<\/p>\n\n\n
 SHOW<\/span> SESSION<\/span> VARIABLES<\/span> LIKE<\/span> 'f%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------------+----------------+<\/span>\r\n| Variable_name            | Value          |\r\n+--------------------------+----------------+<\/span>\r\n| flush<\/span>                    | OFF<\/span>            |\r\n| flush_time               | 0<\/span>              |\r\n| foreign_key_checks       | ON<\/span>             |\r\n| ft_boolean_syntax        | + -><()~*:\"\"<\/span>&| |\r\n| ft_max_word_len          | 84<\/span>             |\r\n| ft_min_word_len          | 4<\/span>              |\r\n| ft_query_expansion_limit | 20<\/span>             |\r\n| ft_stopword_file         | (built-in<\/span>)     |\r\n+--------------------------+----------------+<\/span>\r\n8<\/span> rows<\/span> in<\/span> set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns 8 session variables.<\/p>\n\n\n\n
SELECT<\/span> @@variable_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
SELECT<\/span> @@foreign_key_checks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------------+<\/span>\r\n| @@foreign_key_checks |\r\n+----------------------+<\/span>\r\n|                    1 |\r\n+----------------------+<\/span>\r\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Setting session variables<\/h3>\n\n\n\n
To set the value of a session variable, you use the SET SESSION<\/code> statement:<\/p>\n\n\n
SET<\/span> SESSION<\/span> variable_name = value<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement, you replace the variable_name<\/code> with the name of the session variable you want to modify and value with the new value you want to set.<\/p>\n\n\n\n
For example, the following statement changes the value of the session variable foreign_key_checks<\/code> to 'OFF'<\/code>:<\/p>\n\n\n
SET<\/span> foreign_key_checks = 'OFF'<\/span>;\r<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After changing the variable, you can check its current value using the SHOW<\/code> SESSION<\/code> VARIABLE<\/code> statement:<\/p>\n\n\n
SHOW<\/span> SESSION<\/span> VARIABLES<\/span> LIKE<\/span> 'foreign_key_checks'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------+-------+<\/span>\r\n| Variable_name      | Value |\r\n+--------------------+-------+<\/span>\r\n| foreign_key_checks | OFF   |\r\n+--------------------+-------+<\/span>\r\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the SHOW SESSION VARIABLES<\/code> statement to show all session variables.<\/li>\n\n\n\n
  • Use the SHOW SESSION VARIABLES LIKE variable_name<\/code> or SELECT @@variable_name<\/code> to display the current value of the variable_name<\/code>;<\/li>\n\n\n\n
  • Use the SET SESSION variable_name = value<\/code> to set a new value for the variable_name<\/code>.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Another way to return the current value of a variable is to use the SELECT<\/a><\/code> statement:<\/p>\n\n\n