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

What are the categories of variables in MySQL? in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

In MySQL, my.cnf is a parameter file (Option Files), which is similar to the spfile and pfile parameter files in ORACLE database. In theory, the parameters in my.cnf are all system parameters (this name is more in line with the habit of thinking), but it is officially called system variable (system variables), so in the end it is called system parameter or system variable (system variables)? This used to be a difficult question for me, because there are all kinds of variables in MySQL, and sometimes the language is so broad and profound; I believe many people are more or less confused about this problem. In fact, put aside these nouns, they are the same thing (thing), whether you call it a system variable (system variables) or system parameters, there is no need to be so tangled. Just like Wang San, some people call him Wang San, while others call him Wang pockmarked by the same nickname.

In addition, there are many variable types in MySQL, which can sometimes be a little confusing. This article intends to summarize the various variable types in the MySQL database and sort out the concepts of various variable types. Can have a clear train of thought from the overall situation. For more information on MySQL variable types, please see the following figure:

Server System Variables (system variable)

MySQL system variables (system variables) refer to various system variables of MySQL instances, which are actually some system parameters, which are used to initialize or set the database's occupation of system resources, file storage location, and so on. These variables include the default values of MySQL compilation parameters, or the parameter values configured in the my.cnf configuration file. By default, system variables are lowercase letters. The official documents are as follows:

The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically at runtime using the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can also use system variable values in expressions.

System variables (system variables) can be divided into session-level system variables and global-level system variables according to scope. If you want to confirm whether the system variable is global-level or session-level, you can refer to the official documentation. If the value of Scope is GLOBAL or SESSION, it means that the variable is both a global-level system variable and a session-level system variable. If its Scope value is GLOBAL, the system variable is a global-level system variable.

-View the global value of the system variable

Select * from information_schema.global_variables; select * from information_schema.global_variables where variable_name='xxxx'; select * from performance_schema.global_variables

-View the current session value of the system variable

Select * from information_schema.session_variables; select * from information_schema.session_variables where variable_name='xxxx'; select * from performance_schema.session_variables; SELECT @ @ global.sql_mode, @ @ session.sql_mode, @ @ sql_mode; mysql > show variables like'% connect_timeout%'; mysql > show local variables like'% connect_timeout%'; mysql > show session variables like'% connect_timeout%'; mysql > show global variables like'% connect_timeout%'

Note: for SHOW VARIABLES, if you do not specify GLOBAL, SESSION, or LOCAL,MySQL to return the session value, you want to distinguish whether the system variable is global or session-level. You cannot use the following way, if a system variable is global-level, then the value in the current session is also a global-level value. For example, the system variable AUTOMATIC_SP_PRIVILEGES, which is a global-level system variable, can also be found by show session variables like'% automatic_sp_privileges%'. So there is no way to tell whether the system variable is at the session level or at the global level.

Mysql > show session variables like'% automatic_sp_privileges%' +-+ | Variable_name | Value | +-+-+ | automatic_sp_privileges | ON | +-+ -+ 1 row in set (0.00 sec) mysql > select * from information_schema.global_variables-> where variable_name='automatic_sp_privileges' +-+-+ | VARIABLE_NAME | VARIABLE_VALUE | +-- + | AUTOMATIC_SP_PRIVILEGES | ON | +- -+-+ 1 row in set 1 warning (0.00 sec) mysql >

If you want to distinguish whether the system variable is global or session-level, you can use the following ways:

Method 1: look up the Scope attribute of the system variable in the official document.

Method 2: use SET VARIABLE_NAME=xxx; if you report ERROR 1229 (HY000), it means that the variable is global, and if you do not report an error, then prove that the system variable is global and session level.

Mysql > SET AUTOMATIC_SP_PRIVILEGES=OFF; ERROR 1229 (HY000): Variable 'automatic_sp_privileges' is a GLOBAL variable and should be set with SET GLOBAL

You can use the SET command to modify the value of the system variable, as follows:

Modify the global level system variable:

SET GLOBAL max_connections=300; SET @ @ global.max_connections=300

