Server层表级别对象字典表 | 全方位认识 information_schema

浅浅的花香味﹌ 2022-12-23 00:39 30阅读 0赞

在上一篇[《Server层统计信息字典表 | 全方位认识 information\_schema》][Server_ _ _ information_schema]中，我们详细介绍了information\_schema系统库的列、约束等统计信息字典表，本期我们将为大家带来系列第三篇《Server层表级别对象字典表 | 全方位认识information\_schema》。

### **1、VIEWS** ###

该表提供查询数据库中的视图相关的信息，查询该表的帐号需要拥有show view权限

*  该表为InnoDB引擎临时表

下面是该表中存储的信息内容

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)

字段含义如下：

*  TABLE\_CATALOG：该字段总是为def
 *  TABLE\_SCHEMA：表示视图所在的数据库名称
 *  TABLE\_NAME：表示视图名称
 *  VIEW\_DEFINITION：表示视图的定义语句SQL文本，注意，该文本经过了内部转换，并不是原始的定义语句，该列显示的内容与使用SHOW CREATE VIEW语句查看到的视图定义语句信息大部分相同（小部分不同的是，information\_schema.views表中会自动去掉例如"WITH CHECK OPTION"的关键字，而show create view语句查询出来的信息中不会删除"WITH CHECK OPTION"关键字）
 *  CHECK\_OPTION：创建视图时的CHECK OPTION选项值，有效值为：NONE、CASCADED、LOCAL
 *  IS\_UPDATABLE：在创建视图时，MySQL会为视图设置一个是否可更新的标记，如果视图可执行DML语句（以及类似的操作），则该字段值为YES，否则为NO。   
    \* 如果视图不可被更新，则执行DML语句时会报错   
    \* 如果视图依赖于一个或多个其他视图，则IS\_UPDATABLE标记在此场景下不可靠，在这种情况下，被依赖的基础视图中的某一个被更新，IS\_UPDATABLE列的值并不会更新，但可以通过删除和重新创建视图来更新该值
 *  DEFINER：创建视图的账户名称
 *  SECURITY\_TYPE：表示视图以谁的身份来执行，有效值为：DEFINER(表示以定义者的身份来执行)、INVOKER(表示调用者的身份来执行)
 *  CHARACTER\_SET\_CLIENT：表示创建视图时character\_set\_client系统变量的会话值
 *  COLLATION\_CONNECTION：创建视图时collation\_connection系统变量的会话值

**PS：**MySQL可以使用不同的sql\_mode的设置值来使server支持不同的SQL语法类型。例如：当使用"ANSI"值时，双竖杠"||" 被当作连接运算符(字符串拼接符)解析。在MySQL内部会把双竖杠"||" 转换为concat()函数对待，但在MySQL 5.7的默认sql\_mode值下，双竖杠"||" 被解析为逻辑或关键字or。

### **2、TRIGGERS** ###

该表提供查询关于某个数据库下的触发器相关的信息，要查询某个表的触发器，查询的账户必须要有trigger权限

*  该表为InnoDB引擎临时表

下面是该表中存储的信息内容

# 创建触发器
    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)

字段含义如下：

