MySQL variable list

Server system Variables (System variable)

The MySQL system variable (systems variables) refers to the various system variables of the MySQL instance, which are actually some system parameters that are used to initialize or set the database to occupy the system resources, the location of the files, and so on, these variables contain the default values of the parameters at MySQL compile time. or a parameter value configured in the MY.CNF configuration file. By default, system variables are lowercase letters.

Scope Range

System variables (systems variables) can be divided into session-level system variables and global-level system variables by scope scope. If you want to confirm that a system variable is a global or session level, if scope has a value of global or sessions, it means that the variable is both a system variable and a session-level system variable. If its scope has a value of global, it indicates that the system variable is a global-level system variable.

System level

View global values for system variables

SELECT * from Information_schema.global_variables;

SELECT * FROM Information_schema.global_variables

where variable_name= ' xx ';

SELECT * from Performance_schema.global_variables;

Session level

View current session values for system variables

SELECT * from Information_schema.session_variables;

SELECT * from information_schema.session_variables where variable_name= ' xxxx ';

SELECT * from Performance_schema.session_variables;

Distinguishing and manipulating 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, the session value is returned.

If a system variable is a global level, then the value at the current session is the value of the global level. For example, System variable automatic_sp_privileges, which is a global level system variable, but show session variables like '%automatic_sp_privileges% ' can find its value.

If you want to differentiate whether a system variable is global or session-level, you can do this in the following way:

Method 1: Check the scope property of the system variable in the official document.

Method 2: Use Set variable_name=xxx; If the error 1229 (HY000) is reported, it indicates that the variable is global and, if not, proves that the system variable is global and session two levels.

Modifying global-level system variables

SET GLOBAL max_connections=300;

SET @ @global. max_connections=300;

Note: Changing the value of a global variable requires super privilege

modifying session-Level system variables

SET @ @session. Max_join_size=default;

SET Max_join_size=default; --The default is session variables. If there is no level qualifier before the variable name, the session-level variable is modified.

SET SESSION Max_join_size=default;

If you modify a system global variable without specifying a global or @ @global, you will be quoted as "Variable ' xxx ' is a global Variable and should are set with SET global" error.

Dynamic variable static variable

System variables (systems variables) can be dynamically modified, and can be divided into system dynamic variables (dynamical system variables) and system static variables. How to differentiate between dynamic and static system variables? This can only be viewed in official documents, and the "dynamic" property of the system variable is yes, which means that it is dynamically modified.

Some system variables are read-only and cannot be modified. As shown below:

mysql> set global innodb_version= ' 5.6.21 ';

ERROR 1238 (HY000): Variable ' innodb_version ' is a read only Variable

Server Status Variables (servers state variable)

MySQL state variables (server status Variables) are some of the system state information that the current server accumulates after it has been started, such as the maximum number of connections, the cumulative interrupt connection, and so on, which is used primarily to evaluate current system resource usage to further analyze system performance and make appropriate adjustment decisions. The state variable is dynamically changing.

The state variable is read-only: It can be set and modified only by the MySQL server itself, is read-only for the user, cannot be set and modified by the SET statement, and 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 a zero value with the Flush status statement.

View state variables

For view status variables, the show status also supports like matching queries. As shown below:

Show status like '%variable_name% '

Show global status like '%variable_name% '

#当前测试环境

Mysql> Select Version () from dual;

Mysql> Show status; --View all the state variables

Mysql> show global status like ' aborted_connects% ';

Mysql> Show session status like ' aborted_connects% ';

Mysql> select * from Information_schema.global_status;

ERROR 3167 (HY000): the ' information_schema. Global_status ' feature is disabled; See the documentation for ' show_compatibility_56 '

Mysql> Show variables like '%show_compatibility_56% ';

It is flase to find out.

mysql> set global show_compatibility_56=on;

Mysql> select * from Information_schema.global_status;

So you can query it.

SELECT * from Performance_schema.global_status;

SELECT * from Performance_schema.session_status;

Note: After MySQL 5.7 system variables and state variables need to be obtained from Performance_schema, Information_schema still retains global_status,global_variables two tables for compatibility, If you want to follow the habit of querying in Information_schema, 5.7 provides the show_compatibility_56 parameter, set to on can be compatible with the usage before 5.7, or you will get an error (Error 3167 (HY000)).

user-defined Variables (user-defined variable)

A user-defined variable is a user-defined variable.

User-defined variables are based on the current session. This means that the scope of the user-defined variable is limited to the current session (connection), and a user-defined variable defined by one client cannot be seen or used by other clients.

Exception: Users who can access the Performance_schema.user_variables_by_thread table can see the defined user-defined variables for all sessions, but can only see which variables are defined by those sessions and cannot access them.

When a client session exits, all custom variables for the current session are automatically freed.

You can typically store values in a user-defined variable in an SQL statement, and then use another SQL statement to query a user-defined variable. In this way, values can be passed between different SQL.

User-defined variables are case insensitive, with a maximum length of 64 characters and a user-defined variable in the form of @var_name, where the variable name consists of letters, numbers, ".", "_", and "$". Of course, you can also include other special characters (for example: @ ' My-var ', @ "My-var", or @ ' My-var ') when referring to a string or identifier. When setting a variable with set, you can use the "=" or ": =" operator to assign a value. For set, you can use = or: = To assign a value, for select to use only: = To assign a value.