Note: SUPER permission is required to change the value of a global variable

Modify the session-level system variable:

SET @ @ session.max_join_size=DEFAULT; SET max_join_size=DEFAULT;-- defaults to session variables. If there is no level qualifier before the variable name, it means that the session-level variable is modified. SET SESSION max_join_size=DEFAULT

If you modify the system global variable without specifying GLOBAL or @ @ global, an error such as "Variable 'xxx' is a GLOBAL variable and should be set with SET GLOBAL" will be reported.

Mysql > set max_connections=300; ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL mysql > set global max_connections=300; Query OK, 0 rows affected (0 sec) mysql >

According to whether the system variable (system variables) can be dynamically modified, it can be divided into system dynamic variable (Dynamic System Variables) and system static variable. How to tell the difference between dynamic and static system variables? This can only be viewed in the official documentation. The "Dynamic" property of the system variable is Yes, which means that it can be modified dynamically. For more information on Dynamic Variable, please see https://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html.

In addition, some system variables are read-only and cannot be modified. As follows:

Mysql > mysql > set global innodb_version='5.6.21'; ERROR 1238 (HY000): Variable 'innodb_version' is a read only variable mysql >

In addition, there is another concept of Structured System Variables, that is, a system variable is a Strut, which is officially introduced as follows:

Structured System Variables

A structured variable differs from a regular system variable in two respects:

Its value is a structure with components that specify server parameters considered to be closely related.

There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.

Server Status Variables (server status variable)

MySQL state variable (Server Status Variables) is some system state information accumulated by the current server since startup, such as the number of * * connections, accumulated interrupted connections and so on. It is mainly used to evaluate the current use of system resources in order to further analyze system performance and make corresponding adjustment decisions. This estimate will be confused with system variables. In fact, state variables are dynamic. In addition, state variables are read-only: they can only be set and modified by the MySQL server itself. For users, they are read-only, and they cannot be set and modified through SET statements, while system variables can be modified at any time. State variables are also divided into session-level and global-level state information. Some state variables can be reset to zero using the FLUSH STATUS statement.

With regard to viewing status variables, show status also supports like matching queries. As follows:

Show status like'% variable_name%' show global status like'% variable_name%' # current test environment ysql > select version () from dual;-+ version () |-+ 5.7.21 |-+ row in set (0.00 sec)

Mysql > show status;-- View all status variables

Ysql > show global status like 'Aborted_connects%' -+-+ Variable_name | Value |-+-+ Aborted_connects | 2 |-+-+ row in set (0.01sec) ysql > show session status like 'Aborted_connects%' -+-+ Variable_name | Value |-+-+ Aborted_connects | 2 |-+-+ row in set (0.00 sec) ysql > select * from information_schema.global_status RROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for' show_compatibility_56' ysql > # ysql > show variables like'% show_compatibility_56%' -+-+ Variable_name | Value |-+-+ show_compatibility_56 | OFF |-+-+ Row in set (0.00 sec) ysql > set global show_compatibility_56=on Uery OK, 0 rows affected (0.00 sec) ysql > select * from information_schema.global_status -+-+ VARIABLE_NAME VARIABLE_VALUE | |-+-+ ABORTED_CLIENTS | 138097 | | | ABORTED_CONNECTS | 5 | BINLOG_CACHE_DISK_USE | 0 | BINLOG_CACHE_USE | 0 | |. Select * from performance_schema.global_status; select * from performance_schema.session_status

Note: system variables and state variables need to be obtained from performance_schema after MySQL 5.7. information_schema still retains the compatibility of the two tables of GLOBAL_STATUS,GLOBAL_VARIABLES. If you want to follow the habit of querying in information_schema, 5.7.provides the show_compatibility_56 parameter, which is set to ON to be compatible with the usage before 5.7. otherwise, an error will be reported (ERROR 3167 (HY000)).

User-Defined Variables (user-defined variable)

