Shulou(Shulou.com)06/01 Report--

This article introduces variables in MySQL 5.6, which is basically compiled from my previous study of MySQL 5.6 manual.

basic concepts

Variables in MySQL can be divided into the following categories:

MySQL system variable: This type variable reflects how MySQL server is configured. Each system variable has a default value. System variables can be set in MySQL configuration files or using MySQL startup options. Most system variables can be modified dynamically at MySQL runtime using the SET command.

MySQL state variable: This type variable reflects the running state of MySQL server.

Variables can be divided into:

Global variables (global): effective globally for MySQL servers.

Session variables (session variables): only affect a single client session.

Some MySQL options have only global or session variables, while others have both. The association between global and session variables is as follows:

When MySQL starts, it initializes each global variable to its default value. This default value can be modified in the configuration file or using MySQL startup options.

MySQL also maintains a set of session variables for each client connection. When a client connects to the MySQL service, all of its session variables are initialized to the current values of the corresponding global variables. For a small number of system variables, their session values may not be initialized to the values of the corresponding global variables. For details, please consult the MySQL official manual.

System variables are divided into:

Dynamic variables: The value of a variable can be modified dynamically. The changes take effect without restarting MySQL services.

Non-dynamic variable: The value of a variable cannot be modified dynamically. The MySQL service needs to be restarted to take effect.

A complete list of variable attributes can be found in the MySQL reference manual. For example, you can learn whether a variable is a system variable or a state variable, whether it is a global variable or a variable or both, whether it is a dynamic variable or a non-dynamic variable.

View the value of a variable

To view all default system variable values for MySQL (ignoring settings in the configuration file), use the command:

[root@gw ~]# mysqld --no-defaults --verbose --help

To view all MySQL default system variable values (after reading the settings in the configuration file), use the command:

[root@gw ~]# mysqld --verbose --help

The SHOW command views the values of system variables in the syntax format:

mysql> SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

When the keyword GLOBAL is used, it is to view the current values of all global variables:

mysql> SHOW GLOBAL VARIABLES;

When the keyword SESSION is not used or used, the current values of all session variables are viewed (the values of the current session variables are displayed if there are session variables, and the values of global variables are displayed if there are no session variables):

mysql> SHOW VARIABLES;

mysql> SHOW SESSION VARIABLES;

You can add LIKE statements and use wildcards % or_to match specific variables. The percent sign % matches any number of characters and the underscore_matches a single character. Examples:

mysql> SHOW VARIABLES LIKE 'innodb_buffer%';

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer%';

Alternatively, you can use SELECT statements to view the values of system variables. For example:

mysql> SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

For values that refer to variables in expressions of the form @@var_name instead of @@global. or @@session.), MySQL returns the value of the session variable if it exists, otherwise it returns the value of the global variable. @@global. Prefixed is an explicit designation to view global variable values, with @@session. The prefix is the value that explicitly specifies the view session variable.

The SHOW command looks at the value of a state variable in the syntax format:

mysql> SHOW [GLOBAL | SESSION] STATUS [like_or_where]

When the keyword GLOBAL is used, global status values are displayed. A global state variable may represent some state of the server itself (e.g., Aborted_connections), or the aggregate state of all connection sessions (e.g., Bytes_received and Bytes_sent). If a variable does not have a global state value, the session state value is displayed.

mysql> SHOW GLOBAL STATUS;

When the keyword is not used or when the keyword SESSION is used, the session state value is displayed. If a variable has no session value, the global state value is displayed. The LOCAL keyword has the same meaning as SESSION.

mysql> SHOW STATUS;

mysql> SHOW SESSION STATUS;

mysql> SHOW LOCAL STATUS;

Use the FLUSH STATUS command to reset the value of some status variables to 0:

mysql> FLUSH STATUS;

Set the value of a variable

There are several ways to modify the value of a system variable: in the MySQL configuration file, using MySQL startup options, or dynamically after MySQL starts using the SET command. See MySQL's official manual for a list of ways each variable supports.