As shown below:

Mysql> SET @ $test 1= "Test";

Mysql> SELECT @ $test 1 from dual;

Mysql> Select CONNECTION_ID () from dual;

Returns the current connection ID. 149379

Mysql> SELECT c.id, b.thread_id from Performance_schema.threads B joins information_schema.processlist C on b.proces slist_id = c.id where c.id=149379;

+--------+-----------+

| ID | thread_id |

+--------+-----------+

|    149379 | 149404 |

+--------+-----------+

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 | 1200 |

+-----------+---------------+----------------+

1 row in Set (0.00 sec)

User-defined variable considerations, the following is a summary:

1: Undefined user-defined variable initial value is null

Mysql> select @kerry from dual;

Note: Using undefined variables does not produce any syntax errors, and because they are initialized to null values, it is very easy to make mistakes if you do not realize this.

As shown below:

Mysql> Select @num1, @num2: [email protected]+1 from dual;

+-------+-----------------+

| @num1 | @num2: [Email protected]+1 |

+-------+-----------------+

|            NULL | NULL |

+-------+-----------------+

2: User variable name is not sensitive to case.

3: The type of the custom variable is a dynamic type.

MySQL user-defined variables, not strictly restricting the data type, its data type according to the value you assign it to change at any time. Moreover, if the custom variable is given a numeric value, the progress is not guaranteed.

4: The Order of assignment and the point in time of assignment are not always fixed, depending on the optimizer's decision.

One of the most common problems with user-defined variables is the failure to notice that when assigning and reading user-defined variables, it may be at different stages of the query. For example, to assign a value in a SELECT statement and then read the user-defined variable in the WHERE clause, it is possible that the user-defined variable value is not what you think, as shown in the following example, because the where section takes precedence over the Select section in order of execution of the MySQL statement. So you'll see that the maximum value for MsgId and @rownum is 6.

Mysql> Select MsgId from Message order by MsgId limit 12;

+-------+

| MsgId |

+-------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 11 |

| 12 |

| 13 |

| 18 |

| 19 |

+-------+

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 <=5;

+-------+--------+

| MsgId | rownum |

+-------+--------+

|      1 | 1 |

|      2 | 2 |

|      3 | 3 |

|      4 | 4 |

|      5 | 5 |

|      6 | 6 |

+-------+--------+

6 rows in Set (0.00 sec)

Mysql> Select MsgId, @rownum: = @rownum +1 as RowNum

-From Message

where @rownum <=5;

Empty Set (0.00 sec)

Mysql> 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 you want, and this is where you need to be careful with your custom variables. Because the user-defined variable is also considered a "global variable" in the current session, it has become the @rownum after the 6,where condition <= 5 logic is false. A little caution will arise and deviate from the results you expect.

Do not assign values at the same time in the same non-set statement and use the same user-defined variables, because where and select are executed at different stages of query execution. If the order by is added to the query, the result may be more different;

mysql> Set @rownum: = 0;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select MsgId, @rownum: = @rownum +1 as RowNum

-From Message

where @rownum <=5;

+-------+--------+

| MsgId | rownum |

+-------+--------+

|      1 | 1 |

|      2 | 2 |

|      3 | 3 |

|      4 | 4 |

|      5 | 5 |

|      6 | 6 |

+-------+--------+

6 rows in Set (0.00 sec)

Mysql>

mysql> Set @rownum: = 0;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select MsgId, @rownum: = @rownum +1 as RowNum

-From Message

where @rownum <=5

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 |

+-------+--------+

All in Set (0.00 sec)

Mysql>

If you sort by msgid, then it's normal, what's the difference between those 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 <=5

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>

We'll look at the execution plan.

The official explanations are as follows:

In a SELECT statement, each select expression was evaluated only if sent to the client. This means. In a has, GROUP by, or ORDER by clause, referring to a variable so is assigned a value in the Select Expression list does not work as expected

In a 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 a value specified in the Select expression list does not work as expected. This means that the value of the user-defined variable is calculated after the result set is sent to the client.

Examples of official testing:

This explanation is more authoritative, but, somewhat puzzling, is the SQL execution order in which the where is before the select operation, but how does the first SQL statement explain it? One explanation is that "the MySQL optimizer might optimize these variables in some scenarios, which could lead to code not running as expected." "The solution to this problem is to have the assignment and value of the variable occur at the same stage of the execution of the query, as follows:

About user-defined variables, if used well, can write efficient and concise SQL statements, if used improperly, may also give themselves to the pit. It all depends on the person who uses it.

Official documentation also describes user-defined variables that are not suitable for use in scenarios. Excerpt from the following sections:

User variables may used in the most contexts where expressions is permitted. This does isn't 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 is intended to provide data values. They cannot is 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 variables

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

The distinction between local variables and user-defined variables lies in these areas:

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

2. Define variables in different ways. User-defined variables use SET statements, local variables are defined using the Declare statement

3. The range of functions is different. Local variables are only valid between Begin-end statement blocks. After the Begin-end statement block has run, the local variable disappears. The user-defined variable is valid for the current connection (session).

MySQL variable list