*  TRIGGER\_CATALOG：该字段总是为def
 *  TRIGGER\_SCHEMA和TRIGGER\_NAME：表示触发器所属的数据库名称和触发器名称
 *  EVENT\_MANIPULATION：表示触发器触发事件在关联表上的操作类型，有效值为：“INSERT”（表示插入了一行数据）、“DELETE”（表示一行被删除）、“UPDATE”（表示一行被修改）
 *  EVENT\_OBJECT\_CATALOG：该字段总是为def
 *  EVENT\_OBJECT\_SCHEMA和EVENT\_OBJECT\_TABLE：每个触发器只与一个表相关联。这两个字段表示触发器关联的表所在的数据库名称和触发器关联的表名
 *  ACTION\_ORDER：表示具有相同的EVENT\_MANIPULATION和ACTION\_TIMING列值触发器在同一个表上的触发顺序。在MySQL 5.7.2之前，ACTION\_ORDER列值总是为0，因为在这之前的版本中，具有相同的EVENT\_MANIPULATION和ACTION\_TIMING值的触发器一个表只能有一个。
 *  ACTION\_CONDITION：该字段总是为NULL
 *  ACTION\_STATEMENT：表示触发器SQL语句主体，也就是触发器激活时需要执行的语句。本文使用UTF-8编码。
 *  ACTION\_ORIENTATION：始终为"ROW"
 *  ACTION\_TIMING：表示触发器是之前还是之后激活。有效值为：'BEFORE' 或 'AFTER'
 *  ACTION\_REFERENCE\_OLD\_TABLE和ACTION\_REFERENCE\_NEW\_TABLE：这两个字段总是为NULL
 *  ACTION\_REFERENCE\_OLD\_ROW和ACTION\_REFERENCE\_NEW\_ROW：分别表示旧的和新的列标识符。ACTION\_REFERENCE\_OLD\_ROW列值总是为“OLD”，ACTION\_REFERENCE\_NEW\_ROW列值总是为“NEW”
 *  CREATED：表示创建触发器的时间。在MySQL 5.7.2或更高版本中创建的触发器时，该字段是一个TIMESTAMP（2）类型值（小数部分保留2位数，即百分之一秒），在5.7.2之前创建的触发器该字段为NULL
 *  SQL\_MODE：表示创建触发器时会话的sql\_mode值（该字段为 "MySQL extension" 列）
 *  DEFINER：创建触发器的账户名称（该字段为 "MySQL extension" 列）
 *  CHARACTER\_SET\_CLIENT：表示创建触发器时character\_set\_client系统变量的会话值（该字段为 "MySQL extension" 列）
 *  COLLATION\_CONNECTION：表示创建触发器时collation\_connection系统变量的会话值（该字段为 "MySQL extension" 列）
 *  DATABASE\_COLLATION：表示创建触发器时与触发器关联的数据库的排序规则（该字段为 "MySQL extension" 列）

**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)

### **3、TABLES** ###

该表提供查询数据库内的表相关的基本信息

*  该表为Memory引擎临时表

下面是该表中存储的信息内容

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" 列）

*  TABLE\_CATALOG：该字段总是为def
 *  TABLE\_SCHEMA：表示表所在的数据库名称
 *  TABLE\_NAME：表示表名称
 *  TABLE\_TYPE：表的类型，有效值为：SYSTEM VIEW（information\_schema下的表都被归类为系统表）、BASE TABLE（用户自定义的持久表、mysql和performance\_schema库下的所有表都被归类为基表）、VIEW（用户自定义视图和sys schema下的表），注意，在该表中不会记录临时表的信息
 *  ENGINE：表示表引擎类型，注意：分区表的引擎会记录为建表语句中指定的引擎，但在之前的版本中，分区表的引擎被记录为"PARTTION"
 *  VERSION：表示表定义.frm文件的版本号，代表表结构定义被修改过多少次
 *  ROW\_FORMAT：行存储格式，可能出现的值为：Fixed、Dynamic、Compressed、Redundant、Compact，对于myisam表，只支持Fixed、Dynamic两种（但在dynamic行格式时，myisamchk -dvv命令报告行格式为Packed），memory只支持Fixed一种，innodb引擎支持Dynamic、Compressed、Redundant、Compact四种（不支持Fixed）
 *  TABLE\_ROWS：表数据总行数，对于一些存储引擎（如MyISAM）该行数值是准确的计数（因为myisam表有行数据计数器，该值直接取计数器的值）。对于其他存储引擎，如InnoDB，此值是一个近似值，并且可能与实际值相差多达40％至50％。在这种情况下，请使用SELECT COUNT（\*）来获取准确的计数 。对于performance\_schema，则该字段为NULL
 *  AVG\_ROW\_LENGTH：表中数据的行的平均数据行长度，可以使用Avg\_row\_length\*Rows/表空间大小=表数据实际占用表空间大小，使用1减去这个值就等于表空间碎片率。如果太大时，可以使用optimize table tb\_name语句来优化表，或者直接使用alter table tb\_name engine=innodb语句来优化表。
 *  DATA\_LENGTH：数据总长度。对于MyISAM表，该字段代表数据文件的长度，以字节为单位。对于InnoDB表，该字段是指的聚集索引分配的内存数量近似值，以字节为单位。具体来说，它指的是聚集索引大小（以页为单位）乘以InnoDB page size的结果值
 *  MAX\_DATA\_LENGTH：对于MyISAM表，Max\_data\_length是指的数据文件的最大长度。可以存储在表中的数据的字节总数。对于innodb表，并未使用该字段
 *  INDEX\_LENGTH：对于MyISAM表，该字段是指的索引文件的长度，以字节为单位。对于InnoDB表，该字段是指的非聚集索引分配的大概内存数量，以字节为单位。具体来说，它指的是非聚集索引大小（以页为单位）乘以InnoDB page size的结果值。
 *  DATA\_FREE：已分配但未使用的表空间字节数。对于 InnoDB表，报告表所属的表空间的可用空间。如果表使用共享表空间，这指的是共享表空间的可用空间大小。如果使用独立表空间，则可用空间指的该表的可用空间。可用空间表示完全可用区(extends)中的字节数减去安全余量。即使可用空间显示为0，也可以插入行，只要不需要分配新的区（extends）就允许插入操作。对于分区表，此值仅为估计值，可能不是绝对正确。在这种情况下获取此选项信息值的更准确的方法是查询INFORMATION\_SCHEMA.PARTITIONS表，如：SELECT SUM（DATA\_FREE） FROM ATION\_SCHEMA.PARTITIONS WHERE TABLE\_SCHEMA ='mydb' AND TABLE\_NAME ='mytable';
 *  AUTO\_INCREMENT：下一个AUTO\_INCREMENT值
 *  CREATE\_TIME：创建表时间
 *  UPDATE\_TIME：上次更新数据文件时间。对于某些存储引擎，此值为NULL。例如，InnoDB在其共享表空间中存储多个表，数据更新不会更新数据文件时间戳。即使每个表使用独立表空间，每个InnoDB表在一个单独的.ibd文件中，change buffer可能会导致DML数据延迟写入数据文件，因此文件修改时间与上次插入，更新或删除的时间可能不同。对于MyISAM，数据更新会更新数据文件时间;但是，在Windows上，数据更新不会导致数据文件时间的更新，因此值不准确。注意：分区表的每个分区的更新该字段也会进行更新
 *  CHECK\_TIME：上次检查表时。并非所有存储引擎都会更新此时间，如果不检测表，则值始终为NULL，注意：分区表的记录中该字段总是为NULL
 *  TABLE\_COLLATION：表的字符集和排序规则
 *  CHECKSUM：实时校验和值（如果表有使用值校验和功能）
 *  CREATE\_OPTIONS：与CREATE TABLE一起使用的额外建表选项，如果记录的表为分区表，则该字段值为"partitioned"
 *  TABLE\_COMMENT：创建表时使用的注释（或有关MySQL无法访问表信息的信息）

**PS：**

*  在5.7.9版本中，引入了一个变量innodb\_default\_row\_format来控制Innodb表的默认行格式，全局变量，动态变量，默认值为dynamic，枚举类型，有效值为：dynamic、compack、reduncant，compressed格式因为共享表空间不支持该行格式，所以该变量也不支持设置默认值为compressed。在innodb表创建时没有显式定义row\_format或者定义的row\_format=default时，会使用到该变量的默认值。注意：innodb内部临时表直接使用dynamic行格式创建，而不管该变量的值设置为多少。
 *  对于MEMORY表，DATA\_LENGTH，MAX\_DATA\_LENGTH和INDEX\_LENGTH值近似于所分配的内存存储的实际容量。分配算法可以减少分配操作的次数
 *  对于NDB表，此语句的输出显示AVG\_ROW\_LENGTH和DATA\_LENGTH列的确切值，但不包括BLOB列
 *  对于视图，SHOW TABLE STATUS显示的所有字段都为NULL，除了Name表示视图名称和Comment表示视图注释外

**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';

### **4、ROUTINES** ###

该表提供查询关于存储过程和存储函数的信息（不包括用户自定义函数UDF）

*  该表中的信息与“mysql.proc”中记录的信息相对应（如果该表中有值的话）
 *  该表为InnoDB引擎临时表

下面是该表中存储的信息内容

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)

字段含义如下（部分字段）

*  SPECIFIC\_NAME：存储程序名称
 *  ROUTINE\_CATALOG：该字段总是为def
 *  ROUTINE\_SCHEMA：存储程序所在的数据库名称
 *  ROUTINE\_NAME：存储程序名称
 *  ROUTINE\_TYPE：存储程序类型，有效值为：PROCEDURE和FUNCTION
 *  DTD\_IDENTIFIER：如果存储程序为函数，则该字段为返回数据类型的值，如果为存储过程，则该字段为空
 *  ROUTINE\_BODY：存储程序的主体内容，总是为"SQL"
 *  ROUTINE\_DEFINITION：存储程序的具体的定义SQL文本
 *  EXTERNAL\_LANGUAGE：表示是否使用外部语言。如果mysql.proc.language ='SQL'，则EXTERNAL\_LANGUAGE字段值为NULL，否则，EXTERNAL\_LANGUAGE字段值和mysql.proc.language中的值相同。但是，当前MySQL Server还不支持除了SQL意外的其他预研，所以EXTERNAL\_LANGUAGE字段值始终为NULL
 *  EXTERNAL\_NAME：该字段总是为NULL
 *  EXTERNAL\_LANGUAGE：存储程序的外部语言，由于目前MySQL 只支持SQL，所以该字段总是为NULL
 *  PARAMETER\_STYLE：该字段总是为SQL
 *  IS\_DETERMINISTIC：有效值为YES和NO，具体值依赖于定义存储程序时是否使用了DETERMINISTIC关键字来指定相关的特性
 *  SQL\_DATA\_ACCESS：表示存储程序数据访问特性，有效值为：CONTAINS SQL、NO SQL、READS SQL DATA、MODIFIES SQL DATA
 *  SQL\_PATH：该字段总是为NULL
 *  SECURITY\_TYPE：表示存储程序的安全特性，有效值为：DEFINER、INVOKER
 *  CREATED：表示创建存储程序的日期和时间。是一个TIMESTAMP值
 *  LAST\_ALTERED：表示存储过程最近一次修改的日期和时间。也是一个TIMESTAMP值。如果自存储程序创建以来从未修改过，则该列值与CREATED列值相同
 *  SQL\_MODE：表示创建或修改存储程序时MySQL Server的sql\_mode值（该字段为 "MySQL extension" 列）
 *  ROUTINE\_COMMENT：存储程序的注释信息（该字段为 "MySQL extension" 列）
 *  DEFINER：存储程序的创建者账户名（该字段为 "MySQL extension" 列）
 *  CHARACTER\_SET\_CLIENT：表示创建存储程序时的character\_set\_client系统变量会话值（该字段为 "MySQL extension" 列）
 *  COLLATION\_CONNECTION：表示创建纯粹程序时的collation\_connection系统变量会话值（该字段为 "MySQL extension" 列）
 *  DATABASE\_COLLATION：表示创建存储程序时所在的数据库的排序规则（该字段为 "MySQL extension" 列）
 *  DATA\_TYPE，CHARACTER\_MAXIMUM\_LENGTH，CHARACTER\_OCTET\_LENGTH，NUMERIC\_PRECISION，NUMERIC\_SCALE，DATETIME\_PRECISION，CHARACTER\_SET\_NAME和COLLATION\_NAME：这些字段含义和columns表的字段含义相同。这些字段提供有关存储函数（注意不是存储过程）的RETURNS子句的数据类型相关的信息。如果存储程序是存储过程，则这些列的值为NULL   
    \* 有关存储的函数RETURNS数据类型的信息也可在PARAMETERS表中查询到一些信息。当存储程序为存储函数时，PARAMETERS.ORDINAL\_POSITION列值为0

### **5、PARTITIONS** ###

该表提供查询关于分区表的信息

*  该表为InnoDB引擎临时表

下面是该表中存储的信息内容（该表中的每行记录对应着一个分区表或分区表的某个子分区表）

# 创建一个示例分区表
    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" 列）

*  TABLE\_CATALOG：这个列总是def
 *  TABLE\_SCHEMA：表示该分区表所属的数据库的名称
 *  TABLE\_NAME：表示该分区表的表名称
 *  PARTITION\_NAME：表示分区表的分区名称
 *  SUBPARTITION\_NAME：表示分区表的某个分区的子分区名称，如果分区表不存在子分区，则列值为NULL
 *  PARTITION\_ORDINAL\_POSITION：表示分区表的所有分区的索引编号，该编号的大小与定义的顺序相同，如：1表示第一个分区的编号。索引编号会随着分区的添加、删除和重组而改变。
 *  SUBPARTITION\_ORDINAL\_POSITION：与PARTITION\_ORDINAL\_POSITION字段含义类似，该字段表示子分区的索引编号和定义顺序
 *  PARTITION\_METHOD：表示分区表的分区函数类型，有效值为：RANGE、LIST、HASH、LINEAR HASH、KEY、LINEAR KEY
 *  SUBPARTITION\_METHOD：表示分区表的子分区的函数类型，有效值为：HASH、LINEAR HASH、KEY、LINEAR KEY
 *  PARTITION\_EXPRESSION：表示分区函数中的分区表达式，在创建分区表或修改分区表的分区函数时指定，例如：指定了分区表达式为 "PARTITION BY HASH(c1 + c2)"，则在该字段中记录表达式 "c1 + c2"
 *  SUBPARTITION\_EXPRESSION：与PARTITION\_EXPRESSION字段含义类型，表示分区表的子分区的分区函数表达式，如果分区表中没有子分区，则该字段为NULL
 *  PARTITION\_DESCRIPTION：表示RANGE和LIST分区定义的分区定义值。对于RANGE分区，它表示每个分区的VALUES LESS THAN子句中设置的值，该值可以是整数或MAXVALUE。对于LIST分区，它表示每个分区的VALUES IN子句中定义的值，该值为逗号分隔的整数值列表。另外，对于非range和list分区的其他分区类型，该字段为NULL
 *  TABLE\_ROWS：分区中的记录行数，对于InnoDB分区表，TABLE\_ROWS列值只是SQL优化中使用的估计值，并不是精确值
 *  AVG\_ROW\_LENGTH：存储在分区或子分区中的行的平均长度（以字节为单位），与DATA\_LENGTH列值/TABLE\_ROWS列值的结果值相同
 *  DATA\_LENGTH：存储在分区或子分区中的所有行记录的总长度（以字节为单位），即存储在分区或子分区中的总数据字节数
 *  MAX\_DATA\_LENGTH：可以存储在分区或子分区中的最大数据字节数
 *  INDEX\_LENGTH：分区或子分区的索引文件的长度（以字节为单位）
 *  DATA\_FREE：分配给分区或子分区但未使用的空间大小字节数
 *  CREATE\_TIME：创建分区或子分区的时间
 *  UPDATE\_TIME：分区或子分区上次修改的时间
 *  CHECK\_TIME：最后一次检查分区或子分区的事件，对于InnoDB分区表，该列值总是为NULL
 *  CHECKSUM：如果有进行checksum校验，则该列值表示校验和值，否则，该列值为NULL
 *  PARTITION\_COMMENT：表示分区的注释文本，注意：在MySQL 5.7中，分区注释的最大长度定义为1024个字符，PARTITION\_COMMENT列的显示宽度也是1024个字符。默认值为空串
 *  NODEGROUP：表示分区所属的节点组。与NDB Cluster表有关，如果非NDB Cluster表则该列值为0
 *  TABLESPACE\_NAME：分区表的表空间名称。该列值始终为DEFAULT

