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

This article mainly explains "the detailed explanation of mysql global variables and local variables". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "mysql global variables and local variables explained in detail" it!

Usually when the server starts, each global variable is initialized to its default value (we can change these default values through the options specified on the command line or in the options file), and then the server also maintains a set of session variables for each connected client, which is initialized with the current value of the corresponding global variable when connecting.

Examples are as follows:

When the server starts, it initializes a system variable named default_storage_engine with scope of GLOBAL, and then whenever a client connects to the server, the server assigns to the client a system variable named default_storage_engine with scope of SESSION. The value of the system variable with scope of SESSION is initialized by the value of the system variable of the same name with the current scope of GLOBAL.

Obviously, the scope of the system variable set through the startup option is GLOBAL, which is valid for all clients, because no client program is connected when the system starts. Now that we understand the scope of GLOBAL and SESSION of system variables, let's take a look at the syntax for setting system variables through the client program while the server program is running:

SET [GLOBAL | SESSION] system variable name = value

Or write as follows:

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

For example, if we want to change the value of the system variable default_storage_engine with the scope of GLOBAL to MyISAM when the server is running, that is, we want all the clients newly connected to the server to use MyISAM as the default storage engine, then we can choose any of the following two statements to set it:

Statement 1:

SET GLOBAL default_storage_engine = MyISAM

Statement 2:

SET @ @ GLOBAL.default_storage_engine = MyISAM

If you only want to take effect on this client, you can also select any of the following three statements to set it:

Statement 1:

SET SESSION default_storage_engine = MyISAM

Statement 2:

SET @ @ SESSION.default_storage_engine = MyISAM

Statement 3:

SET default_storage_engine = MyISAM

As can be seen from statement 3 above, if the scope is omitted in the statement that sets the system variable, the default scope is SESSION. That is, the SET system variable name = value and the SET SESSION system variable name = value are equivalent.

View system variables with different scopes

Since system variables are scoped, what scope of system variables does our SHOW VARIABLES statement look at?

Answer: the default view is the system variable of the scope of SESSION.

Of course, we can also add the system variable to the statement that looks at the system variable, like this:

SHOW [GLOBAL | SESSION] VARIABLES [LIKE matching pattern]; now that you have a better understanding of "detailed explanation of mysql global and local variables", you might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.