Shulou(Shulou.com)05/31 Report--

MySQL in which system variables, many novices are not very clear, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

What are the applications of MySQL system variables

MySQL system variabl

For example, the total number of people in the company, many stored procedures need to call this value, but because this value doesn't change very often, it doesn't need to be count every time.

So most people will consider cache it, then the global variable provided by mysql is a good storage place.

2, database configuration center

First of all, this is not necessarily a good solution, especially for large distributed systems. But when I think of it, I will share it with you.

Mainly uses a three-tier, four-tier architecture of the system, the essential database layer to achieve.

As we all know, the configuration of the application is mostly realized by the configuration file or the configuration server, and the former needs to be packaged and deployed with the project.

For the latter, just go to the configuration center to get the configuration when the application is initialized or running. (both are used on Taobao.)

The database configuration center, in fact, is similar to the configuration server, but flexibly uses the variable mechanism of mysql.

It can inherit most of the advantages of configuring the server, but the biggest feature is that it can be easily referenced in sql,function,procedure. (it is more convenient than using a meter.)

And it has its own cache, if transplanted, mysql has to be exported very quickly.

So small system, want to quickly build a configuration center, the use of mysql feels good.

Invocation syntax:

Select*fromuser_infowhereid=@@global.admin_id-- example, which fetches user information through the administrator id configured globally

The following are some common commands for global variables, which are relatively simple:

The growth value of the setglobalauto_increment_increment=1;-- setting sequence

Showglobalvariables;-- displays all global variables

Showglobalvariableslike'%test%'-- queries global variables that contain test strings

Session system variable, mainly used in the life cycle of the current client connection. The value of its variable is a copy of the global variable.

If the connection is disconnected, the changes made to the current session variable will be reset.

For example, the server session variable autovariant defaults to true, and if you set false on a non-connected client connection, the connection will be disconnected after executing sql. After that, if you create a new connection to execute sql,autocommit, it will default to true.

The usage scenario of session variable is similar to that of the global, except that the life cycle is different, so it can be used to count the number of sql requests in the same connection, sql type and other information.

Some common operations of session variables:

The growth value of the setsessionauto_increment_increment=1;-- setting sequence

If showvariables;orshowsessionvariables;-- is not specified, the session variable is used by default

Showvariableslike'%test%'orshowsessionvariableslile'%test%'-- queries session variables that contain test strings.

How to use the MySQL system variable

MySQL has access to many system and connection variables. Many variables can be changed dynamically while the server is running. This usually allows you to modify the server operation without having to stop and restart the server.

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

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

The server also maintains a series of session variables for each connected client. The session variable of the client is initialized with the current value of the corresponding global variable when connecting. For dynamic session variables, the client can change them through SETSESSIONvar_name statements. No special permissions are required to set session variables, but clients can only change their own session variables, not those of other clients.

Changes to a global variable can be seen by any client that accesses the global variable. However, it only affects the corresponding session variables initialized from this global variable for the connected customer after the change. Does not affect the session variables of the currently connected client (even if the client executes the SETGLOBAL statement).

You can use several syntax forms 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 > SETGLOBALsort_buffer_size=value

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.