MySQL 服务器维护了许多配置其操作的系统变量。第 7.1.8 节，“服务器系统变量”，描述了这些变量的含义。每个系统变量都有一个默认值。系统变量可以在服务器启动时通过命令行选项或配置文件设置。大多数系统变量可以在服务器运行时通过 SET 语句动态修改，从而在无需停止和重启服务器的情况下更改其操作。你也可以在表达式中使用系统变量的值。The MySQL server maintains many system variables that configure its operation. Section 7.1.8, “Server System Variables”, describes the meaning of these variables. 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 while the server is running by means of 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.

许多系统变量是内置的。系统变量也可以由服务器插件或组件安装：Many system variables are built in. System variables may also be installed by server plugins or components:

系统变量存在两个作用范围。全局变量影响服务器的整体运行。会话变量影响其对单个客户端连接的操作。给定的系统变量可以同时具有全局值和会话值。全局和会话系统变量的关系如下：There are two scopes in which system variables exist. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections. A given system variable can have both a global and a session value. Global and session system variables are related as follows:

系统变量值可以在服务器启动时通过命令行或选项文件的选项全局设置。在启动时，系统变量的语法与命令选项相同，因此在变量名称中，短横线和下划线可以互换使用。例如，--general_log=ON 和 --general-log=ON 是等效的。System variable values can be set globally at server startup by using options on the command line or in an option file. At startup, the syntax for system variables is the same as for command options, so within variable names, dashes and underscores may be used interchangeably. For example, --general_log=ON and --general-log=ON are equivalent.

当你使用启动选项来设置一个接受数值的变量时，该值可以使用后缀 K、M、G、T、P 或 E（大写或小写均可）来表示乘以 1024、1024²、1024³、1024⁴、1024⁵ 或 1024⁶；也就是说，分别表示千字节、兆字节、千兆字节、太字节、拍字节或艾字节。因此，以下命令以 256 千字节的排序缓冲区大小和 1 千兆字节的最大数据包大小启动服务器：When you use a startup option to set a variable that takes a numeric value, the value can be given with a suffix of K, M, G, T, P, or E (either uppercase or lowercase) to indicate a multiplier of 1024, 1024², 1024³, 1024⁴, 1024⁵, or 1024⁶; that is, units of kilobytes, megabytes, gigabytes, terabytes, petabytes, or ettabytes, respectively. Thus, the following command starts the server with a sort buffer size of 256 kilobytes and a maximum packet size of one gigabyte:

mysqld --sort-buffer-size=256K --max-allowed-packet=1G

在一个选项文件中，这些变量是这样设置的：Within an option file, those variables are set like this:

[mysqld]
sort_buffer_size=256K
max_allowed_packet=1G

后缀字母的大小写无关紧要；256K 和 256k 等价，1G 和 1g 也等价。The lettercase of suffix letters does not matter; 256K and 256k are equivalent, as are 1G and 1g.

要限制系统变量在运行时通过 SET 语句可以设置的最大值，可在服务器启动时使用形式为 --maximum-var_name=value 的选项来指定此最大值。例如，要防止 sort_buffer_size 的值在运行时被增加到超过 32MB，请使用选项 --maximum-sort-buffer-size=32M。To restrict the maximum value to which a system variable can be set at runtime with the SET statement, specify this maximum by using an option of the form --maximum-var_name=value at server startup. For example, to prevent the value of sort_buffer_size from being increased to more than 32MB at runtime, use the option --maximum-sort-buffer-size=32M.

许多系统变量是动态的，可以在运行时使用SET语句进行更改。有关列表，请参见第 7.1.9.2 节，“动态系统变量”。要使用SET更改系统变量，请按名称引用该变量，可选择添加修饰符。在运行时，系统变量名必须使用下划线，而不是连字符。以下示例简要说明了此语法：Many system variables are dynamic and can be changed at runtime by using the SET statement. For a list, see Section 7.1.9.2, “Dynamic System Variables”. To change a system variable with SET, refer to it by name, optionally preceded by a modifier. At runtime, system variable names must be written using underscores, not dashes. The following examples briefly illustrate this syntax:

