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

This article mainly introduces "variable description in mysql". In daily operation, I believe many people have doubts about variable description in mysql. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "variable description in mysql". Next, please follow the editor to study!

Similar to the parameter file of Oracle, the option file of MySQL (such as my.cnf) is used to configure the MySQL server, but it is different from the name of Oracle. In MySQL, it is officially called variable (Varialbes), but actually it is OK to call it parameter, as long as you understand that the two are the same thing.

There are two types of variables for MySQL:

1) system variable: configure the running environment of the MySQL server, which can be viewed with show variables

2) status variable: monitor the running status of the MySQL server, which can be viewed with show status

System variable

System variables can be divided into the following two categories according to their scope:

1) divided into global (GLOBAL) level: valid for the entire MySQL server

2) session (SESSION or LOCAL) level: only the current session is affected

Some variables have both levels, and MySQL will initialize session-level variables with global-level variables when establishing a connection, but once the connection is established, changes in global-level variables will not affect session-level variables.

View the value of the system variabl

You can view the value of the system variable through the show vairables statement:

[sql]

Mysql > show variables like 'log%'

Mysql > show variables where Variable_name like 'log%' and value='ON'

Note: show variables gives priority to the value of session-level variables, and if this value does not exist, displays the value of global-level variables. Of course, you can also add the difference between GLOBAL or SESSION keywords:

[sql]

Show global variables

Show session/local variables

When writing some stored procedures, you may need to reference the value of the system variable, using the following methods:

[sql]

@ @ GLOBAL.var_name

@ @ SESSION.var_name or

@ @ LOCAL.var_name

If there is no level qualifier before the variable name, the session-level value takes precedence.

The final way to see the value of a variable is from the GLOBAL_VARIABLES and SESSION_VARIABLES tables in the INFORMATION_SCHEMA database.

Set and modify the value of the system variable

When the MySQL server starts, there are two ways to set the value of the system variable:

1) Command line parameters, such as: mysqld-- max_connections=200

2) options file (my.cnf)

After the MySQL server starts, if you need to change the value of the system variable, you can use the SET statement:

[plain]

SET GLOBAL var_name = value

SET @ @ GLOBAL.var_name = value

SET SESSION var_name = value

SET @ @ SESSION.var_name = value

If there is no level qualifier before the variable name, it means that the session-level variable is modified.

Note: unlike at startup, variables set at run time are not allowed to use the suffix letters'K','M', etc., but expressions can be used to achieve the same effect, such as:

[sql]

SET GLOBAL read_buffer_size = 2 "1024" 1024

One of the things that is easy to confuse people here is that if you use show variables when querying, you will find that the settings do not seem to take effect, because simply using show variables is equivalent to using show session variables, querying session variables, and querying global variables only by using show global variables.

Many people on the Internet complain that they don't find any change after using show variables query after set global, because they confuse session variables with global variables. If you just want to modify session variables, you can use syntax like set wait_timeout=10; or set session wait_timeout=10;.

State variable

State variables enable us to keep abreast of the health of the MySQL server, which can be viewed using the show status statement.

State variables are similar to the same variables, and are also divided into global level and session level. Show status also supports like matching query, but the big difference is that state variables can only be set and modified by the MySQL server itself, which is read-only for users, and cannot be set and modified through SET statements.

At this point, the study of "variable description in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.