MySQL中的系统变量（非常详细，附带实例）

MySQL 服务器端程序在运行过程中会用到许多影响程序行为的变量，它们被称为 MySQL 系统变量，如下面 3 个系统变量：

• max_connections：表示允许同时连接到服务器的客户端数量；
• default_storage_engine：表示默认存储引擎；
• query_cache_size：表示查询缓存的大小。

MySQL 服务器端程序支持的系统变量有好几百个，我们就不一一列举了。每个系统变量都有一个默认值，我们可以使用命令行或配置文件中的启动选项在启动服务器时改变一些系统变量的值。

MySQL查看系统变量

可以使用如下命令查看 MySQL 服务器端程序支持的系统变量及它们的当前值：

SHOW VARIABLES;
SHOW VARIABLES [LIKE '匹配的模式'];

由于系统变量实在太多了，因此我们通常会带一个 LIKE 过滤条件来查看所需系统变量的值，如下所示：

mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 15    |
+-----------------+-------+
1 row in set (0.00 sec)

LIKE 表达式后面可以跟通配符来进行模糊查询。也就是说，可以采用如下方式编写 SQL 语句，这样就可以查询出所有以 default 开头的系统变量的值：

mysql> SHOW VARIABLES LIKE 'default%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
| default_password_lifetime     | 0                     |
| default_storage_engine        | InnoDB                |
| default_tmp_storage_engine    | InnoDB                |
| default_week_format           | 0                     |
+-------------------------------+-----------------------+
5 rows in set (0.01 sec)

通过启动选项设置系统变量

大部分系统变量都可以通过启动服务器时传送启动选项的方式来进行设置。前面提到，添加启动选项主要有两种方式，分别是通过命令行添加启动选项和通过配置文件添加启动选项。

1) 通过命令行添加启动选项。例如，在启动服务器端程序时使用如下命令，表示 MySQL 启动时默认存储引擎为 MyISAM，允许同时连接到服务器的客户端数量为 10 个。

mysqld --default-storage-engine=MyISAM --max-connections=10

2) 通过配置文件添加启动选项。例如，编辑 my.cnf 配置文件，如下所示：

[server]
default-storage-engine=MyISAM
max-connections=10

使用上述两种方式中的任意一种启动服务器端程序后，我们再来查看一下系统变量的值，如下所示：

mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 10    |
+-----------------+-------+
1 row in set (0.00 sec)

可以看到，default_storage_engine 和 max_connections 这两个系统变量的值已经被修改了。

需要注意的是，对于启动选项来说，如果启动选项名由多个单词组成，那么各个单词之间使用短画线“-”或下画线“_”连接均可，但是它对应的系统变量的单词之间必须使用下画线“_”连接。在命令行或配置文件中添加的启动选项是使用短画线“-”连接的，其实也可以使用下画线“_”连接，但是我们在查询系统变量的时候只能使用下画线“_”连接。

在服务器端程序运行过程中设置系统变量

对于大部分系统变量来说，它们的值可以在服务器端程序运行过程中进行动态修改而无须停止并重启服务器。但是，系统变量有作用范围之分。

1) 设置不同作用范围的系统变量

多个客户端程序可以同时连接到一个服务器端程序。对于同一个系统变量，我们有时想让不同的客户端对应设置不同的值。

例如，若客户端 A 想让当前客户端对应的默认存储引擎为 InnoDB，则可以把系统变量 default_storage_engine 的值设置为 InnoDB；若客户端 B 想让当前客户端对应的默认存储引擎为 MyISAM，则可以把系统变量 default_storage_engine 的值设置为 MyISAM。

由此，两个客户端就拥有不同的默认存储引擎，使用时互不影响，十分方便。但是，这样一来，每个客户端都会私有一份系统变量，有可能产生如下两个问题：

• 有些系统变量并不是针对单个客户端的，如允许同时连接到服务器的客户端数量 max_connections。把这些公有的系统变量变成某个客户端私有的显然不太合适。
• 一个新连接到服务器的客户端对应的系统变量的值应该怎么设置呢？

为了解决这两个问题，MySQL 提出了系统变量作用范围的概念。具体来说，作用范围分为两种，分别是全局变量和会话变量。

• GLOBAL：全局变量，会影响服务器的整体操作。
• SESSION：会话变量，会影响某个客户端的连接操作（注：SESSION 有一个别名叫 LOCAL）。

服务器启动时，会将每个全局变量初始化为其默认值，也可以通过命令行或配置文件中指定的启动选项更改这些默认值。此外，服务器还会为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值初始化。

例如，服务器启动时会初始化一个名为 default_storage_engine、作用范围为 GLOBAL 的系统变量。每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为 default_storage_engine、作用范围为 SESSION 的系统变量，该作用范围为 SESSION 的系统变量值按照当前作用范围为 GLOBAL 的同名系统变量值进行初始化。

显然，通过启动选项设置的系统变量的作用范围都是 GLOBAL，也就是对所有客户端有效，因为在服务器启动时还没有客户端连接进来。

了解了系统变量的 GLOBAL 和 SESSION 作用范围后，我们看一下在服务器端程序运行过程中通过客户端程序设置系统变量的语法，如下所示：

SET [GLOBAL|SESSION] 系统变量名 = 值;

或者写为如下形式：

SET [@@(GLOBAL|SESSION).] = 值;

例如，在服务器端程序运行过程中把作用范围为 GLOBAL 的系统变量 default_storage_engine 的值修改为 MyISAM，也就是想让后面新连接到服务器的客户端都使用 MyISAM 作为默认存储引擎，可以选择下面两种方式中的任意一种进行设置：

#方式1
mysql> SET GLOBAL default_storage_engine = MyISAM;
#方式2
mysql> SET @@GLOBAL.default_storage_engine = MyISAM;

如果只想对当前客户端生效，则可以选择下面 3 种方式中的任意一种进行设置：

#方式一
mysql> SET SESSION default_storage_engine = MyISAM;
#方式二
mysql> SET @@SESSION.default_storage_engine = MyISAM;
#方式三
mysql> SET default_storage_engine = MyISAM;

由方式三可知，如果在设置系统变量的语句中省略了作用范围，则默认作用范围为 SESSION。

2) 查看不同作用范围的系统变量

既然系统变量有作用范围之分，那么 SHOW VARIABLES 语句查看的是什么作用范围的系统变量呢？该语句默认查看的是 SESSION 作用范围的系统变量。

我们也可以在查看系统变量的语句中加入作用范围，如下所示：

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 匹配的模式];

演示完整的设置并查看系统变量的过程，如下所示：

-- 初始状态
mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+
1 row in set (0.00 sec)

-- 仅修改 SESSION 作用范围
mysql> SET SESSION default_storage_engine = MyISAM;
Query OK, 0 rows affected (0.00 sec)

-- 再次查看
mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+
1 row in set (0.00 sec)

可以看到，最初，无论是在 GLOBAL 作用范围内，还是在 SESSION 作用范围内，系统变量 default_storage_engine 的值都是 InnoDB；在 SESSION 作用范围内把该系统变量的值设置为 MyISAM 后，GLOBAL 作用范围内该系统变量的值并没有发生改变。

某个客户端改变了某个系统变量在 GLOBAL 作用范围内的值，并不会影响该系统变量在当前已经连接的客户端作用范围为 SESSION 内的值，只会影响该系统变量在后续连接进来的客户端作用范围为 SESSION 内的值。

需要注意的是，并不是所有系统变量都具有 GLOBAL 和 SESSION 作用范围，分为如下 4 种情况：

• 有些系统变量只具有 GLOBAL 作用范围，如 max_connections；
• 有些系统变量只具有 SESSION 作用范围，如 insert_id；
• 有些系统变量既具有 GLOBAL 作用范围，也具有 SESSION 作用范围，如 default_storage_engine。大部分系统变量都是这样的；
• 有些系统变量是只读的，并不能设置值，如 version（表示当前 MySQL 版本），在客户端不能设置该系统变量的值，只能使用 SHOW VARIABLES 语句查看该系统变量的值。

启动选项和系统变量之间的关系

启动选项是在程序启动时用户传递的一些参数，而系统变量是影响服务器端程序运行行为的变量，它们之间的关系如下。

• 大部分系统变量都可以被当作启动选项传入；
• 有些系统变量是在程序运行过程中自动生成的，不能被当作启动选项进行设置，如 auto_increment_offset、character_set_client；
• 有些启动选项不是系统变量，如 defaults-file。