**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列值为空串）

### **6、EVENTS** ###

该表提供查询计划任务事件相关的信息

*  该表是InnoDB引擎临时表

下面是该表中存储的信息内容

# 先创建一个存放统计数据的表，然后再创建一个事件每天统计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" 列）

*  EVENT\_CATALOG：此列的值始终为def
 *  EVENT\_SCHEMA：此事件所属的数据库名称
 *  EVENT\_NAME：事件的名称
 *  DEFINER：创建事件的账户名称
 *  TIME\_ZONE：事件的时区，是用于调度事件的时区，且在事件执行时生效。默认值为SYSTEM，代表使用system\_time\_zone系统变量设置的时区
 *  EVENT\_BODY：用于事件的DO子句中的语句的语言类型，在MySQL 5.7中，总是"SQL"。注意：不要将此列值与早期MySQL版本中存在的同名列（该列先更名为EVENT\_DEFINITION列）列混淆
 *  EVENT\_DEFINITION：构成事件的DO子句的SQL语句的文本。即被事件执行的SQL语句
 *  EVENT\_TYPE：事件重复类型，一次（transient）或重复（RECURRING）
 *  EXECUTE\_AT：对于一次性事件，该字段表示创建事件的CREATE EVENT语句中、或修改事件的最后一个ALTER EVENT语句的AT子句中指定的DATETIME值（例如，如果事件是使用"ON SCHEDULE AT CURRENT\_TIMESTAMP +'1：6'DAY\_HOUR"子句创建，且事件在2018-01-21 14:05:30创建的，则此列中显示的值为'2018-01-22 20:05:30'，表示这个一次性事件将在创建时间2018-01-21 14:05:30的基础上再过一天+6小时之后执行）。如果事件的计时由EVERY子句而不是AT子句确定（则表示该事件是一个重复事件），则此列的值为NULL。
 *  INTERVAL\_VALUE：对于重复事件，此列包含事件的EVERY子句中的数字部分。但对于一次性事件，此列为NULL。
 *  INTERVAL\_FIELD：对于重复事件，此列包含EVERY子句的单位部分，用于管理事件的时间。此列有效值可能包含“YEAR”，“QUARTER”，“DAY”等值。但对于一次性事件，此列为NULL。
 *  SQL\_MODE：创建或更改事件时MySQL Server的SQL模式
 *  STARTS：对于其定义中包含STARTS子句的重复事件，此列包含相应的DATETIME值。与EXECUTE\_AT列类似，此值可解析定义语句中所使用的任何表达式并计算出结果值存放在该列中。如果没有STARTS子句，则此列为NULL
 *  ENDS：对于其定义中包含ENDS子句的重复事件，此列包含相应的DATETIME值。与EXECUTE\_AT列类似，此值可解析定义语句中所使用的任何表达式并计算出结果值存放在该列中。如果没有ENDS子句，则此列为NULL
 *  STATUS：该列包含三个有效值，ENABLED、DISABLED、SLAVESIDE\_DISABLED   
    \* SLAVESIDE\_DISABLED：表示事件是通过主备复制中的binlog重放方式在从库上创建的，事件运行状态在从库上被关闭
 *  ON\_COMPLETION：该列包含两个有效值，PRESVEVE、NOT PRESERVE
 *  CREATED：创建事件的日期和时间。是一个TIMESTAMP值
 *  LAST\_ALTERED：上次修改事件的日期和时间。是一个TIMESTAMP值。如果该事件自创建以来从未修改，则此列与CREATED列值相同
 *  LAST\_EXECUTED：事件上次执行的日期和时间。是一个 DATETIME值。如果事件从未执行，则此列值为NULL。LAST\_EXECUTED表示事件是从什么时候开始的。因此，ENDS列的时间值总是大于LAST\_EXECUTED
 *  EVENT\_COMMENT：事件的注释文本信息，如果事件没有注释信息，则该字段为空串
 *  ORIGINATOR：创建事件的MySQL Server的server id，用于复制。默认值为0
 *  CHARACTER\_SET\_CLIENT：创建事件时的character\_set\_client系统变量的会话值
 *  COLLATION\_CONNECTION：创建事件时的collation\_connection系统变量的会话值
 *  DATABASE\_COLLATION：与事件关联的数据库的排序规则

