MySQL系统变量查看与设置（详解）

原创

从一到无穷 2023-05-04 14:17:27 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者从一到无穷的原创作品，请联系作者获取转载授权，否则将追究法律责任

系统变量

系统变量简介

MySQL 服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为系统变量，比如，允许同时连入的客户端数量用系统变量 max_connections 表示，表的默认存储引擎用系统变量 default_storage_engine 表示；查询缓存的大小用系统变量 query_cache_size 表示。 MySQL 服务器程序的系统变量有好几百条，这里不再一一列举。每个系统变量都有一个默认值，我们可以使用命令行或者配置文件中的选项在启动服务器时改变一些系统变量的值。大多数系统变量的值也可以在程序运行过程中修改，而无需停止并重新启动服务器。

查看系统变量

我们可以使用下列命令查看 MySQL 服务器程序支持的系统变量以及它们的当前值： SHOW VARIABLES [LIKE 匹配的模式]; 由于系统变量实在太多了，如果我们直接使用 SHOW VARIABLES 查看的话就直接刷屏了，所以通常都会带一个LIKE 过滤条件来查看我们需要的系统变量的值，比如这么写：

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

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

可以看到，现在服务器程序使用的默认存储引擎就是 InnoDB，允许同时连接的客户端数量最多为 800 。

更严谨地说，MySOL服务器实际上允许 max_connections+1个客户端连接，额外的1个是给超级用户准备的(很显然这是超级用户的一个特权)。

别忘了LIKE 表达式中可以使用通配符来进行模糊查询，也就是说我们可以这么写：

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.00 sec)

这样就查出了所有以 default 开头的系统变量的值。

设置系统变量

通过启动选项设置

大部分的系统变量都可以通过启动服务器时传送启动选项的方式来进行设置。

通过命令行添加启动选项。
比方说在启动服务器程序时用这个命令： mysqld --default-storage-engine=MyISAM --max-connections=100
通过配置文件添加启动选项。
可以这样填写配置文件： [server] default-storage-engine=MyISAM max-connections=100

当使用上边的任何一种方式启动服务器程序后，再来查看一下系统变量的值：

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 | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>

可以看到default_storage_engine和max_connections这两个系统变量的值已经被修改了。需要注意的一点是，对于启动选项来说，如果启动选项名由多个单词组成，各个单词之间用短划线（-）或者下划线（_）连接起来都可以；但是对于对应的系统变量来说，各个单词之间必须使用下划线连接起来。

服务器程序运行过程中设置

对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器。不过系统变量有作用范围之分，下边详细唠叨下。

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

我们前边说过，多个客户端程序可以同时连接到一个服务器程序。对于同一个系统变量，我们有时想让不同的客户端有不同的值。比方说狗哥使用客户端A，他想让当前客户端对应的默认存储引擎为 InnoDB ，所以他可以把系统变量 default_storage_engine 的值设置为 InnoDB ；猫爷使用客户端B，他想让当前客户端对应的默认存储引擎为 MyISAM ，所以他可以把系统变量 default_storage_engine 的值设置为 MyISAM 。这样可以使狗哥和猫爷的客户端拥有不同的默认存储引擎，使用时互不影响，十分方便。但是这样各个客户端都私有一份系统变量会产生这么两个问题：

有一些系统变量并不是针对单个客户端的，比如允许同时连接到服务器的客户端数量 max_connections ，查询缓存的大小 query_cache_size ，这些公有的系统变量让某个客户端私有显然不合适。
一个新连接到服务器的客户端对应的系统变量的值该怎么设置？

为了解决这两个问题，设计 MySQL 的大叔提出了系统变量的作用范围的概念，具体来说作用范围分为这两种：

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

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

这话有点儿绕，还是以 default_storage_engine 举例，在服务器启动时会初始化一个名为 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：SET GLOBAL default_storage_engine = MyISAM;

语句2：SET @@GLOBAL.default_storage_engine = MyISAM;

如果只想对本客户端生效，也可以选择下边3条语句中的任意一条来进行设置：

语句1：SET SESSION default_storage_engine = MyISAM;

语句2：SET @@SESSION.default_storage_engine = MyISAM;

语句3：SET default_storage_engine = MyISAM;

从上边的语句3 也可以看出，如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是 SESSION 。也就是说 SET 系统变量名 = 值和 SET SESSION 系统变量名 = 值是等价的。

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

我们可以在查看系统变量的语句中加上要查看哪个作用范围的系统变量的修饰符，就像下面这样:

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

如果使用 GLOBAL 修饰符，则显示全局系统变量的值。如果某个系统变量没有 GLOBAL作用范围，则不显示它。
如果使用 SESSION修饰符，则显示针对当前连接有效的系统变量值。如果某个系统变量没有SESSION作用范围，则显示 GLOBAL作用范围的值。
如果没写修饰符，则与使用 SESSION 修饰符效果一样。

下面演示一下完整地设置并查看系统变量的过程:

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)

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)

mysql>

可以看到，最初 default storage engine 的系统变量无论是在 GLOBAL作用范围还是在 SESSION作用范围，值都是InnoDB。我们把SESSION作用范围的系统变量值设置为 MyISAM之后，可以看到 GLOBAL作用范围的值并没有改变。

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

（3）注意事项

并不是所有的系统变量都具有 GLOBAL和SESSION的作用范围。

有一些系统变量只具有有一些系统变量只具有 GLOBAL作用范围，比如max connections，它表示服务器程序支持同时最多有多少个客户端程序进行连接。
有一些系统变量只具有SESSION作用范围，比如imsert_id，它表示在对某个包含AUTO_ICREMENT 列的表进行插入时，该列初始的值。
有一些系统变量的值既具有 GLOBAL 作用范围，也具有 SESSION作用范围，比如前面用到的 default_storage_engine，而且其实大部分的系统变量都是这样的。

有些系统变量是只读的，并不能设置值。

比如 version，它表示当前 MySOL 的版本。客户端不能设置它的值，只能在SHOW VARIABLES语句中查看。

启动选项和系统变量的区别

启动选项是在程序启动时由用户传递的一些参数，而系统变量是影响服务器程序运行行为的变量。它们之间的关系如下。大部分的系统变量都可以当作启动选项传入。有些系统变量是在程序运行过程中自动生成的，不可以当作启动选项来设置，比如 character_set_client. 有些启动选项也不是系统变量，比如 defaults-file。