Detailed explanation of mysql global variables and local variables

Usually when the server starts, each global variable is initialized to its default value (we can change these default values ​​through the command line or options specified in the option file), and then the server A set of session variables is also maintained for each connected client, with the client's session variables initialized at connection time using the current values ​​of the corresponding global variables.

An example is as follows:

When the server starts, a system variable named default_storage_engine with a scope of GLOBAL will be initialized. After that, whenever a client connects to When the server is installed, the server will separately allocate a system variable named default_storage_engine with a scope of SESSION to the client. The value of the system variable with a scope of SESSION is initialized according to the value of the system variable with the same name that currently has a scope of GLOBAL.

(Free learning video tutorial recommendation: mysql video tutorial)

Obviously, the scope of system variables set through startup options are all GLOBAL, that is, for All clients are valid because no client program has connected when the system is started. After understanding the GLOBAL and SESSION scope of system variables, let's take a look at the syntax for setting system variables through the client program during the running of the server program:

SET [GLOBAL|SESSION] 系统变量名 = 值;

or the following writing:

SET [@@(GLOBAL|SESSION).]var_name = XXX;

For example We want to change the value of the system variable default_storage_engine with a scope of GLOBAL to MyISAM during the running of the server. That is, we want all new clients connected to the server to use MyISAM as the default storage engine. Then we can choose the following two Use any one of the statements to set:

Statement one:

SET GLOBAL default_storage_engine = MyISAM;

Statement two:

SET @@GLOBAL.default_storage_engine = MyISAM;

If you only want to take effect for this client, you can also choose one of the following three statements. Set any one of them:

Statement one:

SET SESSION default_storage_engine = MyISAM;

Statement two:

SET @@SESSION.default_storage_engine = MyISAM;

Statement three:

SET default_storage_engine = MyISAM;

You can also use statement three above It can be seen that if the scope is omitted in the statement that sets the system variable, the default scope is SESSION. In other words, SET system variable name = value and SET SESSION system variable name = value are equivalent.

View system variables with different scopes

Since system variables have different scopes, what scope of system variables does our SHOW VARIABLES statement check?

Answer: By default, the system variables in the SESSION scope are viewed.

Of course, we can also add the system variables of which scope we want to check on the statement to check the system variables, like this:

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

Recommended related articles and tutorials: mysql tutorial

The above is the detailed content of Detailed explanation of mysql global variables and local variables. For more information, please follow other related articles on the PHP Chinese website!