在上一篇《Server层统计信息字典表 | 全方位认识 information_schema》中,我们详细介绍了information_schema系统库的列、约束等统计信息字典表,本期我们将为大家带来系列第三篇《Server层表级别对象字典表 | 全方位认识information_schema》。
该表提供查询数据库中的视图相关的信息,查询该表的帐号需要拥有show view权限
下面是该表中存储的信息内容
admin@localhost : information_schema 06:22:17> select * from VIEWS where TABLE_SCHEMA='sys' limit 1\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sys
TABLE_NAME: host_summary
VIEW_DEFINITION: select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS \
...... # 视图定义文本内容过长,后续内容省略
CHECK_OPTION: NONE
IS_UPDATABLE: NO
DEFINER: mysql.sys@localhost
SECURITY_TYPE: INVOKER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.02 sec)
字段含义如下:
PS:MySQL可以使用不同的sql_mode的设置值来使server支持不同的SQL语法类型。例如:当使用"ANSI"值时,双竖杠"||" 被当作连接运算符(字符串拼接符)解析。在MySQL内部会把双竖杠"||" 转换为concat()函数对待,但在MySQL 5.7的默认sql_mode值下,双竖杠"||" 被解析为逻辑或关键字or。
该表提供查询关于某个数据库下的触发器相关的信息,要查询某个表的触发器,查询的账户必须要有trigger权限
下面是该表中存储的信息内容
# 创建触发器
root@localhost : (none) 12:02:38> use sbtest
Database changed
root@localhost : sbtest 12:18:08> CREATE TRIGGER test_trigger BEFORE UPDATE ON sbtest1 FOR EACH ROW SET @sum = @sum + NEW.id;
Query OK, 0 rows affected (0.10 sec)
# 查询TRIGGERS表
root@localhost : sbtest 12:20:15> select * from information_schema.triggers where TRIGGER_SCHEMA='sbtest'\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sbtest
TRIGGER_NAME: test_trigger
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sbtest
EVENT_OBJECT_TABLE: sbtest1
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.id
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2018-01-31 00:19:10.82
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@%
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_bin
1 row in set (0.00 sec)
字段含义如下:
PS:该表中的信息还可以使用show语句查询(但show语句可查询的信息非常有限),如下:
root@localhost : sbtest 12:23:26> show triggers from sbtest where `Table`='sbtest1'\G;
*************************** 1. row ***************************
Trigger: test_trigger
Event: UPDATE
Table: sbtest1
Statement: SET @sum = @sum + NEW.id
Timing: BEFORE
Created: 2018-01-31 00:19:10.82
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_bin
1 row in set (0.00 sec)
该表提供查询数据库内的表相关的基本信息
下面是该表中存储的信息内容
admin@localhost : information_schema 06:15:49> select * from TABLES where TABLE_NAME='sbtest1'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sbtest
TABLE_NAME: sbtest1
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 4392380
AVG_ROW_LENGTH: 233
DATA_LENGTH: 1027604480
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 97140736
DATA_FREE: 50331648
AUTO_INCREMENT: 10730975
CREATE_TIME: 2017-09-05 18:58:33
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_bin
CHECKSUM: NULL
CREATE_OPTIONS: max_rows=1000000
TABLE_COMMENT:
1 row in set (0.00 sec)
字段含义如下(该表中的字段从ENGINE开启,后续的字段都为 "MySQL extension" 列)
PS:
PS:该表中的信息还可以使用show语句查询
root@localhost : information_schema 11:33:23> show table status from sbtest where name = 'sbtest1'\G;
*************************** 1. row ***************************
Name: sbtest1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 7522488
Avg_row_length: 227
Data_length: 1713373184
Max_data_length: 0
Index_length: 158089216
Data_free: 3145728
Auto_increment: 16790458
Create_time: 2018-01-14 16:18:34
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: max_rows=1000000
Comment:
1 row in set (0.00 sec)
# 或者使用like子句
show table status from sbtest like 'sbtest1';
该表提供查询关于存储过程和存储函数的信息(不包括用户自定义函数UDF)
下面是该表中存储的信息内容
admin@localhost : information_schema 06:10:43> select * from ROUTINES limit 1\G;
*************************** 1. row ***************************
SPECIFIC_NAME: create_synonym_db
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: sys
ROUTINE_NAME: create_synonym_db
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN DECLARE v_done bool DEFAULT FALSE; DECLARE v_db_name_check VARCHAR(64); DECLARE v_db_err_msg TEXT; DECLARE v_table
...... # 由于存储程序定义文本太长,省略后续文本
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: MODIFIES SQL DATA
SQL_PATH: NULL
SECURITY_TYPE: INVOKER
CREATED: 2017-07-06 12:43:53
LAST_ALTERED: 2017-07-06 12:43:53
SQL_MODE:
ROUTINE_COMMENT:
Description
Takes a source database name and synonym name, and then creates the
synonym database with views that point to all of the tables within
the source database.
......
DEFINER: mysql.sys@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
字段含义如下(部分字段)
该表提供查询关于分区表的信息
下面是该表中存储的信息内容(该表中的每行记录对应着一个分区表或分区表的某个子分区表)
# 创建一个示例分区表
root@localhost : luoxiaobo 03:18:28> use luoxiaobo
Database changed
root@localhost : luoxiaobo 03:18:31> CREATE TABLE test_partition(
-> d1 INT,
-> d2 INT,
-> d3 VARCHAR(25)
-> )
-> PARTITION BY HASH(d1 + d2)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.03 sec)
# 查询分区表信息
root@localhost : luoxiaobo 03:19:21> select * from information_schema.PARTITIONS where TABLE_NAME='test_partition'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: luoxiaobo
TABLE_NAME: test_partition
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: d1 + d2
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2018-01-27 15:18:32
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
............
4 row in set (0.01 sec)
表字段含义(该表中所有字段都为 "MySQL extension" 列)
PS:对于非分区表,在INFORMATION_SCHEMA.PARTITIONS中只有一条记录。但是,PARTITION_NAME、SUBPARTITION_NAME、PARTITION_ORDINAL_POSITION、SUBPARTITION_ORDINAL_POSITION、PARTITION_METHOD、SUBPARTITION_METHOD、PARTITION_EXPRESSION、SUBPARTITION_EXPRESSION、PARTITION_DESCRIPTION列值均为NULL (PARTITION_COMMENT列值为空串)
该表提供查询计划任务事件相关的信息
下面是该表中存储的信息内容
# 先创建一个存放统计数据的表,然后再创建一个事件每天统计sbtest.sbtest1表中的最大自增值,并插入到统计数据表中
## 创建存放统计数据的表
root@localhost : sbtest 05:03:31> create table test_table (id int);
Query OK, 0 rows affected (0.02 sec)
## 创建统计事件
root@localhost : sbtest 05:04:06> DELIMITER $$
root@localhost : sbtest 05:05:28> CREATE EVENT test_event
-> ON SCHEDULE
-> EVERY 1 DAY
-> COMMENT '每天统计sbtest1表中的最大自增值'
-> DO
-> BEGIN
-> insert into test_table select max(id) from sbtest1;
-> END $$
Query OK, 0 rows affected (0.00 sec)
root@localhost : sbtest 05:05:37>
root@localhost : sbtest 05:05:37> DELIMITER ;
# 然后在events表中查询事件信息
root@localhost : sbtest 05:05:39> select * from information_schema.events\G;
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: sbtest
EVENT_NAME: test_event
DEFINER: root@%
TIME_ZONE: +08:00
EVENT_BODY: SQL
EVENT_DEFINITION: BEGIN
insert into test_table select max(id) from sbtest1;
END
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
STARTS: 2018-01-21 17:05:37
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2018-01-21 17:05:37
LAST_ALTERED: 2018-01-21 17:05:37
LAST_EXECUTED: NULL
EVENT_COMMENT: 每天统计sbtest1表中的最大自增值
ORIGINATOR: 3306111
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_bin
1 row in set (0.00 sec)
# 修改事件
root@localhost : sbtest 05:06:00> ALTER EVENT test_event enable;
Query OK, 0 rows affected (0.01 sec)
# 再次查询events表中的事件信息
root@localhost : sbtest 05:08:56> select * from information_schema.events\G;
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: sbtest
EVENT_NAME: test_event
DEFINER: root@%
TIME_ZONE: +08:00
EVENT_BODY: SQL
EVENT_DEFINITION: BEGIN
insert into test_table select max(id) from sbtest1;
END
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
STARTS: 2018-01-21 17:05:37
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2018-01-21 17:05:37
LAST_ALTERED: 2018-01-21 17:08:56
LAST_EXECUTED: NULL
EVENT_COMMENT: 每天统计sbtest1表中的最大自增值
ORIGINATOR: 3306111
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_bin
1 row in set (0.00 sec)
字段含义如下(该表中所有字段都为 "MySQL extension" 列)
PS:查询该表中的事件信息还可以使用show语法 ,如下:
# 语法
Syntax:
SHOW EVENTS [{FROM | IN} schema_name]
[LIKE 'pattern' | WHERE expr]
# 示例(从下面的结果中可以看到,查询到的信息明显少于直接查询events表)
root@localhost : sbtest 05:11:08> show events from sbtest where Name='test_event'\G;
*************************** 1. row ***************************
Db: sbtest
Name: test_event
Definer: root@%
Time zone: +08:00
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2018-01-21 17:05:37
Ends: NULL
Status: ENABLED
Originator: 3306111
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_bin
1 row in set (0.00 sec)
该表提供有关存储过程和函数的参数信息,以及有关存储函数的返回值的信息。 这些参数信息与mysql.proc表中的param_list列记录的内容类似
下面是该表中存储的信息内容
admin@localhost : information_schema 05:59:15> select * from PARAMETERS where SPECIFIC_NAME='create_synonym_db' limit 1\G;
*************************** 1. row ***************************
SPECIFIC_CATALOG: def
SPECIFIC_SCHEMA: sys
SPECIFIC_NAME: create_synonym_db
ORDINAL_POSITION: 1
PARAMETER_MODE: IN
PARAMETER_NAME: in_db_name
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 64
CHARACTER_OCTET_LENGTH: 192
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_general_ci
DTD_IDENTIFIER: varchar(64)
ROUTINE_TYPE: PROCEDURE
1 rows in set (0.00 sec)
字段含义如下:
PS:对于存储过程或函数存在连续参数时,ORDINAL_POSITION列值为1,2,3,...这样记录。但对于存储函数中还有一行RETURNS子句的数据类型。它返回值不是一个真实的参数(可能是一堆字符串数据),所以ORDINAL_POSITION列值记录为0,且PARAMETER_NAME和PARAMETER_MODE列值记录为NULL,因为此时函数返回的值没有参数名称,所以不适用。
本期内容就介绍到这里,本期内容参考链接如下:
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。
全文完。