有关 SET 语法的详细信息，请参见 第 15.7.6.1 节，“变量赋值的 SET 语法”。有关设置和持久化系统变量所需权限的说明，请参见 第 7.1.9.1 节，“系统变量权限”For complete details about SET syntax, see Section 15.7.6.1, “SET Syntax for Variable Assignment”. For a description of the privilege requirements for setting and persisting system variables, see Section 7.1.9.1, “System Variable Privileges”

在服务器启动时设置变量时，可以使用用于指定值倍增的后缀，但不能在运行时通过 SET 设置值。另一方面，通过 SET，你可以使用表达式为变量赋值，而在服务器启动时设置变量时则不行。例如，下面的第一行在服务器启动时是合法的，但第二行则不合法：Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not:

$> mysql --max_allowed_packet=16M
$> mysql --max_allowed_packet=16*1024*1024

相反，以下两行中，第二行在运行时是合法的，而第一行则不合法：Conversely, the second of the following lines is legal at runtime, but the first is not:

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;

要显示系统变量的名称和值，请使用 SHOW VARIABLES 语句：To display system variable names and values, use the SHOW VARIABLES statement:

mysql> SHOW VARIABLES;
+-------------------------------------------------------+----------------------+
| Variable_name                                         | Value                |
+-------------------------------------------------------+----------------------+
| activate_all_roles_on_login                           | OFF                  |
| admin_address                                         |                      |
| admin_port                                            | 33062                |
| admin_ssl_ca                                          |                      |
| admin_ssl_capath                                      |                      |
| admin_ssl_cert                                        |                      |
| admin_ssl_cipher                                      |                      |
| admin_ssl_crl                                         |                      |
| admin_ssl_crlpath                                     |                      |
| admin_ssl_key                                         |                      |
| admin_tls_ciphersuites                                |                      |
| admin_tls_version                                     | TLSv1.2,TLSv1.3      |
| authentication_policy                                 | *,,                  |
| auto_generate_certs                                   | ON                   |
| auto_increment_increment                              | 1                    |
| auto_increment_offset                                 | 1                    |
| autocommit                                            | ON                   |
| automatic_sp_privileges                               | ON                   |

...

| version                                               | 8.4.0                |
| version_comment                                       | Source distribution  |
| version_compile_machine                               | x86_64               |
| version_compile_os                                    | Linux                |
| version_compile_zlib                                  | 1.2.13               |
| wait_timeout                                          | 28800                |
| warning_count                                         | 0                    |
| windowing_use_high_precision                          | ON                   |
| xa_detach_on_prepare                                  | ON                   |
+-------------------------------------------------------+----------------------+

使用 LIKE 子句，该语句只显示与模式匹配的变量。要获取特定的变量名，请使用如下所示的 LIKE 子句：With a LIKE clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, use a LIKE clause as shown:

SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';

要获取名称匹配特定模式的变量列表，请在 LIKE 子句中使用 % 通配符字符：To get a list of variables whose name match a pattern, use the % wildcard character in a LIKE clause:

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

通配符字符可以在要匹配的模式中的任何位置使用。严格来说，因为 _ 是匹配任意单个字符的通配符，所以如果要字面匹配它，应该转义为 \_。在实际操作中，这很少是必要的。Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because _ is a wildcard that matches any single character, you should escape it as \_ to match it literally. In practice, this is rarely necessary.

对于 SHOW VARIABLES，如果既未指定 GLOBAL 也未指定 SESSION，MySQL 会返回 SESSION 的值。For SHOW VARIABLES, if you specify neither GLOBAL nor SESSION, MySQL returns SESSION values.

要求在设置仅适用于 GLOBAL 的变量时使用 GLOBAL 关键字，而在检索它们时不需要使用该关键字的原因，是为了防止将来出现问题：The reason for requiring the GLOBAL keyword when setting GLOBAL-only variables but not when retrieving them is to prevent problems in the future: