5.1.8 使用系统变量

MySQL 服务器维护许多配置其操作的系统变量。 第 5.1.7 节“服务器系统变量”,描述了这些变量的含义。每个系统变量都有一个默认值。可以在服务器启动时使用命令行或选项文件中的选项设置系统变量。可以在服务器运行时通过SET语句动态更改大多数参数,这使您无需停止并重新启动服务器即可修改服务器的操作。您还可以在 table 达式中使用系统变量值。

内置了许多系统变量。服务器插件实现的系统变量在安装插件时公开,并且其名称以插件名称开头。例如,audit_log插件实现了一个名为audit_log_policy的系统变量。

系统变量存在两个范围。全局变量会影响服务器的整体操作。会话变量会影响单个 Client 端连接的操作。给定的系统变量可以具有全局值和会话值。全局和会话系统变量之间的关系如下:

  • 服务器启动时,它将每个全局变量初始化为其默认值。这些默认值可以通过在命令行或选项文件中指定的选项来更改。 (请参阅第 4.2.2 节“指定程序选项”。)

  • 服务器还为每个连接的 Client 端维护一组会话变量。Client 端的会话变量在连接时使用相应全局变量的当前值进行初始化。例如,Client 端的 SQL 模式由会话sql_mode值控制,该值在 Client 端连接到全局sql_mode值的值时初始化。

对于某些系统变量,不会从相应的全局值初始化会话值;如果是这样,则在变量描述中指出。

可以在服务器启动时通过使用命令行或选项文件中的选项来全局设置系统变量值。启动时,系统变量的语法与命令选项的语法相同,因此在变量名称中,破折号和下划线可以互换使用。例如,--general_log=ON--general-log=ON是等效的。

当您使用启动选项来设置一个采用数字值的变量时,可以给该值加上后缀KMG(大写或小写),以 table 示乘数 1024、10242 或 10243.也就是说,分别以千字节,兆字节或千兆字节为单位。因此,以下命令以InnoDB日志文件大小为 16 兆字节,最大数据包大小为 1 GB 来启动服务器:

mysqld --innodb-log-file-size=16M --max-allowed-packet=1G

在选项文件中,这些变量的设置如下:

[mysqld]
innodb_log_file_size=16M
max_allowed_packet=1G

后缀字母的大小写无关紧要; 16M16m等效,1G1g等效。

要限制使用SET语句在运行时可以将系统变量设置为的最大值,请在服务器启动时使用格式为--maximum-var_name=value的选项来指定此最大值。例如,为防止innodb_log_file_size的值在运行时增加到 32MB 以上,请使用选项--maximum-innodb-log-file-size=32M

许多系统变量是动态的,可以在运行时使用SET语句进行更改。有关列 table,请参见第 5.1.8.2 节“动态系统变量”。要使用SET更改系统变量,请按名称引用该名称,还可以在其前面加上修饰符。在运行时,必须使用下划线而不是破折号来写系统变量名称。以下示例简要说明了此语法:

  • 设置全局系统变量:
SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;
  • 设置会话系统变量:
SET SESSION sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';

有关SET语法的完整详细信息,请参见第 13.7.4.1 节“变量分配的 SET 语法”。有关设置系统变量的特权要求的描述,请参见第 5.1.8.1 节“系统变量特权”

在服务器启动时设置变量时,可以使用用于指定值倍数的后缀,但在运行时不能使用SET来设置值。另一方面,使用SET可以使用 table 达式分配变量的值,当在服务器启动时设置变量时不正确。例如,以下第一行在服务器启动时是合法的,但第二行不是:

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

相反,以下第二行在运行时合法,但第一行则不合法:

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

Note

可以通过将SET语句设置为ON1来启用某些系统变量,或者通过将它们设置为OFF0来禁用某些系统变量。但是,要在命令行或选项文件中设置此类变量,必须将其设置为10;将其设置为ONOFF将不起作用。例如,在命令行上--delay_key_write=1有效,而--delay_key_write=ON无效。

要显示系统变量名称和值,请使用SHOW VARIABLES语句:

mysql> SHOW VARIABLES;
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| auto_increment_increment        | 1                                 |
| auto_increment_offset           | 1                                 |
| automatic_sp_privileges         | ON                                |
| back_log                        | 50                                |
| basedir                         | /home/mysql/                      |
| binlog_cache_size               | 32768                             |
| bulk_insert_buffer_size         | 8388608                           |
| character_set_client            | utf8                              |
| character_set_connection        | utf8                              |
| character_set_database          | latin1                            |
| character_set_filesystem        | binary                            |
| character_set_results           | utf8                              |
| character_set_server            | latin1                            |
| character_set_system            | utf8                              |
| character_sets_dir              | /home/mysql/share/mysql/charsets/ |
| collation_connection            | utf8_general_ci                   |
| collation_database              | latin1_swedish_ci                 |
| collation_server                | latin1_swedish_ci                 |
...
| innodb_autoextend_increment     | 8                                 |
| innodb_buffer_pool_size         | 8388608                           |
| innodb_checksums                | ON                                |
| innodb_commit_concurrency       | 0                                 |
| innodb_concurrency_tickets      | 500                               |
| innodb_data_file_path           | ibdata1:10M:autoextend            |
| innodb_data_home_dir            |                                   |
...
| version                         | 5.7.18-log                        |
| version_comment                 | Source distribution               |
| version_compile_machine         | i686                              |
| version_compile_os              | suse-linux                        |
| wait_timeout                    | 28800                             |
+---------------------------------+-----------------------------------+

使用LIKE子句,该语句仅显示那些与模式匹配的变量。要获取特定的变量名称,请使用LIKE子句,如下所示:

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

要获取名称与模式匹配的变量列 table,请在LIKE子句中使用%通配符:

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

通配符可以在要匹配的模式中的任何位置使用。严格来说,因为_是可与任何单个字符匹配的通配符,所以您应将其转为\_以对其进行字面匹配。实际上,这几乎没有必要。

对于SHOW VARIABLES,如果您既未指定GLOBAL也未指定SESSION,则 MySQL 返回SESSION值。

在设置仅GLOBAL个变量时而不是在检索它们时要求GLOBAL关键字的原因是为了防止将来出现问题:

  • 如果要删除的SESSION变量与GLOBAL变量具有相同的名称,则具有足以修改全局变量的特权的 Client 端可能会意外地更改GLOBAL变量,而不仅仅是为其自己的会话更改SESSION变量。

  • 如果要添加一个与GLOBAL变量同名的SESSION变量,则打算更改GLOBAL变量的 Client 端可能只会发现自己的SESSION变量被更改。