Use of MySQL System Variables

MySQL has many variable types. The following describes MySQL system variables. If you are interested in MySQL System variables, take a look.

MySQL can access many systems and connection variables. Many variables can be changed dynamically when the server is running. This usually allows you to modify server operations without stopping and restarting the server.

The mysqld server maintains two MySQL system variables. Global variables affect the overall operation of the server. Session variables affect the connection operations of specific clients.

When the server starts, it initializes all global variables to the default value. These default values can be changed in the options file or the options specified in the command line. After the server is started, you can dynamically change these GLOBAL variables by connecting to the server and executing the set global var_name statement. To change global variables, you must have the SUPER permission.

The server also maintains a series of session variables for each connected client. During connection, use the current value of the corresponding global variable to initialize the client session variable. For dynamic SESSION variables, the client can change them through the set session var_name statement. You do not need special permissions to set session variables, but the client can only change its own session variables, rather than the session variables of other clients.

Changes to global variables can be seen by any client accessing the global variables. However, it only affects the corresponding session variables initialized from the global variables of the connected customers after the change. Does not affect the session variables of connected clients (even if the client executes the set global statement ).

You can use several syntaxes to set or retrieve global or session variables. The following example uses sort_buffer_sizeas as the sample variable name.

To set the value of a GLOBAL variable, use the following syntax:

Mysql> set global sort_buffer_size = value;
Mysql> SET @ global. sort_buffer_size = value;
To set the value of a SESSION variable, use the following syntax:

Mysql> set session sort_buffer_size = value;
Mysql> SET @ session. sort_buffer_size = value;
Mysql> SET sort_buffer_size = value;
LOCAL is a synonym for a SESSION.

If GLOBAL, SESSION, or LOCAL is not specified when setting variables, the SESSION is used by default.
To retrieve the value of a GLOBAL variable, use the following syntax:

Mysql> SELECT @ global. sort_buffer_size;
Mysql> show global variables like 'sort _ buffer_size ';
To retrieve the value of a SESSION variable, use the following syntax:

Mysql> SELECT @ sort_buffer_size;
Mysql> SELECT @ session. sort_buffer_size;
Mysql> show session variables like 'sort _ buffer_size ';
Here, LOCAL is also a synonym for SESSION.

When you use SELECT @ var_name to search for a variable (that is, global., session. or local. is not specified), MySQL returns the SESSION value if it exists); otherwise, the GLOBAL value is returned.

For show variables, if GLOBAL, SESSION, or LOCAL is not specified, MySQL returns the SESSION value.

The reason why GLOBAL variables require GLOBAL keywords but are not required for retrieval is to prevent future problems. If we remove a SESSION variable with the same name as a GLOBAL variable, customers with SUPER permissions may change the GLOBAL variable rather than their own connected SESSION variables. If we add a SESSION variable with the same name as a GLOBAL variable, customers who want to change the GLOBAL variable may find that only their SESSION variables have been changed.

 

Take you to understand MySQL random string functions

In-depth study of MySQL result strings

MySQL string truncation Function Method

Differences between MySQL string column types

MySQL multiple condition judgment example