Sign InTry Free

The System Variables

The system variables in MySQL are the system parameters that modify the operation of the database runtime. These variables have two types of scope, Global Scope and Session Scope. TiDB supports all the system variables in MySQL 5.7. Most of the variables are only supported for compatibility and do not affect the runtime behaviors.

Set the system variables

You can use the SET statement to change the value of the system variables. Before you change, consider the scope of the variable. For more information, see MySQL Dynamic System Variables.

Set Global variables

Add the GLOBAL keyword before the variable or use @@global. as the modifier:

SET GLOBAL autocommit = 1; SET @@global.autocommit = 1;

Set Session variables

Add the SESSION keyword before the variable, use @@session. as the modifier, or use no modifier:

SET SESSION autocommit = 1; SET @@session.autocommit = 1; SET @@autocommit = 1;

The working mechanism of system variables

  • Session variables will only initialize their own values based on global variables when a session is created. Changing a global variable does not change the value of the system variable being used by the session that has already been created.

    mysql> SELECT @@GLOBAL.autocommit; +---------------------+ | @@GLOBAL.autocommit | +---------------------+ | ON | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT @@SESSION.autocommit; +----------------------+ | @@SESSION.autocommit | +----------------------+ | ON | +----------------------+ 1 row in set (0.00 sec) mysql> SET GLOBAL autocommit = OFF; Query OK, 0 rows affected (0.01 sec) mysql> SELECT @@SESSION.autocommit; -- Session variables do not change, and the transactions in the session are executed in the form of autocommit. +----------------------+ | @@SESSION.autocommit | +----------------------+ | ON | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT @@GLOBAL.autocommit; +---------------------+ | @@GLOBAL.autocommit | +---------------------+ | OFF | +---------------------+ 1 row in set (0.00 sec) mysql> exit Bye $ mysql -h127.0.0.1 -P4000 -uroot -D test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.25-TiDB-None MySQL Community Server (Apache License 2.0) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT @@SESSION.autocommit; -- The newly created session uses a new global variable. +----------------------+ | @@SESSION.autocommit | +----------------------+ | OFF | +----------------------+ 1 row in set (0.00 sec)

The fully supported MySQL system variables in TiDB

The following MySQL system variables are fully supported in TiDB and have the same behaviors as in MySQL.

autocommitGLOBAL | SESSIONwhether automatically commit a transaction
sql_modeGLOBAL | SESSIONsupport some of the MySQL SQL modes
time_zoneGLOBAL | SESSIONthe time zone of the database
tx_isolationGLOBAL | SESSIONthe isolation level of a transaction
max_execution_timeGLOBAL | SESSIONthe execution timeout for a statement, in milliseconds
innodb_lock_wait_timeoutGLOBAL | SESSIONthe lock wait time for pessimistic transactions, in seconds
interactive_timeoutSESSION | GLOBALthe idle timeout of the interactive user session, in seconds

TiDB specific system variables

See TiDB Specific System Variables.

Download PDF
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
TiDB Dedicated
TiDB Serverless
Get Demo
Get Started
© 2023 PingCAP. All Rights Reserved.