User-defined variables, as the name implies, are user-defined variables. User-defined variables are based on the current session. In other words, the scope of user-defined variables is limited to the current session (connection), and user-defined variables defined by one client cannot be seen or used by other clients. (exception: users who have access to the performance_schema.user_variables_by_ thread table can see user-defined variables for all sessions, but of course only those sessions have defined variables and cannot access them. ). When a client session exits, all custom variables for the current session are automatically released.

Generally, you can store the value in the user-defined variable in the SQL statement, and then use another SQL statement to query the user-defined variable. This way, values can be passed between different SQL.

In addition, user-defined variables are case-insensitive. The length of user-defined variables is 64 characters. User-defined variables are generally in the form of @ var_name, where the variable name consists of letters, numbers, ".", "_" and "$". Of course, you can also include other special characters when referencing as a string or identifier (for example: @ 'my-var',@ "my-var", or @ `my- var`). When you use SET to set variables, you can assign values using the "=" or ": =" operators. For SET, you can use = or: = to assign values, and for SELECT you can only use: = to assign values. As follows:

Mysql > set @ $test1= "test"; Query OK, 0 rows affected (0.00 sec) mysql > select @ $test1 from dual; +-+ | @ $test1 | +-+ | test | +-+ 1 row in set (0.00 sec) mysql > mysql > set @ "ac#k": = 'kerry'; Query OK, 0 rows affected (0.00 sec) mysql > select @ "ac#k" from dual +-+ | @ "ac#k" | +-+ | kerry | +-+ 1 row in set (0.00 sec) mysql > mysql > select version () from dual +-+ | version () | +-+ | 5.7.21 | +-+ 1 row in set (0.00 sec) mysql > mysql > set @ my_test=1200; Query OK, 0 rows affected (0.00 sec) mysql > select @ my_test +-+ | @ my_test | +-+ | 1200 | +-+ 1 row in set (0.00 sec) mysql > select connection_id () from dual +-+ | connection_id () | +-+ | 149379 | +-+ 1 row in set (sec) mysql > SELECT c.id -> b.thread_id-> FROM performance_schema.threads b-> join information_schema.processlist c-> ON b.processlist_id = c.id-> where c.id=149379 +-+-+ | id | thread_id | +-+-+ | 149379 | 149404 | +-+-+ 1 row in set (0.00 sec) mysql > select @ My_Test, @ my_TEST from dual +-+-+ | @ My_Test | @ my_TEST | +-+-+ | 1200 | 1200 | +-+-+ 1 row in set (sec) mysql >

Mysql > select connection_id () from dual; +-+ | connection_id () | +-+ | 151821 | +-+ 1 row in set (sec) mysql > select @ my_test from dual +-+ | @ my_test | +-+ | NULL | +-+ 1 row in set (0.00 sec) mysql > select * from performance_schema.user_variables_by_thread +-+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-+ | 149404 | my_test | 0 | +-+ 1 row in set (1200 sec) mysql >

Considerations for user-defined variables, the following is a summary:

Summary

1 the initial value of an undefined user-defined variable is NULL

Mysql > select @ kerry from dual; +-+ | @ kerry | +-+ | NULL | +-+ 1 row in set (0.00 sec)

Note: using an undefined variable does not cause any syntax errors, because it is initialized to a null value, and it is very easy to make mistakes if you are not aware of this. As follows:

Mysql > select @ num1, @ num2: = @ num1+1 from dual; +-+-+ | @ num1 | @ num2: = @ num1+1 | +-+-+ | NULL | NULL | +-+-+ 1 row in set (0.00 sec) mysql >

2 user variable names are not case-sensitive (as described above, abbreviated here)

3 the type of a custom variable is a dynamic type

User-defined variables in MySQL do not strictly restrict the data type, and its data type changes from time to time according to the value you assign to it. And if custom variables are assigned numeric values, there is no guarantee of progress. Official document introduction:

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types is converted to a permissible type. For example, a value having a temporal or spatial data type is converted to a binary string. A value having the JSON data type is converted to a string with a character set of utf8mb4 and a collation of utf8mb4_bin.

4 the order and time of assignment are not always fixed, which depends on the decision of the optimizer.