**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)

### **7、PARAMETERS** ###

该表提供有关存储过程和函数的参数信息，以及有关存储函数的返回值的信息。 这些参数信息与mysql.proc表中的param\_list列记录的内容类似

*  该表为InnoDB引擎临时表

下面是该表中存储的信息内容

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)

字段含义如下：

*  SPECIFIC\_CATALOG：该字段总是为def
 *  SPECIFIC\_SCHEMA：存储过程或函数所在的数据库名称
 *  SPECIFIC\_NAME：存储过程或函数的名称
 *  ORDINAL\_POSITION：1，2，3，...等大于0的数字用于存储过程和函数的参数个数，0用于函数中RETURNS子句
 *  PARAMETER\_MODE：有效值为IN，OUT，INOUT（存在RETURNS子句返回数据时该列值为NULL）
 *  PARAMETER\_NAME：存储过程或函数的参数名称（存在RETURNS子句返回数据时该列值为NULL）
 *  DATA\_TYPE：与COLUMNS表相同
 *  CHARACTER\_MAXIMUM\_LENGTH：与COLUMNS表相同
 *  CHARACTER\_OCTET\_LENGTH：与COLUMNS表相同
 *  NUMERIC\_PRECISION：与COLUMNS表相同
 *  NUMERIC\_SCALE：与COLUMNS表相同
 *  DATETIME\_PRECISION：与COLUMNS表相同
 *  CHARACTER\_SET\_NAME：与COLUMNS表相同
 *  COLLATION\_NAME：与COLUMNS表相同
 *  DTD\_IDENTIFIER：字段定义的数据类型（包含长度和其他字段属性）
 *  ROUTINE\_TYPE：程序类型，是存储过程还是函数等

**PS：**对于存储过程或函数存在连续参数时，ORDINAL\_POSITION列值为1,2,3,...这样记录。但对于存储函数中还有一行RETURNS子句的数据类型。它返回值不是一个真实的参数(可能是一堆字符串数据)，所以ORDINAL\_POSITION列值记录为0，且PARAMETER\_NAME和PARAMETER\_MODE列值记录为NULL，因为此时函数返回的值没有参数名称，所以不适用。

本期内容就介绍到这里，本期内容参考链接如下：

*  https://dev.mysql.com/doc/refman/5.7/en/views-table.html
 *  https://dev.mysql.com/doc/refman/5.7/en/triggers-table.html
 *  https://dev.mysql.com/doc/refman/5.7/en/tables-table.html
 *  https://dev.mysql.com/doc/refman/5.7/en/routines-table.html
 *  https://dev.mysql.com/doc/refman/5.7/en/partitions-table.html
 *  https://dev.mysql.com/doc/refman/5.7/en/events-table.html
 *  https://dev.mysql.com/doc/refman/5.7/en/parameters-table.html

**| 作者简介**

### **罗小波·数据库技术专家** ###

《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。

全文完。

Enjoy MySQL :)

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png][]

[Server_ _ _ information_schema]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA%3D%3D&chksm=fb242d7ecc53a4685f976f4eb88f61a72c62cba64b6bab037a8f72f4fffd2f5daa8d35135bef&idx=1&mid=2247484531&scene=21&sn=7703142d041263efc58fb15a6796c290#wechat_redirect
[format_png]: /images/20221120/1f4afe758e024e4d93edd317267267ae.png