用户变量线程级可见会话级别、持久化级别的系统变量

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

MySQL :: MySQL 8.0 Reference Manual :: 9.4 User-Defined Variables https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

【用户变量线程级可见 查看所有会话的用户变量】

9.4 User-Defined Variables

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.

User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, ., _, and $. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @`my-var`).

User-defined variables are session specific. A user variable defined by one client cannot be seen or used by other clients. (Exception: A user with access to the Performance Schema user_variables_by_thread table can see all user variables for all sessions.) All variables for a given client session are automatically freed when that client exits.

User variable names are not case-sensitive. Names have a maximum length of 64 characters.

One way to set a user-defined variable is by issuing a SET statement:

SET @var_name = expr [, @var_name = expr] ...

5.1.8 Server System Variables

The MySQL server maintains many system variables that configure its operation. 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.

At runtime, setting a global system variable value normally requires the SYSTEM_VARIABLES_ADMIN or SUPER privilege. Setting a session system variable value normally requires no special privileges and can be done by any user, although there are exceptions. For more information, see Section 5.1.9.1, “System Variable Privileges”

There are several ways to see the names and values of system variables:

To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:
```
mysqld --verbose --help
```
To see the values that a server will use based only on its compiled-in defaults, ignoring the settings in any option files, use this command:
```
mysqld --no-defaults --verbose --help
```
To see the current values used by a running server, use the SHOW VARIABLES statement or the Performance Schema system variable tables. See Section 26.12.13, “Performance Schema System Variable Tables”.

This section provides a description of each system variable. For a system variable summary table, see Section 5.1.5, “Server System Variable Reference”. For more information about manipulation of system variables, see Section 5.1.9, “Using System Variables”.

For additional system variable information, see these sections:

Section 5.1.9, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.9.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
Information on tuning system variables can be found in Section 5.1.1, “Configuring the Server”.
Section 15.13, “InnoDB Startup Options and System Variables”, lists InnoDB system variables.
Section 22.3.3.9.2, “NDB Cluster System Variables”, lists system variables which are specific to NDB Cluster.
For information on server system variables specific to replication, see Section 17.1.6, “Replication and Binary Logging Options and Variables”.

Note

【会话级别、持久化级别的系统变量】

Many system variables are dynamic and can be changed at runtime by using the SET statement. For a list, see Section 5.1.9.2, “Dynamic System Variables”. To change a system variable with SET, refer to it by name, optionally preceded by a modifier. The following examples briefly illustrate this syntax:

Set a global system variable:

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;

Persist a global system variable to the mysqld-auto.cnf file (and set the runtime value):
```
SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;
```
Persist a global system variable to the mysqld-auto.cnf file (without setting the runtime value):
```
SET PERSIST_ONLY back_log = 1000;
SET @@PERSIST_ONLY.back_log = 1000;
```

Set a session system variable:

SET SESSION sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';

For complete details about SET syntax, see Section 13.7.5.1, “SET Syntax for Variable Assignment”. For a description of the privilege requirements for setting and persisting system variables, see Section 5.1.9.1, “System Variable Privileges”