One of the most common problems with using user-defined variables is not noticing that they may be at different stages of the query when assigning and reading user-defined variables. For example, if you assign a value in the SELECT statement and then read the user-defined variable in the WHERE clause, you may not have the user-defined variable value as you might think, as shown in the following example, because the WHERE part takes precedence with the SELECT part in the order in which the MySQL statement is executed, so you will see that the msgid and @ rownum have a * * value of 6.

Mysql > select msgid from message order by msgid limit 12; +-+ | msgid | +-+ | 1 | | 2 | 3 | 4 | 5 | 6 | 7 | 11 | 12 | 13 | 18 | 19 | +-+ 12 rows in set (0.00 sec) mysql > set @ rownum: = 0 Query OK, 0 rows affected (0.00 sec) mysql > select msgid, @ rownum: = @ rownum + 1 as rownum-> from message-> where @ rownum select msgid, @ rownum: = @ rownum + 1 as rownum-> from message-> where @ rownum select @ rownum from dual +-+ | @ rownum | +-+ | 6 | +-+ 1 row in set (0.00 sec) mysql >

As shown above, the second query may have deviated from the actual logic, which is where you need to be careful when using custom variables. Because the user-defined variable is also a "global variable" in the current session, it has become @ rownum set @ rownum: = 0; Query OK, 0 rows affected (0.00 sec) mysql > select msgid, @ rownum: = @ rownum + 1 as rownum-> from message-> where @ rownum mysql > set @ rownum: = 0 Query OK, 0 rows affected (0.00 sec) mysql > select msgid, @ rownum: = @ rownum + 1 as rownum-> from message-> where @ rownum order by msgcontent +-+-+ | msgid | rownum | +-+-+ | 20 | 1 | 28 | 2 | 43 | 3 | 47 | 4 | . | | 22 | 57 | 69 | 58 | 40 | 59 | | 52 | 60 | 24 | 61 | 66 | 62 | 51 | 63 | +-+-+ 63 rows in set (0.00 sec) mysql > |

If you sort by msgid, then it's normal, so what's the difference between the three?

Mysql > set @ rownum: = 0; Query OK, 0 rows affected (0.00 sec) mysql > select msgid, @ rownum: = @ rownum + 1 as rownum-> from message-> where @ rownum order by msgid +-+-+ | msgid | rownum | +-+-+ | 1 | 1 | 2 | 2 | 3 | 3 | 4 | 4 | 5 | 5 | 6 | 6 | +-+ 6 rows in set (0.00 sec) mysql >

Let's look at the implementation plan first.

The official explanation is as follows:

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected

In the SELECT statement, each selection expression is evaluated only when it is sent to the client. This means that in the HAVING,GROUP BY or ORDER BY clause, a user-defined variable that references the value specified in the list of selection expressions does not work as expected. That is, the value of the user-defined variable is not calculated until the result set is sent to the client.

An official example of testing:

This explanation is authoritative, but what is a little puzzling is that in the order of SQL execution, WHERE comes before the SELECT operation, but what about * SQL statements? One explanation is that "the MySQL optimizer may optimize these variables in some scenarios, which may cause the code to not run as expected." The solution to this problem is to have the assignment and evaluation of the variable occur at the same stage of the query execution, as shown below:

With regard to user-defined variables, if you use them well, you can write efficient and concise SQL statements, and you may cheat yourself if you don't use them properly. It all depends on the person who uses it.

The official documentation also describes that user-defined variables are not suitable for use in scenarios. The excerpt is as follows:

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT.

Local variable

Local variable: the scope of action is between begin and end statement blocks. The variable set in the statement block. The declare statement is used specifically to define and declare local variables.

The difference between local variables and user-defined variables lies in the following aspects:

User-defined variables start with "@". Local variables do not have this symbol.

Variables are defined in different ways. User-defined variables use set statements, and local variables are defined using declare statements.

The scope of action is different. Local variables are valid only between blocks of begin-end statements. After the block of begin-end statements is run, the local variables disappear. The user-defined variable is valid for the current connection (session).

This is the answer to the question about the classification of variables in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.