When setting the value of a system variable, the default unit is bytes if the variable value is the size of the capacity, or the unit can be explicitly specified as K, M, or G(both upper and lower case can be used, indicating 1024, 10242, or 10243 bytes respectively). If the variable value is a file name, you can use an absolute path name, but if you use a relative path, the path is relative to the MySQL data directory.

The value of a dynamic variable can be modified at MySQL runtime using the SET command:

To indicate that you are modifying a global variable, use the GLOBAL keyword or add @@global. Modifier. Examples:

SET GLOBAL max_connections = 1000;

SET @@global.max_connections = 1000;

To indicate that you are modifying a session variable, use the SESSION keyword or add @@session. or @@ modifier. A client can only modify its own session variables and cannot modify those of other clients. Examples:

SET SESSION sql_mode = 'TRADITIONAL';

SET @@session.sql_mode = 'TRADITIONAL';

SET @@sql_mode = 'TRADITIONAL';

LOCAL and @@local. SESSION AND @@session. Synonymous, same function.

If no modifiers are provided, the SET command modifies session variables. But if that variable doesn't have a session value, an error is reported:

mysql> SET max_connections = 1000;

ERROR 1229 (HY000): Variable 'max_connections' is a

GLOBAL variable and should be set with SET GLOBAL

The SET command can set the values of multiple variables simultaneously, separated by commas. Examples:

SET @x = 1, SESSION sql_mode = '';

When multiple variables are set simultaneously, the closest GLOBAL or SESSION modifier is applied to the variable without the modifier following it. Examples:

SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;

SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;

SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

If one of the SET variables fails, the entire statement fails and the other variables are not modified.

After the SET command modifies the value of a session variable, the modification is immediately effective for the current session until you change it to another value or the current session terminates. Changes do not affect other sessions. When you change the value of a global variable, the change takes effect immediately, and the new value is remembered and applied to the new session until you change it to another value or the MySQL service is shut down. The value of a global variable affects only the value of the corresponding session variable, and therefore only new sessions, not existing sessions, or even the current session executing SET GLOBAL statements. To make the value of a global variable permanent, you should also set the value of the corresponding option in the MySQL configuration file.

To set the value of a global variable to MySQL's default value, or to set the value of a session variable to the value of the current global variable, set the variable's value to the keyword DEFAULT. For example, the following two statements are identical, both setting the session variable max_join_size to the current value of the corresponding global variable:

SET @@session.max_join_size=DEFAULT;

SET @@session.max_join_size=@@global.max_join_size;

Of course, not all system variables can be set to DEFAULT, which would result in an error. Assigning the value DEFAULT to user-defined variables, stored procedure or function parameters, or local variables of stored procedures is not allowed.

To reference the value of a system variable in an expression, use one of the @@ modifiers. For example, to get the value of a variable in a SELECT statement, you can:

SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

For values that refer to variables in expressions of the form @@var_name instead of @@global. or @@session.), MySQL returns the value of the session variable if it exists, otherwise it returns the value of the global variable. This is not the same as the statement SET @@var_name = expr, which always sets the value of the session variable.

In MySQL startup options, variable values can be explicitly specified in K, M, or G (case-insensitive), but not with the SET command; on the other hand, in MySQL startup options, variable values cannot be evaluated by expressions, while SET commands can. For example, the first one is OK, the second one is not:

[root@gw ~]# mysql --max_allowed_packet=16M //startup options, this usage can be

[root@gw ~]# mysql --max_allowed_packet=16*1024*1024 //startup options, this usage does not work

In contrast, the second of the following is OK, but the first is not:

mysql> SET GLOBAL max_allowed_packet=16M; //SET command, this usage does not work

mysql> SET GLOBAL max_allowed_packet=16*1024*1024; //SET command, this usage can be

custom variables

The SET command can also be used to set custom variables. Adding the @ modifier to a variable name means setting a custom variable. Variable names can consist of letters, numbers,". ","_"and"$". Of course, other characters can be included when referred to as strings or identifiers (e.g.@'my-var',@"my-var", or @`my-var`).

mysql> SET @x = 1;

Custom variables are session-level variables with scope limited to the current session. When the current session terminates, all session variables will be released, including custom variables. Use SELECT to view the value of a custom variable:

mysql> SELECT @x;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.