MySQL System variables

in the database, variables are divided into system variables (with ' @@ ' start ') as well as user-defined variables. System variables are divided into global system variables and session system variables, and static variables belong to special global system variables.

Global System variables (global ) and Session system variables (session ), sometimes called Global system variables as global variables, the session system is called local variable or system session variable. mysql after the service has successfully started, if there is no mysql Client connection mysql server, then mysql The system variables in server memory are all global system variables.

After each MySQL client successfully connects to the MySQL server, it will generate a corresponding session. During a session, no MySQL service instance generates session system variables corresponding to the session in MySQL server memory, and the initial values of these session system variables are the replication of global system variable values. Because each session has different operations during the session, in order to mark each session, the session system variable adds a new variable, for example, the session system variable pseudo_thread_id to mark the current session of MySQL The connection ID.

Client 1 is not client 2 seen or modified; mysql client 1 off or mysql Client Span lang= "en-us" xml:lang= "en-us" >1 after disconnecting from the server with mysql client 1 are automatically freed to save mysql The memory of the server.

The global system variable is characterized by the fact that it is used to define the properties and characteristics of a MySQL service instance, and session 1 is worth modifying for a global system variable that will cause the same global system variable in session 2 to be modified.

View system variable Values "show global variables;" command to view the values of global system variables, use "show session variable;" To view all session system variables related to the current session and all global system variables.

In MySQL , there are some system variables that are only session system variables. For example,MySQL connection ID session system variable pseudo_thread_id, you can use the following two methods to view the value of the session system variable.

Show session variables like ' pseudo_thread_id ';

Show variables like ' pseudo_thread_id ';

In MySQL , there are some system variables that are global system variables and session system variables. For example, the system variable character_set_client is both a global system variable and a session system variable.

Note: As a mysql encoding specification, system variables inMySQL start with two "@", where "@ @global" is used only to mark global system variables, "@ @session "is used only to mark session system variables. "@@" first marks the session system variable and marks the global system variable if the session system variable does not exist. For example, to view Innodb_data_file_path , you can use "select @ @global. innodb_data_file_path;".

Set the value of a system variable

Method One: Modify the mysql source code, then the MySQL configuration file, and then modify the value of the MySQL system variable (for MySQL advanced users);

Method two: By modifying the MySQL configuration file, and then modify the value of the MySQL system variable (need to restart the MySQL service);

Method Three: Use the "set" command to reset the value of the system variable while the MySQL service is running, and if you set a system value to the MySQL default, you can use the default keyword; ,set @ @global. innodb_file_per_table=default; Note the set command does not cause the contents of the My.ini configuration file to change.

If you reset the values of global system variable values (for example, innodb_file_per_table), you can use the following two methods (theglobal keyword cannot be omitted)

Set @ @global. Innodb_file_per_table=on;

Set Global.innodb_file_per_table=on;

There are four ways to resetthe values of Session system variables (sessions):

Set @ @session. pseudo_thread_id=5;

Set session pseudo_thread_id=5;

Set @ @pseudo_thread_id = 5;

Set pseudo_thread_id=5;

mysql service runs through " command to reset its value. There are also special global variables in mysql (for example log_in   tmpdir  version  datadir ), mysql Their values cannot be modified dynamically during service instance run, and cannot be reset using set commands, which are called static variables. The database administrator can reset the value of a static variable using method one or method two.