GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES - xiaoboluo768/mysql-system-schema GitHub Wiki
- GLOBAL_STATUS表提供查询具备全局级别的状态变量值，该表中的值对应着show global status;语句的输出信息
- SESSION_STATUS表提供查询会话级别的状态变量值（如果某个状态变量不具备会话级别，则显示全局级别变量值），该表中的值对应着show session status;或show status;语句的输出信息
- GLOBAL_VARIABLES表提供具备全局级别的系统变量值，该表中对应着show global variables;语句的输出信息
- SESSION_VARIABLES表提供会话级别的系统变量值（如果某个系统变量不具备会话级别，则显示全局级别变量值），该表中的值对应着show session variables;或show variables;语句的输出信息
- PS：
- 从MySQL 5.7.6版本起，information_schema.global_status表中是否记录数据依赖于系统变量show_compatibility_56如何设置，如果设置为OFF，则记录内容迁移到performance_schema.global_status表中，information_schema.global_status表不再记录
- 这些表为Memory引擎临时表
- 表定义语句
CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
`VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
# GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES表定义与GLOBAL_STATUS表完全相同- 表字段含义
- VARIABLE_NAME：全局状态变量名称
- VARIABLE_VALUE：全局状态变量统计值
- 表记录内容示例
# GLOBAL_STATUS表
root@localhost Wed Jan 24 00:12:23 2018 00:12:23 [(none)]>select * from information_schema.global_status limit 2;
+------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+------------------+----------------+
| ABORTED_CLIENTS | 1 |
| ABORTED_CONNECTS | 0 |
+------------------+----------------+
2 rows in set (0.00 sec)
# SESSION_STATUS表
root@localhost Wed Jan 24 00:25:59 2018 00:25:59 [(none)]>select * from information_schema.session_status limit 2;
+------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+------------------+----------------+
| ABORTED_CLIENTS | 2 |
| ABORTED_CONNECTS | 0 |
+------------------+----------------+
2 rows in set (0.00 sec)
# GLOBAL_VARIABLES表
root@localhost Wed Jan 24 00:25:56 2018 00:25:56 [(none)]>select * from information_schema.global_variables limit 2;
+-------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+----------------+
| MAX_PREPARED_STMT_COUNT | 16382 |
| OPTIMIZER_PRUNE_LEVEL | 1 |
+-------------------------+----------------+
2 rows in set (0.00 sec)
# SESSION_VARIABLES表
root@localhost Wed Jan 24 00:26:22 2018 00:26:22 [(none)]>select * from information_schema.session_variables limit 2;
+-------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+----------------+
| MAX_PREPARED_STMT_COUNT | 16382 |
| OPTIMIZER_PRUNE_LEVEL | 1 |
+-------------------------+----------------+
2 rows in set (0.00 sec)- 对于系统变量，查询的方式还可以使用 "select语句+@@variable_name" 的方式，如下
# 查询全局级别系统变量
select @@global.optimizer_switch;
# 查询会话级别系统变量
select @@session.optimizer_switch;
select @@optimizer_switch;上一篇：FILES表 |下一篇：KEY_COLUMN_USAGE表