Chapter 25 INFORMATION_SCHEMA Tables


内容表

25.1 Introduction
25.2 The INFORMATION_SCHEMA CHARACTER_SETS Table
25.3 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table
25.4 The INFORMATION_SCHEMA COLLATIONS Table
25.5 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
25.6 The INFORMATION_SCHEMA COLUMNS Table
25.7 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
25.8 The INFORMATION_SCHEMA COLUMN_STATISTICS Table
25.9 The INFORMATION_SCHEMA ENGINES Table
25.10 The INFORMATION_SCHEMA EVENTS Table
25.11 The INFORMATION_SCHEMA FILES Table
25.12 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
25.13 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
25.14 The INFORMATION_SCHEMA KEYWORDS Table
25.15 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
25.16 The INFORMATION_SCHEMA PARAMETERS Table
25.17 The INFORMATION_SCHEMA PARTITIONS Table
25.18 The INFORMATION_SCHEMA PLUGINS Table
25.19 The INFORMATION_SCHEMA PROCESSLIST Table
25.20 The INFORMATION_SCHEMA PROFILING Table
25.21 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
25.22 The INFORMATION_SCHEMA RESOURCE_GROUPS Table
25.23 The INFORMATION_SCHEMA ROUTINES Table
25.24 The INFORMATION_SCHEMA SCHEMATA Table
25.25 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
25.26 The INFORMATION_SCHEMA STATISTICS Table
25.27 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table
25.28 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table
25.29 The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table
25.30 The INFORMATION_SCHEMA TABLES Table
25.31 The INFORMATION_SCHEMA TABLESPACES Table
25.32 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
25.33 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
25.34 The INFORMATION_SCHEMA TRIGGERS Table
25.35 The INFORMATION_SCHEMA USER_PRIVILEGES Table
25.36 The INFORMATION_SCHEMA VIEWS Table
25.37 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table
25.38 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table
25.39 INFORMATION_SCHEMA InnoDB Tables
25.39.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
25.39.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
25.39.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
25.39.4 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table
25.39.5 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
25.39.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
25.39.7 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
25.39.8 The INFORMATION_SCHEMA INNODB_COLUMNS Table
25.39.9 The INFORMATION_SCHEMA INNODB_DATAFILES Table
25.39.10 The INFORMATION_SCHEMA INNODB_FIELDS Table
25.39.11 The INFORMATION_SCHEMA INNODB_FOREIGN Table
25.39.12 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table
25.39.13 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
25.39.14 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
25.39.15 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
25.39.16 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
25.39.17 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
25.39.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
25.39.19 The INFORMATION_SCHEMA INNODB_INDEXES Table
25.39.20 The INFORMATION_SCHEMA INNODB_LOCKS Table
25.39.21 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
25.39.22 The INFORMATION_SCHEMA INNODB_METRICS Table
25.39.23 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table
25.39.24 The INFORMATION_SCHEMA INNODB_TABLES Table
25.39.25 The INFORMATION_SCHEMA INNODB_TABLESPACES Table
25.39.26 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table
25.39.27 The INFORMATION_SCHEMA INNODB_TABLESTATS View
25.39.28 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
25.39.29 The INFORMATION_SCHEMA INNODB_TRX Table
25.39.30 The INFORMATION_SCHEMA INNODB_VIRTUAL Table
25.40 INFORMATION_SCHEMA Thread Pool Tables
25.40.1 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
25.40.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
25.40.3 The INFORMATION_SCHEMA TP_THREAD_STATE Table
25.41 INFORMATION_SCHEMA Connection-Control Tables
25.41.1 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
25.42 Extensions to SHOW Statements

INFORMATION_SCHEMA provides access to database metadata , information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog .

25.1 Introduction

INFORMATION_SCHEMA provides access to database metadata , information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog .

INFORMATION_SCHEMA Usage Notes

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT , UPDATE , or DELETE operations on them.

Here is an example of a statement that retrieves information from INFORMATION_SCHEMA :

mysql> SELECT table_name, table_type, engine
       FROM information_schema.tables
       WHERE table_schema = 'db5'
       ORDER BY table_name;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| fk         | BASE TABLE | InnoDB |
| fk2        | BASE TABLE | InnoDB |
| goto       | BASE TABLE | MyISAM |
| into       | BASE TABLE | MyISAM |
| k          | BASE TABLE | MyISAM |
| kurs       | BASE TABLE | MyISAM |
| loop       | BASE TABLE | MyISAM |
| pk         | BASE TABLE | InnoDB |
| t          | BASE TABLE | MyISAM |
| t2         | BASE TABLE | MyISAM |
| t3         | BASE TABLE | MyISAM |
| t7         | BASE TABLE | MyISAM |
| tables     | BASE TABLE | MyISAM |
| v          | VIEW       | NULL   |
| v2         | VIEW       | NULL   |
| v3         | VIEW       | NULL   |
| v56        | VIEW       | NULL   |
+------------+------------+--------+
17 rows in set (0.01 sec)
                        

Explanation: The statement requests a list of all the tables in database db5 , showing just three pieces of information: the name of the table, its type, and its storage engine.

Character Set Considerations

The definition for character columns (for example, TABLES.TABLE_NAME ) is generally VARCHAR( N ) CHARACTER SET utf8 where N is at least 64. MySQL uses the default collation for this character set ( utf8_general_ci ) for all searches, sorts, comparisons, and other string operations on such columns.

Because some MySQL objects are represented as files, searches in INFORMATION_SCHEMA string columns can be affected by file system case sensitivity. For more information, see Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches” .

INFORMATION_SCHEMA as Alternative to SHOW Statements

The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the various SHOW statements that MySQL supports ( SHOW DATABASES , SHOW TABLES , and so forth). Using SELECT has these advantages, compared to SHOW :

  • It conforms to Codd's rules, because all access is done on tables.

  • You can use the familiar syntax of the SELECT statement, and only need to learn some table and column names.

  • The implementor need not worry about adding keywords.

  • You can filter, sort, concatenate, and transform the results from INFORMATION_SCHEMA queries into whatever format your application needs, such as a data structure or a text representation to parse.

  • This technique is more interoperable with other database systems. For example, Oracle Database users are familiar with querying tables in the Oracle data dictionary.

Because SHOW is familiar and widely used, the SHOW statements remain as an alternative. In fact, along with the implementation of INFORMATION_SCHEMA , there are enhancements to SHOW as described in Section 25.42, “Extensions to SHOW Statements” .

INFORMATION_SCHEMA and Privileges

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA ROUTINES table), users who have insufficient privileges see NULL . These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege.

The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements. In either case, you must have some privilege on an object to see information about it.

Performance Considerations

INFORMATION_SCHEMA queries that search for information from more than one database might take a long time and impact performance. To check the efficiency of a query, you can use EXPLAIN . For information about using EXPLAIN output to tune INFORMATION_SCHEMA queries, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries” .

Standards Considerations

The implementation for the INFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11 Schemata . Our intent is approximate compliance with SQL:2003 core feature F021 Basic information schema .

Users of SQL Server 2000 (which also follows the standard) may notice a strong similarity. However, MySQL has omitted many columns that are not relevant for our implementation, and added columns that are MySQL-specific. One such added column is the ENGINE column in the INFORMATION_SCHEMA TABLES table.

Although other DBMSs use a variety of names, like syscat or system , the standard name is INFORMATION_SCHEMA .

To avoid using any name that is reserved in the standard or in DB2, SQL Server, or Oracle, we changed the names of some columns marked MySQL extension . (For example, we changed COLLATION to TABLE_COLLATION in the TABLES table.) See the list of reserved words near the end of this article: https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5 .

Conventions in the INFORMATION_SCHEMA Reference Sections

The following sections describe each of the tables and columns in INFORMATION_SCHEMA . For each column, there are three pieces of information:

  • INFORMATION_SCHEMA Name indicates the name for the column in the INFORMATION_SCHEMA table. This corresponds to the standard SQL name unless the Remarks field says MySQL extension.

  • SHOW Name indicates the equivalent field name in the closest SHOW statement, if there is one.

  • Remarks provides additional information where applicable. If this field is NULL , it means that the value of the column is always NULL . If this field says MySQL extension, the column is a MySQL extension to standard SQL.

Many sections indicate what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA . For SHOW statements that display information for the default database if you omit a FROM db_name clause, you can often select information for the default database by adding an AND TABLE_SCHEMA = SCHEMA() condition to the WHERE clause of a query that retrieves information from an INFORMATION_SCHEMA table.

Related Information

These sections discuss additional INFORMATION_SCHEMA -related topics:

25.2 The INFORMATION_SCHEMA CHARACTER_SETS Table

The CHARACTER_SETS table provides information about available character sets.

The CHARACTER_SETS table has these columns:

  • CHARACTER_SET_NAME

    The character set name.

  • DEFAULT_COLLATE_NAME

    The default collation for the character set.

  • DESCRIPTION

    A description of the character set.

  • MAXLEN

    The maximum number of bytes required to store one character.

Notes

Character set information is also available from the SHOW CHARACTER SET statement. See Section 13.7.6.3, “SHOW CHARACTER SET Syntax” . The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
  [WHERE CHARACTER_SET_NAME LIKE 'wild']
SHOW CHARACTER SET
  [LIKE 'wild']
                    

25.3 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table

The CHECK_CONSTRAINTS table (available as of MySQL 8.0.16) provides information about CHECK constraints defined on tables.

The CHECK_CONSTRAINTS table has these columns:

  • CONSTRAINT_CATALOG

    The name of the catalog to which the constraint belongs. This value is always def .

  • CONSTRAINT_SCHEMA

    The name of the schema (database) to which the constraint belongs.

  • CONSTRAINT_NAME

    The name of the constraint.

  • CHECK_CLAUSE

    The expression that specifies the constraint condition.

25.4 The INFORMATION_SCHEMA COLLATIONS Table

The COLLATIONS table provides information about collations for each character set.

The COLLATIONS table has these columns:

  • COLLATION_NAME

    The collation name.

  • CHARACTER_SET_NAME

    The name of the character set with which the collation is associated.

  • ID

    The collation ID.

  • IS_DEFAULT

    Whether the collation is the default for its character set.

  • IS_COMPILED

    Whether the character set is compiled into the server.

  • SORTLEN

    This is related to the amount of memory required to sort strings expressed in the character set.

  • PAD_ATTRIBUTE

    The collation pad attribute.

Notes

Collation information is also available from the SHOW COLLATION statement. See Section 13.7.6.4, “SHOW COLLATION Syntax” . The following statements are equivalent:

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
  [WHERE COLLATION_NAME LIKE 'wild']
SHOW COLLATION
  [LIKE 'wild']
                    

25.5 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table

The COLLATION_CHARACTER_SET_APPLICABILITY table indicates what character set is applicable for what collation.

The COLLATION_CHARACTER_SET_APPLICABILITY table has these columns:

  • COLLATION_NAME

    The collation name.

  • CHARACTER_SET_NAME

    The name of the character set with which the collation is associated.

Notes

The COLLATION_CHARACTER_SET_APPLICABILITY columns are equivalent to the first two columns displayed by the SHOW COLLATION statement.

25.6 The INFORMATION_SCHEMA COLUMNS Table

The COLUMNS table provides information about columns in tables. The related ST_GEOMETRY_COLUMNS table provides information about table columns that store spatial data. See Section 25.27, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table” .

The COLUMNS table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the table containing the column belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table containing the column belongs.

  • TABLE_NAME

    The name of the table containing the column.

  • COLUMN_NAME

    The name of the column.

  • ORDINAL_POSITION

    The position of the column within the table. ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION . Unlike SHOW COLUMNS , SELECT from the COLUMNS table does not have automatic ordering.

  • COLUMN_DEFAULT

    The default value for the column. This is NULL if the column has an explicit default of NULL , or if the column definition includes no DEFAULT clause.

  • IS_NULLABLE

    The column nullability. The value is YES if NULL values can be stored in the column, NO if not.

  • DATA_TYPE

    The column data type.

    The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.

  • CHARACTER_MAXIMUM_LENGTH

    For string columns, the maximum length in characters.

  • CHARACTER_OCTET_LENGTH

    For string columns, the maximum length in bytes.

  • NUMERIC_PRECISION

    For numeric columns, the numeric precision.

  • NUMERIC_SCALE

    For numeric columns, the numeric scale.

  • DATETIME_PRECISION

    For temporal columns, the fractional seconds precision.

  • CHARACTER_SET_NAME

    For character string columns, the character set name.

  • COLLATION_NAME

    For character string columns, the collation name.

  • COLUMN_TYPE

    The column data type.

    The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.

  • COLUMN_KEY

    Whether the column is indexed:

    • If COLUMN_KEY is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.

    • If COLUMN_KEY is PRI , the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY .

    • If COLUMN_KEY is UNI , the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null column.)

    • If COLUMN_KEY is MUL , the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.

    If more than one of the COLUMN_KEY values applies to a given column of a table, COLUMN_KEY displays the one with the highest priority, in the order PRI , UNI , MUL .

    A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

  • EXTRA

    Any additional information that is available about a given column. The value is nonempty in these cases:

    • auto_increment for columns that have the AUTO_INCREMENT attribute.

    • on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.

    • VIRTUAL GENERATED or VIRTUAL STORED for generated columns.

    • DEFAULT_GENERATED for columns that have an expression default value.

  • PRIVILEGES

    The privileges you have for the column.

  • COLUMN_COMMENT

    Any comment included in the column definition.

  • GENERATION_EXPRESSION

    For generated columns, displays the expression used to compute column values. Empty for nongenerated columns. For information about generated columns, see Section 13.1.20.9, “CREATE TABLE and Generated Columns” .

  • SRS_ID

    This value applies to spatial columns. It contains the column SRID value that indicates the the spatial reference system for values stored in the column. See Section 11.5.1, “Spatial Data Types” , and Section 11.5.5, “Spatial Reference System Support” . The value is NULL for nonspatial columns and spatial columns with no SRID attribute.

Notes

  • In SHOW COLUMNS , the Type display includes values from several different COLUMNS columns.

  • CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH , except for multibyte character sets.

  • CHARACTER_SET_NAME can be derived from COLLATION_NAME . For example, if you say SHOW FULL COLUMNS FROM t , and you see in the COLLATION_NAME column a value of utf8_swedish_ci , the character set is what is before the first underscore: utf8 .

Column information is also available from the SHOW COLUMNS statement. See Section 13.7.6.5, “SHOW COLUMNS Syntax” . The following statements are nearly equivalent:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']
SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE 'wild']
                    

25.7 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table

The COLUMN_PRIVILEGES table provides information about column privileges. It takes its values from the mysql.columns_priv system table.

The COLUMN_PRIVILEGES table has these columns:

  • GRANTEE

    The name of the account to which the privilege is granted, in ' user_name '@' host_name ' format.

  • TABLE_CATALOG

    The name of the catalog to which the table containing the column belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table containing the column belongs.

  • TABLE_NAME

    The name of the table containing the column.

  • COLUMN_NAME

    The name of the column.

  • PRIVILEGE_TYPE

    The privilege granted. The value can be any privilege that can be granted at the column level; see Section 13.7.1.6, “GRANT Syntax” . Each row lists a single privilege, so there is one row per column privilege held by the grantee.

    In the output from SHOW FULL COLUMNS , the privileges are all in one column and in lowercase, for example, select,insert,update,references . In COLUMN_PRIVILEGES , there is one privilege per row, in uppercase.

  • IS_GRANTABLE

    YES if the user has the GRANT OPTION privilege, NO otherwise. The output does not list GRANT OPTION as a separate row with PRIVILEGE_TYPE='GRANT OPTION' .

Notes

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SHOW GRANTS ...
                    

25.8 The INFORMATION_SCHEMA COLUMN_STATISTICS Table

The COLUMN_STATISTICS table provides access to histogram statistics for column values.

For information about histogram statistics, see Section 8.9.6, “Optimizer Statistics” , and Section 13.7.3.1, “ANALYZE TABLE Syntax” .

You can see information only for columns for which you have some privilege.

The COLUMN_STATISTICS table has these columns:

  • SCHEMA_NAME

    The names of the schema for which the statistics apply.

  • TABLE_NAME

    The names of the column for which the statistics apply.

  • COLUMN_NAME

    The names of the column for which the statistics apply.

  • HISTOGRAM

    A JSON object describing the column statistics, stored as a histogram.

25.9 The INFORMATION_SCHEMA ENGINES Table

The ENGINES table provides information about storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is.

The ENGINES table has these columns:

  • ENGINE

    The name of the storage engine.

  • SUPPORT

    The server's level of support for the storage engine, as shown in the following table.

    Meaning
    YES The engine is supported and is active
    DEFAULT Like YES , plus this is the default engine
    NO The engine is not supported
    DISABLED The engine is supported but has been disabled

    A value of NO means that the server was compiled without support for the engine, so it cannot be enabled at runtime.

    A value of DISABLED occurs either because the server was started with an option that disables the engine, or because not all options required to enable it were given. In the latter case, the error log should contain a reason indicating why the option is disabled. See Section 5.4.2, “The Error Log” .

    You might also see DISABLED for a storage engine if the server was compiled to support it, but was started with a --skip- engine_name option. For the NDB storage engine, DISABLED means the server was compiled with support for NDB Cluster, but was not started with the --ndbcluster option.

    All MySQL servers support MyISAM tables. It is not possible to disable MyISAM .

  • COMMENT

    A brief description of the storage engine.

  • TRANSACTIONS

    Whether the storage engine supports transactions.

  • XA

    Whether the storage engine supports XA transactions.

  • SAVEPOINTS

    Whether the storage engine supports savepoints.

Notes

  • The ENGINES table is a nonstandard INFORMATION_SCHEMA table.

Storage engine information is also available from the SHOW ENGINES statement. See Section 13.7.6.16, “SHOW ENGINES Syntax” . The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.ENGINES
SHOW ENGINES
                    

25.10 The INFORMATION_SCHEMA EVENTS Table

The EVENTS table provides information about Event Manager events, which are discussed in Section 24.4, “Using the Event Scheduler” .

The EVENTS table has these columns:

  • EVENT_CATALOG

    The name of the catalog to which the event belongs. This value is always def .

  • EVENT_SCHEMA

    The name of the schema (database) to which the event belongs.

  • EVENT_NAME

    The name of the event.

  • DEFINER

    The account of the user who created the event, in ' user_name '@' host_name ' format.

  • TIME_ZONE

    The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM .

  • EVENT_BODY

    The language used for the statements in the event's DO clause. The value is always SQL .

  • EVENT_DEFINITION

    The text of the SQL statement making up the event's DO clause; in other words, the statement executed by this event.

  • EVENT_TYPE

    The event repetition type, either ONE TIME (transient) or RECURRING (repeating).

  • EXECUTE_AT

    For a one-time event, this is the DATETIME value specified in the AT clause of the CREATE EVENT statement used to create the event, or of the last ALTER EVENT statement that modified the event. The value shown in this column reflects the addition or subtraction of any INTERVAL value included in the event's AT clause. For example, if an event is created using ON SCHEDULE AT CURRENT_TIMESTAMP + '1:6' DAY_HOUR , and the event was created at 2018-02-09 14:05:30, the value shown in this column would be '2018-02-10 20:05:30' . If the event's timing is determined by an EVERY clause instead of an AT clause (that is, if the event is recurring), the value of this column is NULL .

  • INTERVAL_VALUE

    For a recurring event, the number of intervals to wait between event executions. For a transient event, the value is always NULL .

  • INTERVAL_FIELD

    The time units used for the interval which a recurring event waits before repeating. For a transient event, the value is always NULL .

  • SQL_MODE

    The SQL mode in effect when the event was created or altered, and under which the event executes. For the permitted values, see Section 5.1.11, “Server SQL Modes” .

  • STARTS

    The start date and time for a recurring event. This is displayed as a DATETIME value, and is NULL if no start date and time are defined for the event. For a transient event, this column is always NULL . For a recurring event whose definition includes a STARTS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used. If there is no STARTS clause affecting the timing of the event, this column is NULL

  • ENDS

    For a recurring event whose definition includes a ENDS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used. If there is no ENDS clause affecting the timing of the event, this column is NULL .

  • STATUS

    The event status. One of ENABLED , DISABLED , or SLAVESIDE_DISABLED . SLAVESIDE_DISABLED indicates that the creation of the event occurred on another MySQL server acting as a replication master and replicated to the current MySQL server which is acting as a slave, but the event is not presently being executed on the slave. For more information, see Section 17.4.1.16, “Replication of Invoked Features” . information.

  • ON_COMPLETION

    One of the two values PRESERVE or NOT PRESERVE .

  • CREATED

    The date and time when the event was created. This is a TIMESTAMP value.

  • LAST_ALTERED

    The date and time when the event was last modified. This is a TIMESTAMP value. If the event has not been modified since its creation, this value is the same as the CREATED value.

  • LAST_EXECUTED

    The date and time when the event last executed. This is a DATETIME value. If the event has never executed, this column is NULL .

    LAST_EXECUTED indicates when the event started. As a result, the ENDS column is never less than LAST_EXECUTED .

  • EVENT_COMMENT

    The text of the comment, if the event has one. If not, this value is empty.

  • ORIGINATOR

    The server ID of the MySQL server on which the event was created; used in replication. This value may be updated by ALTER EVENT to the server ID of the server on which that statement occurs, if executed on a master server. The default value is 0.

  • CHARACTER_SET_CLIENT

    The session value of the character_set_client system variable when the event was created.

  • COLLATION_CONNECTION

    The session value of the collation_connection system variable when the event was created.

  • DATABASE_COLLATION

    The collation of the database with which the event is associated.

Notes

Example

Suppose that the user 'jon'@'ghidora' creates an event named e_daily , and then modifies it a few minutes later using an ALTER EVENT statement, as shown here:

DELIMITER |
CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |
DELIMITER ;
ALTER EVENT e_daily
    ENABLE;
                    

(Note that comments can span multiple lines.)

This user can then run the following SELECT statement, and obtain the output shown:

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
       WHERE EVENT_NAME = 'e_daily'
       AND EVENT_SCHEMA = 'myschema'\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: myschema
          EVENT_NAME: e_daily
             DEFINER: jon@ghidora
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      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_ENGINE_SUBSTITUTION
              STARTS: 2018-08-08 11:06:34
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2018-08-08 11:06:34
        LAST_ALTERED: 2018-08-08 11:06:34
       LAST_EXECUTED: 2018-08-08 16:06:34
       EVENT_COMMENT: Saves total number of sessions then clears the
                      table each day
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  DATABASE_COLLATION: utf8mb4_0900_ai_ci
                    

Event information is also available from the SHOW EVENTS statement. See Section 13.7.6.18, “SHOW EVENTS Syntax” . The following statements are equivalent:

SELECT
    EVENT_SCHEMA, EVENT_NAME, DEFINER, TIME_ZONE, EVENT_TYPE, EXECUTE_AT,
    INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ORIGINATOR,
    CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE table_schema = 'db_name'
  [AND column_name LIKE 'wild']
SHOW EVENTS
  [FROM db_name]
  [LIKE 'wild']
                    

25.11 The INFORMATION_SCHEMA FILES Table

The FILES table provides information about the files in which MySQL tablespace data is stored.

The FILES table provides information about InnoDB data files. In NDB Cluster, this table also provides information about the files in which NDB Cluster Disk Data tables are stored. For additional information specific to InnoDB , see InnoDB Notes , later in this section; for additional information specific to NDB Cluster, see NDB Notes .

The FILES table has these columns:

  • FILE_ID

    For InnoDB : The tablespace ID, also referred to as the space_id or fil_space_t::id .

    For NDB : A file identifier. FILE_ID column values are auto-generated.

  • FILE_NAME

    For InnoDB : The name of the data file. File-per-table and general tablespaces have an .ibd file name extension. Undo tablespaces are prefixed by undo . The system tablespace is prefixed by ibdata . The global temporary tablespace is prefixed by ibtmp . The file name includes the file path, which may be relative to the MySQL data directory (the value of the datadir system variable).

    For NDB : The name of an UNDO log file created by CREATE LOGFILE GROUP or ALTER LOGFILE GROUP , or of a data file created by CREATE TABLESPACE or ALTER TABLESPACE .

  • FILE_TYPE

    For InnoDB : The tablespace file type. There are three possible file types for InnoDB files. TABLESPACE is the file type for any system, general, or file-per-table tablespace file that holds tables, indexes, or other forms of user data. TEMPORARY is the file type for temporary tablespaces. UNDO LOG is the file type for undo tablespaces, which hold undo records.

    For NDB : One of the values UNDO LOG , DATAFILE , or TABLESPACE .

  • TABLESPACE_NAME

    For InnoDB : The SQL name for the tablespace. A general tablespace name is the SYS_TABLESPACES.NAME value. For other tablespace files, names start with innodb_ , such as innodb_system , innodb_undo , and innodb_file_per_table . The file-per-table tablespace name format is innodb_file_per_table_ ## , where ## is the tablespace ID.

    For NDB : The name of the tablespace with which the file is associated.

  • TABLE_CATALOG

    This value is always empty.

  • TABLE_SCHEMA

    This value is always NULL .

  • TABLE_NAME

    For InnoDB : This value is always NULL .

    For NDB : The name of the Disk Data table with which the file is associated, if any.

  • LOGFILE_GROUP_NAME

    For InnoDB : This value is always NULL .

    For NDB : The name of the log file group to which the log file or data file belongs.

  • LOGFILE_GROUP_NUMBER

    For InnoDB : This value is always NULL .

    For NDB : For an UNDO log file, the auto-generated ID number of the log file group to which the log file belongs.

  • ENGINE

    For InnoDB : This value is always InnoDB .

    For NDB : For an NDB Cluster Disk Data log file or data file, this value is always NDB or NDBCLUSTER .

  • FULLTEXT_KEYS

    For InnoDB : This value is always NULL .

    For NDB : For an NDB Cluster Disk Data log file or data file, this value is always empty.

  • DELETED_ROWS

    For InnoDB : This value is always NULL .

  • UPDATE_COUNT

    For InnoDB : This value is always NULL .

  • FREE_EXTENTS

    For InnoDB : The number of fully free extents in the current data file.

    For NDB : The number of extents which have not yet been used by the file.

  • TOTAL_EXTENTS

    For InnoDB : The number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

    For NDB : The total number of extents allocated to the file.

  • EXTENT_SIZE

    For InnoDB : Extent size is 1048576 (1MB) for files with a 4KB, 8KB, or 16KB page size. Extent size is 2097152 bytes (2MB) for files with a 32KB page size, and 4194304 (4MB) for files with a 64KB page size. FILES does not report InnoDB page size. Page size is defined by the innodb_page_size system variable. Extent size information can also be retrieved from the INNODB_TABLESPACES table where FILES.FILE_ID = INNODB_TABLESPACES.SPACE .

    For NDB : The size of an extent for the file in bytes.

  • INITIAL_SIZE

    For InnoDB : The initial size of the file in bytes.

    For NDB : The size of the file in bytes. This is the same value that was used in the INITIAL_SIZE clause of the CREATE LOGFILE GROUP , ALTER LOGFILE GROUP , CREATE TABLESPACE , or ALTER TABLESPACE statement used to create the file.

  • MAXIMUM_SIZE

    For InnoDB : The maximum number of bytes permitted in the file. The value is NULL for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined by innodb_data_file_path . Maximum global temporary tablespace file size is defined by innodb_temp_data_file_path . A NULL value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly.

    For NDB : For NDB Cluster Disk Data files, this value is always the same as the INITIAL_SIZE value.

  • AUTOEXTEND_SIZE

    For InnoDB : AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or by innodb_temp_data_file_path for the global temporary tablespace.

    For NDB : For NDB Cluster Disk Data files, this value is always empty.

  • CREATION_TIME

    For InnoDB : This value is always NULL .

    For NDB : The date and time when the file was created.

  • LAST_UPDATE_TIME

    For InnoDB : This value is always NULL .

    For NDB : The date and time when the file was last modified.

  • LAST_ACCESS_TIME

    For InnoDB : This value is always NULL .

    For NDB : The date and time when the file was last accessed by the server.

  • RECOVER_TIME

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always 0 .

  • TRANSACTION_COUNTER

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always 0 .

  • VERSION

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • ROW_FORMAT

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • TABLE_ROWS

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • AVG_ROW_LENGTH

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • DATA_LENGTH

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • MAX_DATA_LENGTH

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • INDEX_LENGTH

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • DATA_FREE

    For InnoDB : The total amount of free space (in bytes) for the entire tablespace. Predefined system tablespaces, which include the system tablespace and temporary table tablespaces, may have one or more data files.

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • CREATE_TIME

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • UPDATE_TIME

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • CHECK_TIME

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • CHECKSUM

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, this value is always NULL .

  • STATUS

    For InnoDB : This value is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING , which indicates that the tablespace is not yet available.

    For NDB : For NDB Cluster Disk Data files, this value is always NORMAL .

  • EXTRA

    For InnoDB : This value is always NULL .

    For NDB : For NDB Cluster Disk Data files, the EXTRA column shows which data node the file belongs to (each data node having its own copy), as well as the size of its undo buffer. Suppose that you use this statement on an NDB Cluster with four data nodes:

    CREATE LOGFILE GROUP mygroup
        ADD UNDOFILE 'new_undo.dat'
        INITIAL_SIZE 2G
        ENGINE NDB;
                                    

    After running the CREATE LOGFILE GROUP statement successfully, you should see a result similar to the one shown here for this query against the FILES table:

    mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
             FROM INFORMATION_SCHEMA.FILES
             WHERE FILE_NAME = 'new_undo.dat';
    +--------------------+-----------+-----------------------------------------+
    | LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA                                   |
    +--------------------+-----------+-----------------------------------------+
    | mygroup            | UNDO LOG  | CLUSTER_NODE=5;UNDO_BUFFER_SIZE=8388608 |
    | mygroup            | UNDO LOG  | CLUSTER_NODE=6;UNDO_BUFFER_SIZE=8388608 |
    | mygroup            | UNDO LOG  | CLUSTER_NODE=7;UNDO_BUFFER_SIZE=8388608 |
    | mygroup            | UNDO LOG  | CLUSTER_NODE=8;UNDO_BUFFER_SIZE=8388608 |
    +--------------------+-----------+-----------------------------------------+
                                    

    This information was not included in NDB Cluster 8.0 prior to NDB 8.0.15. (Bug #92796, Bug #28800252)

Notes

  • The FILES table is a nonstandard INFORMATION_SCHEMA table.

InnoDB Notes

The following notes apply to InnoDB data files.

  • Data reported by FILES is reported from the InnoDB in-memory cache for open files. By comparison, INNODB_DATAFILES reports data from the InnoDB SYS_DATAFILES internal data dictionary table.

  • The data reported by FILES includes global temporary tablespace data. This data is not available in the InnoDB SYS_DATAFILES internal data dictionary table, and is therefore not reported by INNODB_DATAFILES .

  • Undo tablespace data is reported by FILES when separate undo tablespaces are present, which they are by default in MySQL 8.0

  • The following query returns all data pertinent to InnoDB tablespaces.

    SELECT
      FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
      TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE,
      AUTOEXTEND_SIZE, DATA_FREE, STATUS
    FROM INFORMATION_SCHEMA.FILES WHERE ENGINE='InnoDB'\G
                                        

NDB Notes

  • The FILES table provides information about Disk Data files only; you cannot use it for determining disk space allocation or availability for individual NDB tables. However, it is possible to see how much space is allocated for each NDB table having data stored on disk—as well as how much remains available for storage of data on disk for that table—using ndb_desc . For more information, see Section 22.4.9, “ ndb_desc — Describe NDB Tables” .

  • The CREATION_TIME , LAST_UPDATE_TIME , and LAST_ACCESSED values are as reported by the operating system, and are not supplied by the NDB storage engine. Where no value is provided by the operating system, these columns display 0000-00-00 00:00:00 .

  • The difference between the TOTAL EXTENTS and FREE_EXTENTS columns is the number of extents currently in use by the file:

    SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used
        FROM INFORMATION_SCHEMA.FILES
        WHERE FILE_NAME = 'myfile.dat';
                                        

    To approximate the amount of disk space in use by the file, multiply that difference by the value of the EXTENT_SIZE column, which gives the size of an extent for the file in bytes:

    SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
        FROM INFORMATION_SCHEMA.FILES
        WHERE FILE_NAME = 'myfile.dat';
                                        

    Similarly, you can estimate the amount of space that remains available in a given file by multiplying FREE_EXTENTS by EXTENT_SIZE :

    SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
        FROM INFORMATION_SCHEMA.FILES
        WHERE FILE_NAME = 'myfile.dat';
                                        
    Important

    The byte values produced by the preceding queries are approximations only, and their precision is inversely proportional to the value of EXTENT_SIZE . That is, the larger EXTENT_SIZE becomes, the less accurate the approximations are.

    It is also important to remember that once an extent is used, it cannot be freed again without dropping the data file of which it is a part. This means that deletes from a Disk Data table do not release disk space.

    The extent size can be set in a CREATE TABLESPACE statement. For more information, see Section 13.1.21, “CREATE TABLESPACE Syntax” .

  • An additional row is present in the FILES table following the creation of a logfile group. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0 , that of the FILE_TYPE column is always UNDO FILE , and that of the STATUS column is always NORMAL . The value of the ENGINE column is always NDBCLUSTER .

    The FREE_EXTENTS column in this row shows the total number of free extents available to all undo files belonging to a given log file group whose name and number are shown in the LOGFILE_GROUP_NAME and LOGFILE_GROUP_NUMBER columns, respectively.

    Suppose there are no existing log file groups on your NDB Cluster, and you create one using the following statement:

    mysql> CREATE LOGFILE GROUP lg1
             ADD UNDOFILE 'undofile.dat'
             INITIAL_SIZE = 16M
             UNDO_BUFFER_SIZE = 1M
             ENGINE = NDB;
                                        

    You can now see this NULL row when you query the FILES table:

    mysql> SELECT DISTINCT
             FILE_NAME AS File,
             FREE_EXTENTS AS Free,
             TOTAL_EXTENTS AS Total,
             EXTENT_SIZE AS Size,
             INITIAL_SIZE AS Initial
             FROM INFORMATION_SCHEMA.FILES;
    +--------------+---------+---------+------+----------+
    | File         | Free    | Total   | Size | Initial  |
    +--------------+---------+---------+------+----------+
    | undofile.dat |    NULL | 4194304 |    4 | 16777216 |
    | NULL         | 4184068 |    NULL |    4 |     NULL |
    +--------------+---------+---------+------+----------+
                                        

    The total number of free extents available for undo logging is always somewhat less than the sum of the TOTAL_EXTENTS column values for all undo files in the log file group due to overhead required for maintaining the undo files. This can be seen by adding a second undo file to the log file group, then repeating the previous query against the FILES table:

    mysql> ALTER LOGFILE GROUP lg1
             ADD UNDOFILE 'undofile02.dat'
             INITIAL_SIZE = 4M
             ENGINE = NDB;
    mysql> SELECT DISTINCT
             FILE_NAME AS File,
             FREE_EXTENTS AS Free,
             TOTAL_EXTENTS AS Total,
             EXTENT_SIZE AS Size,
             INITIAL_SIZE AS Initial
             FROM INFORMATION_SCHEMA.FILES;
    +----------------+---------+---------+------+----------+
    | File           | Free    | Total   | Size | Initial  |
    +----------------+---------+---------+------+----------+
    | undofile.dat   |    NULL | 4194304 |    4 | 16777216 |
    | undofile02.dat |    NULL | 1048576 |    4 |  4194304 |
    | NULL           | 5223944 |    NULL |    4 |     NULL |
    +----------------+---------+---------+------+----------+
                                        

    The amount of free space in bytes which is available for undo logging by Disk Data tables using this log file group can be approximated by multiplying the number of free extents by the initial size:

    mysql> SELECT
             FREE_EXTENTS AS 'Free Extents',
             FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
             FROM INFORMATION_SCHEMA.FILES
             WHERE LOGFILE_GROUP_NAME = 'lg1'
             AND FILE_NAME IS NULL;
    +--------------+------------+
    | Free Extents | Free Bytes |
    +--------------+------------+
    |      5223944 |   20895776 |
    +--------------+------------+
                                        

    If you create an NDB Cluster Disk Data table and then insert some rows into it, you can see approximately how much space remains for undo logging afterward, for example:

    mysql> CREATE TABLESPACE ts1
             ADD DATAFILE 'data1.dat'
             USE LOGFILE GROUP lg1
             INITIAL_SIZE 512M
             ENGINE = NDB;
    mysql> CREATE TABLE dd (
             c1 INT NOT NULL PRIMARY KEY,
             c2 INT,
             c3 DATE
             )
             TABLESPACE ts1 STORAGE DISK
             ENGINE = NDB;
    mysql> INSERT INTO dd VALUES
             (NULL, 1234567890, '2007-02-02'),
             (NULL, 1126789005, '2007-02-03'),
             (NULL, 1357924680, '2007-02-04'),
             (NULL, 1642097531, '2007-02-05');
    mysql> SELECT
             FREE_EXTENTS AS 'Free Extents',
             FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
             FROM INFORMATION_SCHEMA.FILES
             WHERE LOGFILE_GROUP_NAME = 'lg1'
             AND FILE_NAME IS NULL;
    +--------------+------------+
    | Free Extents | Free Bytes |
    +--------------+------------+
    |      5207565 |   20830260 |
    +--------------+------------+
                                        
  • An additional row is present in the FILES table for any NDB Cluster tablespace, whether or not any data files are associated with the tablespace. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0 , that of the FILE_TYPE column is always TABLESPACE , and that of the STATUS column is always NORMAL . The value of the ENGINE column is always NDBCLUSTER .

  • For additional information, and examples of creating and dropping NDB Cluster Disk Data objects, see Section 22.5.13, “NDB Cluster Disk Data Tables” .

25.12 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

The KEY_COLUMN_USAGE table describes which key columns have constraints. This table provides no information about functional key parts because they are expressions and the table provides information only about columns.

The KEY_COLUMN_USAGE table has these columns:

  • CONSTRAINT_CATALOG

    The name of the catalog to which the constraint belongs. This value is always def .

  • CONSTRAINT_SCHEMA

    The name of the schema (database) to which the constraint belongs.

  • CONSTRAINT_NAME

    The name of the constraint.

  • TABLE_CATALOG

    The name of the catalog to which the table belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table belongs.

  • TABLE_NAME

    The name of the table that has the constraint.

  • COLUMN_NAME

    The name of the column that has the constraint.

    If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.

  • ORDINAL_POSITION

    The column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.

  • POSITION_IN_UNIQUE_CONSTRAINT

    NULL for unique and primary-key constraints. For foreign-key constraints, this column is the ordinal position in key of the table that is being referenced.

  • REFERENCED_TABLE_SCHEMA

    The name of the schema referenced by the constraint.

  • REFERENCED_TABLE_NAME

    The name of the table referenced by the constraint.

  • REFERENCED_COLUMN_NAME

    The name of the column referenced by the constraint.

Suppose that there are two tables name t1 and t3 that have the following definitions:

CREATE TABLE t1
(
    s1 INT,
    s2 INT,
    s3 INT,
    PRIMARY KEY(s3)
) ENGINE=InnoDB;
CREATE TABLE t3
(
    s1 INT,
    s2 INT,
    s3 INT,
    KEY(s1),
    CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;
                    

For those two tables, the KEY_COLUMN_USAGE table has two rows:

  • One row with CONSTRAINT_NAME = 'PRIMARY' , TABLE_NAME = 't1' , COLUMN_NAME = 's3' , ORDINAL_POSITION = 1 , POSITION_IN_UNIQUE_CONSTRAINT = NULL .

    For NDB : This value is always NULL .

  • One row with CONSTRAINT_NAME = 'CO' , TABLE_NAME = 't3' , COLUMN_NAME = 's2' , ORDINAL_POSITION = 1 , POSITION_IN_UNIQUE_CONSTRAINT = 1 .

25.13 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table

The ndb_transid_mysql_connection_map table provides a mapping between NDB transactions, NDB transaction coordinators, and MySQL Servers attached to an NDB Cluster as API nodes. This information is used when populating the server_operations and server_transactions tables of the ndbinfo NDB Cluster information database.

INFORMATION_SCHEMA Name SHOW Name Remarks
mysql_connection_id MySQL Server connection ID
node_id Transaction coordinator node ID
ndb_transid NDB transaction ID

The mysql_connection_id is the same as the connection or session ID shown in the output of SHOW PROCESSLIST .

There are no SHOW statements associated with this table.

This is a nonstandard table, specific to NDB Cluster. It is implemented as an INFORMATION_SCHEMA plugin; you can verify that it is supported by checking the output of SHOW PLUGINS . If ndb_transid_mysql_connection_map support is enabled, the output from this statement includes a plugin having this name, of type INFORMATION SCHEMA , and having status ACTIVE , as shown here (using emphasized text):

mysql> SHOW PLUGINS;
+----------------------------------+--------+--------------------+---------+---------+
| Name                             | Status | Type               | Library | License |
+----------------------------------+--------+--------------------+---------+---------+
| binlog                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password            | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| CSV                              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                       | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                        | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                          | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                       | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ndbinfo                          | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ndb_transid_mysql_connection_map | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| InnoDB                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS                | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| partition                        | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------------+--------+--------------------+---------+---------+
22 rows in set (0.00 sec)
                    

The plugin is enabled by default. You can disable it (or force the server not to run unless the plugin starts) by starting the server with the --ndb-transid-mysql-connection-map option. If the plugin is disabled, the status is shown by SHOW PLUGINS as DISABLED . The plugin cannot be enabled or disabled at runtime.

Although the names of this table and its columns are displayed using lowercase, you can use uppercase or lowercase when referring to them in SQL statements.

For this table to be created, the MySQL Server must be a binary supplied with the NDB Cluster distribution, or one built from the NDB Cluster sources with NDB storage engine support enabled. It is not available in the standard MySQL 8.0 Server.

25.14 The INFORMATION_SCHEMA KEYWORDS Table

The KEYWORDS table lists the words considered keywords by MySQL and, for each one, indicates whether it is reserved. Reserved keywords may require special treatment in some contexts, such as special quoting when used as identifiers (see Section 9.3, “Keywords and Reserved Words” ). This table provides applications a runtime source of MySQL keyword information.

Prior to MySQL 8.0.13, selecting from the KEYWORDS table with no default database selected produced an error. (Bug #90160, Bug #27729859)

The KEYWORDS table has these columns:

  • WORD

    The keyword.

  • RESERVED

    An integer indicating whether the keyword is reserved (1) or nonreserved (0).

These queries lists all keywords, all reserved keywords, and all nonreserved keywords, respectively:

SELECT * FROM INFORMATION_SCHEMA.KEYWORDS;
SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED = 1;
SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED = 0;
                    

The latter two queries are equivalent to:

SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED;
SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE NOT RESERVED;
                    

If you build MySQL from source, the build process generates a keyword_list.h header file containing an array of keywords and their reserved status. This file can be found in the sql directory under the build directory. This file may be useful for applications that require a static source for the keyword list.

25.15 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table

The OPTIMIZER_TRACE table provides information produced by the optimizer tracing capability for traced statements. To enable tracking, use the optimizer_trace system variable. For details, see MySQL Internals: Tracing the Optimizer .

The OPTIMIZER_TRACE table has these columns:

  • QUERY

    The text of the traced statement.

  • TRACE

    The trace, in JSON format.

  • MISSING_BYTES_BEYOND_MAX_MEM_SIZE

    Each remembered trace is a string that is extended as optimization progresses and appends data to it. The optimizer_trace_max_mem_size variable sets a limit on the total amount of memory used by all currently remembered traces. If this limit is reached, the current trace is not extended (and thus is incomplete), and the MISSING_BYTES_BEYOND_MAX_MEM_SIZE column shows the number of bytes missing from the trace.

  • INSUFFICIENT_PRIVILEGES

    If a traced query uses views or stored routines that have SQL SECURITY with a value of DEFINER , it may be that a user other than the definer is denied from seeing the trace of the query. In that case, the trace is shown as empty and INSUFFICIENT_PRIVILEGES has a value of 1. Otherwise, the value is 0.

25.16 The INFORMATION_SCHEMA PARAMETERS Table

The PARAMETERS table provides information about parameters for stored routines (stored procedures and stored functions), and about return values for stored functions. The PARAMETERS table does not include built-in SQL functions or user-defined functions (UDFs).

The PARAMETERS table has these columns:

  • SPECIFIC_CATALOG

    The name of the catalog to which the routine containing the parameter belongs. This value is always def .

  • SPECIFIC_SCHEMA

    The name of the schema (database) to which the routine containing the parameter belongs.

  • SPECIFIC_NAME

    The name of the routine containing the parameter.

  • ORDINAL_POSITION

    For successive parameters of a stored procedure or function, the ORDINAL_POSITION values are 1, 2, 3, and so forth. For a stored function, there is also a row that applies to the function return value (as described by the RETURNS clause). The return value is not a true parameter, so the row that describes it has these unique characteristics:

    • The ORDINAL_POSITION value is 0.

    • The PARAMETER_NAME and PARAMETER_MODE values are NULL because the return value has no name and the mode does not apply.

  • PARAMETER_MODE

    The mode of the parameter. This value is one of IN , OUT , or INOUT . For a stored function return value, this value is NULL .

  • PARAMETER_NAME

    The name of the parameter. For a stored function return value, this value is NULL .

  • DATA_TYPE

    The parameter data type.

    The DATA_TYPE value is the type name only with no other information. The DTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.

  • CHARACTER_MAXIMUM_LENGTH

    For string parameters, the maximum length in characters.

  • CHARACTER_OCTET_LENGTH

    For string parameters, the maximum length in bytes.

  • NUMERIC_PRECISION

    For numeric parameters, the numeric precision.

  • NUMERIC_SCALE

    For numeric parameters, the numeric scale.

  • DATETIME_PRECISION

    For temporal parameters, the fractional seconds precision.

  • CHARACTER_SET_NAME

    For character string parameters, the character set name.

  • COLLATION_NAME

    For character string parameters, the collation name.

  • DTD_IDENTIFIER

    The parameter data type.

    The DATA_TYPE value is the type name only with no other information. The DTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.

  • ROUTINE_TYPE

    PROCEDURE for stored procedures, FUNCTION for stored functions.

25.17 The INFORMATION_SCHEMA PARTITIONS Table

The PARTITIONS table provides information about table partitions. Each row in this table corresponds to an individual partition or subpartition of a partitioned table. For more information about partitioning tables, see Chapter 23, Partitioning .

The PARTITIONS table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the table belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table belongs.

  • TABLE_NAME

    The name of the table containing the partition.

  • PARTITION_NAME

    The name of the partition.

  • SUBPARTITION_NAME

    If the PARTITIONS table row represents a subpartition, the name of subpartition; otherwise NULL .

    For NDB : This value is always NULL .

  • PARTITION_ORDINAL_POSITION

    All partitions are indexed in the same order as they are defined, with 1 being the number assigned to the first partition. The indexing can change as partitions are added, dropped, and reorganized; the number shown is this column reflects the current order, taking into account any indexing changes.

  • SUBPARTITION_ORDINAL_POSITION

    Subpartitions within a given partition are also indexed and reindexed in the same manner as partitions are indexed within a table.

  • PARTITION_METHOD

    One of the values RANGE , LIST , HASH , LINEAR HASH , KEY , or LINEAR KEY ; that is, one of the available partitioning types as discussed in Section 23.2, “Partitioning Types” .

  • SUBPARTITION_METHOD

    One of the values HASH , LINEAR HASH , KEY , or LINEAR KEY ; that is, one of the available subpartitioning types as discussed in Section 23.2.6, “Subpartitioning” .

  • PARTITION_EXPRESSION

    The expression for the partitioning function used in the CREATE TABLE or ALTER TABLE statement that created the table's current partitioning scheme.

    For example, consider a partitioned table created in the test database using this statement:

    CREATE TABLE tp (
        c1 INT,
        c2 INT,
        c3 VARCHAR(25)
    )
    PARTITION BY HASH(c1 + c2)
    PARTITIONS 4;
                                    

    The PARTITION_EXPRESSION column in a PARTITIONS table row for a partition from this table displays c1 + c2 , as shown here:

    mysql> SELECT DISTINCT PARTITION_EXPRESSION
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';
    +----------------------+
    | PARTITION_EXPRESSION |
    +----------------------+
    | c1 + c2              |
    +----------------------+
                                    
  • SUBPARTITION_EXPRESSION

    This works in the same fashion for the subpartitioning expression that defines the subpartitioning for a table as PARTITION_EXPRESSION does for the partitioning expression used to define a table's partitioning.

    If the table has no subpartitions, this column is NULL .

  • PARTITION_DESCRIPTION

    This column is used for RANGE and LIST partitions. For a RANGE partition, it contains the value set in the partition's VALUES LESS THAN clause, which can be either an integer or MAXVALUE . For a LIST partition, this column contains the values defined in the partition's VALUES IN clause, which is a list of comma-separated integer values.

    For partitions whose PARTITION_METHOD is other than RANGE or LIST , this column is always NULL .

  • TABLE_ROWS

    The number of table rows in the partition.

    For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact.

    For NDB tables, you can also obtain this information using the ndb_desc utility.

  • AVG_ROW_LENGTH

    The average length of the rows stored in this partition or subpartition, in bytes. This is the same as DATA_LENGTH divided by TABLE_ROWS .

    For NDB tables, you can also obtain this information using the ndb_desc utility.

  • DATA_LENGTH

    The total length of all rows stored in this partition or subpartition, in bytes; that is, the total number of bytes stored in the partition or subpartition.

    For NDB tables, you can also obtain this information using the ndb_desc utility.

  • MAX_DATA_LENGTH

    The maximum number of bytes that can be stored in this partition or subpartition.

    For NDB tables, you can also obtain this information using the ndb_desc utility.

  • INDEX_LENGTH

    The length of the index file for this partition or subpartition, in bytes.

    For partitions of NDB tables, whether the tables use implicit or explicit partitioning, the INDEX_LENGTH column value is always 0. However, you can obtain equivalent information using the ndb_desc utility.

  • DATA_FREE

    The number of bytes allocated to the partition or subpartition but not used.

    For NDB tables, you can also obtain this information using the ndb_desc utility.

  • CREATE_TIME

    The time that the partition or subpartition was created.

  • UPDATE_TIME

    The time that the partition or subpartition was last modified.

  • CHECK_TIME

    The last time that the table to which this partition or subpartition belongs was checked.

    For partitioned InnoDB tables, the value is always NULL .

  • CHECKSUM

    The checksum value, if any; otherwise NULL .

  • PARTITION_COMMENT

    The text of the comment, if the partition has one. If not, this value is empty.

    The maximum length for a partition comment is defined as 1024 characters, and the display width of the PARTITION_COMMENT column is also 1024, characters to match this limit.

  • NODEGROUP

    This is the nodegroup to which the partition belongs. This is relevant only to NDB Cluster tables; otherwise, the value is always 0 .

  • TABLESPACE_NAME

    The name of the tablespace to which the partition belongs. The value is always DEFAULT , unless the table uses the NDB storage engine (see the Notes at the end of this section).

Notes

  • The PARTITIONS table is a nonstandard INFORMATION_SCHEMA table.

  • A table using any storage engine other than NDB and which is not partitioned has one row in the PARTITIONS table. However, the values of the PARTITION_NAME , SUBPARTITION_NAME , PARTITION_ORDINAL_POSITION , SUBPARTITION_ORDINAL_POSITION , PARTITION_METHOD , SUBPARTITION_METHOD , PARTITION_EXPRESSION , SUBPARTITION_EXPRESSION , and PARTITION_DESCRIPTION columns are all NULL . Also, the PARTITION_COMMENT column in this case is blank.

  • An NDB table which is not explicitly partitioned has one row in the PARTITIONS table for each data node in the NDB cluster. For each such row:

    • The SUBPARTITION_NAME , SUBPARTITION_ORDINAL_POSITION , SUBPARTITION_METHOD , PARTITION_EXPRESSION , SUBPARTITION_EXPRESSION , CREATE_TIME , UPDATE_TIME , CHECK_TIME , CHECKSUM , and TABLESPACE_NAME columns are all NULL .

    • The PARTITION_METHOD is always AUTO .

    • The NODEGROUP column is default .

    • The PARTITION_EXPRESSION and PARTITION_COMMENT columns are empty.

25.18 The INFORMATION_SCHEMA PLUGINS Table

The PLUGINS table provides information about server plugins.

The PLUGINS table has these columns:

  • PLUGIN_NAME

    The name used to refer to the plugin in statements such as INSTALL PLUGIN and UNINSTALL PLUGIN .

  • PLUGIN_VERSION

    The version from the plugin's general type descriptor.

  • PLUGIN_STATUS

    The plugin status, one of ACTIVE , INACTIVE , DISABLED , DELETING , or DELETED .

  • PLUGIN_TYPE

    The type of plugin, such as STORAGE ENGINE , INFORMATION_SCHEMA , or AUTHENTICATION .

  • PLUGIN_TYPE_VERSION

    The version from the plugin's type-specific descriptor.

  • PLUGIN_LIBRARY

    The name of the plugin shared library file. This is the name used to refer to the plugin file in statements such as INSTALL PLUGIN and UNINSTALL PLUGIN . This file is located in the directory named by the plugin_dir system variable. If the library name is NULL , the plugin is compiled in and cannot be uninstalled with UNINSTALL PLUGIN .

  • PLUGIN_LIBRARY_VERSION

    The plugin API interface version.

  • PLUGIN_AUTHOR

    The plugin author.

  • PLUGIN_DESCRIPTION

    A short description of the plugin.

  • PLUGIN_LICENSE

    How the plugin is licensed; for example, GPL .

  • LOAD_OPTION

    How the plugin was loaded. The value is OFF , ON , FORCE , or FORCE_PLUS_PERMANENT . See Section 5.6.1, “Installing and Uninstalling Plugins” .

Notes

Plugin information is also available from the SHOW PLUGINS statement. See Section 13.7.6.25, “SHOW PLUGINS Syntax” . These statements are equivalent:

SELECT
  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
  PLUGIN_LIBRARY, PLUGIN_LICENSE
FROM INFORMATION_SCHEMA.PLUGINS;
SHOW PLUGINS;
                    

25.19 The INFORMATION_SCHEMA PROCESSLIST Table

The PROCESSLIST table provides information about which threads are running.

The PROCESSLIST table has these columns:

  • ID

    The connection identifier. This is the same type of value displayed in the Id column of the SHOW PROCESSLIST statement, the PROCESSLIST_ID column of the Performance Schema threads table, and returned by the CONNECTION_ID() function.

  • USER

    The MySQL user who issued the statement. A value of system user refers to a nonclient thread spawned by the server to handle tasks internally. This could be the I/O or SQL thread used on replication slaves or a delayed-row handler. For system user , there is no host specified in the Host column. unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done. event_scheduler refers to the thread that monitors scheduled events (see Section 24.4, “Using the Event Scheduler” ).

  • HOST

    The host name of the client issuing the statement (except for system user , for which there is no host). The host name for TCP/IP connections is reported in host_name : client_port format to make it easier to determine which client is doing what.

  • DB

    The default database, if one is selected; otherwise NULL .

  • COMMAND

    The type of command the thread is executing. For descriptions for thread commands, see Section 8.14, “Examining Thread Information” . The value of this column corresponds to the COM_ xxx commands of the client/server protocol and Com_ xxx status variables. See Section 5.1.10, “Server Status Variables”

  • TIME

    The time in seconds that the thread has been in its current state. For a slave SQL thread, the value is the number of seconds between the timestamp of the last replicated event and the real time of the slave machine. See Section 17.2.2, “Replication Implementation Details” .

  • STATE

    An action, event, or state that indicates what the thread is doing. Descriptions for STATE values can be found at Section 8.14, “Examining Thread Information” .

    Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.

    For the SHOW PROCESSLIST statement, the value of STATE is NULL .

  • INFO

    The statement the thread is executing, or NULL if it is not executing any statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if a CALL statement executes a stored procedure that is executing a SELECT statement, the INFO value shows the SELECT statement.

Notes

  • The PROCESSLIST table is a nonstandard INFORMATION_SCHEMA table.

  • Like the output from the SHOW PROCESSLIST statement, the PROCESSLIST table shows information only about your own threads, unless you have the PROCESS privilege, in which case you will see information about other threads, too. As an anonymous user, you cannot see any rows at all.

  • If an SQL statement refers to the PROCESSLIST table, MySQL populates the entire table once, when statement execution begins, so there is read consistency during the statement. There is no read consistency for a multi-statement transaction.

Process information is also available from the mysqladmin processlist command, the SHOW PROCESSLIST statement, and the Performance Schema threads table (see Section 4.5.2, “ mysqladmin — Client for Administering a MySQL Server” , Section 13.7.6.29, “SHOW PROCESSLIST Syntax” , and Section 26.12.17.5, “The threads Table” ). In contrast to the INFORMATION_SCHEMA PROCESSLIST table and SHOW PROCESSLIST statement, which have negative performance consequences because they require a mutex, access to threads does not require a mutex and has minimal impact on server performance. The threads table also shows information about background threads, which the PROCESSLIST table and SHOW PROCESSLIST do not. This means that threads can be used to monitor activity the other thread information sources cannot.

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
SHOW FULL PROCESSLIST
                    

25.20 The INFORMATION_SCHEMA PROFILING Table

The PROFILING table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILE and SHOW PROFILES statements (see Section 13.7.6.30, “SHOW PROFILE Syntax” ). The table is empty unless the profiling session variable is set to 1.

Note

This table is deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Section 26.19.1, “Query Profiling Using Performance Schema” .

The PROFILING table has these columns:

  • QUERY_ID

    A numeric statement identifier.

  • SEQ

    A sequence number indicating the display order for rows with the same QUERY_ID value.

  • STATE

    The profiling state to which the row measurements apply.

  • DURATION

    How long statement execution remained in the given state, in seconds.

  • CPU_USER , CPU_SYSTEM

    User and system CPU use, in seconds.

  • CONTEXT_VOLUNTARY , CONTEXT_INVOLUNTARY

    How many voluntary and involuntary context switches occurred.

  • BLOCK_OPS_IN , BLOCK_OPS_OUT

    The number of block input and output operations.

  • MESSAGES_SENT , MESSAGES_RECEIVED

    The number of communication messages sent and received.

  • PAGE_FAULTS_MAJOR , PAGE_FAULTS_MINOR

    The number of major and minor page faults.

  • SWAPS

    How many swaps occurred.

  • SOURCE_FUNCTION , SOURCE_FILE , and SOURCE_LINE

    Information indicating where in the source code the profiled state executes.

Notes

  • The PROFILING table is a nonstandard INFORMATION_SCHEMA table.

Profiling information is also available from the SHOW PROFILE and SHOW PROFILES statements. See Section 13.7.6.30, “SHOW PROFILE Syntax” . For example, the following queries are equivalent:

SHOW PROFILE FOR QUERY 2;
SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;
                    

25.21 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table

The REFERENTIAL_CONSTRAINTS table provides information about foreign keys.

The REFERENTIAL_CONSTRAINTS table has these columns:

  • CONSTRAINT_CATALOG

    The name of the catalog to which the constraint belongs. This value is always def .

  • CONSTRAINT_SCHEMA

    The name of the schema (database) to which the constraint belongs.

  • CONSTRAINT_NAME

    The name of the constraint.

  • UNIQUE_CONSTRAINT_CATALOG

    The name of the catalog containing the unique constraint that the constraint references. This value is always def .

  • UNIQUE_CONSTRAINT_SCHEMA

    The name of the schema containing the unique constraint that the constraint references.

  • UNIQUE_CONSTRAINT_NAME

    The name of the unique constraint that the constraint references.

  • MATCH_OPTION

    The value of the constraint MATCH attribute. The only valid value at this time is NONE .

  • UPDATE_RULE

    The value of the constraint ON UPDATE attribute. The possible values are CASCADE , SET NULL , SET DEFAULT , RESTRICT , NO ACTION .

  • DELETE_RULE

    The value of the constraint ON DELETE attribute. The possible values are CASCADE , SET NULL , SET DEFAULT , RESTRICT , NO ACTION .

  • TABLE_NAME

    The name of the table. This value is the same as in the TABLE_CONSTRAINTS table.

  • REFERENCED_TABLE_NAME

    The name of the table referenced by the constraint.

25.22 The INFORMATION_SCHEMA RESOURCE_GROUPS Table

The RESOURCE_GROUPS table provides access to information about resource groups. For general discussion of the resource group capability, see Section 8.12.5, “Resource Groups” .

You can see information only for columns for which you have some privilege.

The RESOURCE_GROUPS table has these columns:

  • RESOURCE_GROUP_NAME

    The name of the resource group.

  • RESOURCE_GROUP_TYPE

    The resource group type, either SYSTEM or USER .

  • RESOURCE_GROUP_ENABLED

    Whether the resource group is enabled (1) or disabled (0);

  • VCPU_IDS

    The CPU affinity; that is, the set of virtual CPUs that the resource group can use. The value is a list of comma-separated CPU numbers or ranges.

  • THREAD_PRIORITY

    The priority for threads assigned to the resource group. The priority ranges from -20 (highest priority) to 19 (lowest priority). System resource groups have a priority that ranges from -20 to 0. User resource groups have a priority that ranges from 0 to 19.

25.23 The INFORMATION_SCHEMA ROUTINES Table

The ROUTINES table provides information about stored routines (stored procedures and stored functions). The ROUTINES table does not include built-in SQL functions or user-defined functions (UDFs).

The ROUTINES table has these columns:

  • SPECIFIC_NAME

    The name of the routine.

  • ROUTINE_CATALOG

    The name of the catalog to which the routine belongs. This value is always def .

  • ROUTINE_SCHEMA

    The name of the schema (database) to which the routine belongs.

  • ROUTINE_NAME

    The name of the routine.

  • ROUTINE_TYPE

    PROCEDURE for stored procedures, FUNCTION for stored functions.

  • DATA_TYPE

    If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.

    The DATA_TYPE value is the type name only with no other information. The DTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.

  • CHARACTER_MAXIMUM_LENGTH

    For stored function string return values, the maximum length in characters. If the routine is a stored procedure, this value is NULL .

  • CHARACTER_OCTET_LENGTH

    For stored function string return values, the maximum length in bytes. If the routine is a stored procedure, this value is NULL .

  • NUMERIC_PRECISION

    For stored function numeric return values, the numeric precision. If the routine is a stored procedure, this value is NULL .

  • NUMERIC_SCALE

    For stored function numeric return values, the numeric scale. If the routine is a stored procedure, this value is NULL .

  • DATETIME_PRECISION

    For stored function temporal return values, the fractional seconds precision. If the routine is a stored procedure, this value is NULL .

  • CHARACTER_SET_NAME

    For stored function character string return values, the character set name. If the routine is a stored procedure, this value is NULL .

  • COLLATION_NAME

    For stored function character string return values, the collation name. If the routine is a stored procedure, this value is NULL .

  • DTD_IDENTIFIER

    If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.

    The DATA_TYPE value is the type name only with no other information. The DTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.

  • ROUTINE_BODY

    The language used for the routine definition. This value is always SQL .

  • ROUTINE_DEFINITION

    The text of the SQL statement executed by the routine.

  • EXTERNAL_NAME

    This value is always NULL .

  • EXTERNAL_LANGUAGE

    The language of the stored routine. The value is read from the external_language column of the mysql.routines data dictionary table.

  • PARAMETER_STYLE

    This value is always SQL .

  • IS_DETERMINISTIC

    YES or NO , depending on whether the routine is defined with the DETERMINISTIC characteristic.

  • SQL_DATA_ACCESS

    The data access characteristic for the routine. The value is one of CONTAINS SQL , NO SQL , READS SQL DATA , or MODIFIES SQL DATA .

  • SQL_PATH

    This value is always NULL .

  • SECURITY_TYPE

    The routine SQL SECURITY characteristic. The value is one of DEFINER or INVOKER .

  • CREATED

    The date and time when the routine was created. This is a TIMESTAMP value.

  • LAST_ALTERED

    The date and time when the routine was last modified. This is a TIMESTAMP value. If the routine has not been modified since its creation, this value is the same as the CREATED value.

  • SQL_MODE

    The SQL mode in effect when the routine was created or altered, and under which the routine executes. For the permitted values, see Section 5.1.11, “Server SQL Modes” .

  • ROUTINE_COMMENT

    The text of the comment, if the routine has one. If not, this value is empty.

  • DEFINER

    The account of the user who created the routine, in ' user_name '@' host_name ' format.

  • CHARACTER_SET_CLIENT

    The session value of the character_set_client system variable when the routine was created.

  • COLLATION_CONNECTION

    The session value of the collation_connection system variable when the routine was created.

  • DATABASE_COLLATION

    The collation of the database with which the routine is associated.

Notes

  • Information about stored function return values is also available in the PARAMETERS table. The return value row for a stored function can be identified as the row that has an ORDINAL_POSITION value of 0.

25.24 The INFORMATION_SCHEMA SCHEMATA Table

A schema is a database, so the SCHEMATA table provides information about databases.

The SCHEMATA table has these columns:

  • CATALOG_NAME

    The name of the catalog to which the schema belongs. This value is always def .

  • SCHEMA_NAME

    The name of the schema.

  • DEFAULT_CHARACTER_SET_NAME

    The schema default character set.

  • DEFAULT_COLLATION_NAME

    The schema default collation.

  • SQL_PATH

    This value is always NULL .

Schema names are also available from the SHOW DATABASES statement. See Section 13.7.6.14, “SHOW DATABASES Syntax” . The following statements are equivalent:

SELECT SCHEMA_NAME AS `Database`
  FROM INFORMATION_SCHEMA.SCHEMATA
  [WHERE SCHEMA_NAME LIKE 'wild']
SHOW DATABASES
  [LIKE 'wild']
                    

25.25 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table

The SCHEMA_PRIVILEGES table provides information about schema (database) privileges. It takes its values from the mysql.db system table.

The SCHEMA_PRIVILEGES table has these columns:

  • GRANTEE

    The name of the account to which the privilege is granted, in ' user_name '@' host_name ' format.

  • TABLE_CATALOG

    The name of the catalog to which the schema belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema.

  • PRIVILEGE_TYPE

    The privilege granted. The value can be any privilege that can be granted at the schema level; see Section 13.7.1.6, “GRANT Syntax” . Each row lists a single privilege, so there is one row per schema privilege held by the grantee.

  • IS_GRANTABLE

    YES if the user has the GRANT OPTION privilege, NO otherwise. The output does not list GRANT OPTION as a separate row with PRIVILEGE_TYPE='GRANT OPTION' .

Notes

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
SHOW GRANTS ...
                    

25.26 The INFORMATION_SCHEMA STATISTICS Table

The STATISTICS table provides information about table indexes.

Columns in STATISTICS that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE . To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry=0 . 更多信息,请见 Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries” .

Note

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB . For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0 .

The STATISTICS table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the table containing the index belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table containing the index belongs.

  • TABLE_NAME

    The name of the table containing the index.

  • NON_UNIQUE

    0 if the index cannot contain duplicates, 1 if it can.

  • INDEX_SCHEMA

    The name of the schema (database) to which the index belongs.

  • INDEX_NAME

    The name of the index. If the index is the primary key, the name is always PRIMARY .

  • SEQ_IN_INDEX

    The column sequence number in the index, starting with 1.

  • COLUMN_NAME

    The column name. See also the description for the EXPRESSION column.

  • COLLATION

    How the column is sorted in the index. This can have values A (ascending), D (descending), or NULL (not sorted).

  • CARDINALITY

    An estimate of the number of unique values in the index. To update this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a .

    CARDINALITY is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

  • SUB_PART

    The index prefix. That is, the number of indexed characters if the column is only partly indexed, NULL if the entire column is indexed.

    Note

    Prefix limits are measured in bytes. However, prefix lengths for index specifications in CREATE TABLE , ALTER TABLE , and CREATE INDEX statements are interpreted as number of characters for nonbinary string types ( CHAR , VARCHAR , TEXT ) and number of bytes for binary string types ( BINARY , VARBINARY , BLOB ). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.

    For additional information about index prefixes, see Section 8.3.5, “Column Indexes” , and Section 13.1.15, “CREATE INDEX Syntax” .

  • PACKED

    Indicates how the key is packed. NULL if it is not.

  • NULLABLE

    Contains YES if the column may contain NULL values and '' if not.

  • INDEX_TYPE

    The index method used ( BTREE , FULLTEXT , HASH , RTREE ).

  • COMMENT

    Information about the index not described in its own column, such as disabled if the index is disabled.

  • INDEX_COMMENT

    Any comment provided for the index with a COMMENT attribute when the index was created.

  • IS_VISIBLE

    Whether the index is visible to the optimizer. See Section 8.3.12, “Invisible Indexes” .

  • EXPRESSION

    MySQL 8.0.13 and higher supports functional key parts (see Functional Key Parts ), which affects both the COLUMN_NAME and EXPRESSION columns:

    • For a nonfunctional key part, COLUMN_NAME indicates the column indexed by the key part and EXPRESSION is NULL .

    • For a functional key part, COLUMN_NAME column is NULL and EXPRESSION indicates the expression for the key part.

Notes

  • There is no standard INFORMATION_SCHEMA table for indexes. The MySQL column list is similar to what SQL Server 2000 returns for sp_statistics , except that QUALIFIER and OWNER are replaced with CATALOG and SCHEMA , respectively.

Information about table indexes is also available from the SHOW INDEX statement. See Section 13.7.6.22, “SHOW INDEX Syntax” . The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  AND table_schema = 'db_name'
SHOW INDEX
  FROM tbl_name
  FROM db_name
                    

25.27 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table

The ST_GEOMETRY_COLUMNS table provides information about table columns that store spatial data. This table is based on the SQL/MM (ISO/IEC 13249-3) standard, with extensions as noted. MySQL implements ST_GEOMETRY_COLUMNS as a view on the INFORMATION_SCHEMA COLUMNS table.

The ST_GEOMETRY_COLUMNS table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the table containing the column belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table containing the column belongs.

  • TABLE_NAME

    The name of the table containing the column.

  • COLUMN_NAME

    The name of the column.

  • SRS_NAME

    The spatial reference system (SRS) name.

  • SRS_ID

    The spatial reference system ID (SRID).

  • GEOMETRY_TYPE_NAME

    The column data type. Permitted values are: geometry , point , linestring , polygon , multipoint , multilinestring , multipolygon , geometrycollection . This column is a MySQL extension to the standard.

25.28 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table

The ST_SPATIAL_REFERENCE_SYSTEMS table provides information about available spatial reference systems for spatial data. This table is based on the SQL/MM (ISO/IEC 13249-3) standard.

Entries in the ST_SPATIAL_REFERENCE_SYSTEMS table are based on the European Petroleum Survey Group (EPSG) data set, except for SRID 0, which corresponds to a special SRS used in MySQL that represents an infinite flat Cartesian plane with no units assigned to its axes. For additional information about SRSs, see Section 11.5.5, “Spatial Reference System Support” .

The ST_SPATIAL_REFERENCE_SYSTEMS table has these columns:

  • SRS_NAME

    The spatial reference system name. This value is unique.

  • SRS_ID

    The spatial reference system numeric ID. This value is unique.

    SRS_ID values represent the same kind of values passed as the SRID argument to spatial functions. SRID 0 (the unitless Cartesian plane) is special. It is always a legal spatial reference system ID and can be used in any computations on spatial data that depend on SRID values.

  • ORGANIZATION

    The name of the organization that defined the coordinate system on which the spatial reference system is based.

  • ORGANIZATION_COORDSYS_ID

    The numeric ID given to the spatial reference system by the organization that defined it.

  • DEFINITION

    The spatial reference system definition. DEFINITION values are WKT values, represented as specified in the Open Geospatial Consortium document OGC 12-063r5 .

    SRS definition parsing occurs on demand when definitions are needed by GIS functions. Parsed definitions are cached in the data dictionary cache so that parsing overhead is not incurred for every statement that needs SRS information.

  • DESCRIPTION

    The spatial reference system description.

Notes

  • The SRS_NAME , ORGANIZATION , ORGANIZATION_COORDSYS_ID , and DESCRIPTION columns contain information that may be of interest to users, but they are not used by MySQL.

Example

mysql> SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS
       WHERE SRS_ID = 4326\G
*************************** 1. row ***************************
                SRS_NAME: WGS 84
                  SRS_ID: 4326
            ORGANIZATION: EPSG
ORGANIZATION_COORDSYS_ID: 4326
              DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 1984",
                          SPHEROID["WGS 84",6378137,298.257223563,
                          AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],
                          PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
                          UNIT["degree",0.017453292519943278,
                          AUTHORITY["EPSG","9122"]],
                          AXIS["Lat",NORTH],AXIS["Long",EAST],
                          AUTHORITY["EPSG","4326"]]
             DESCRIPTION:
                    

This entry describes the SRS used for GPS systems. It has a name ( SRS_NAME ) of WGS 84 and an ID ( SRS_ID ) of 4326, which is the ID used by the European Petroleum Survey Group (EPSG).

The DEFINITION values for projected and geographic SRSs begin with PROJCS and GEOGCS , respectively. The definition for SRID 0 is special and has an empty DEFINITION value. The following query determines how many entries in the ST_SPATIAL_REFERENCE_SYSTEMS table correspond to projected, geographic, and other SRSs, based on DEFINITION values:

mysql> SELECT
         COUNT(*),
         CASE LEFT(DEFINITION, 6)
           WHEN 'PROJCS' THEN 'Projected'
           WHEN 'GEOGCS' THEN 'Geographic'
           ELSE 'Other'
         END AS SRS_TYPE
       FROM ST_SPATIAL_REFERENCE_SYSTEMS
       GROUP BY SRS_TYPE;
+----------+------------+
| COUNT(*) | SRS_TYPE   |
+----------+------------+
|        1 | Other      |
|     4668 | Projected  |
|      483 | Geographic |
+----------+------------+
                    

To enable manipulation of SRS entries stored in the data dictionary, MySQL provides these SQL statements:

25.29 The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table

The ST_UNITS_OF_MEASURE table (available as of MySQL 8.0.14) provides information about acceptable units for the ST_Distance() function.

The ST_UNITS_OF_MEASURE table has these columns:

  • UNIT_NAME

    The name of the unit.

  • UNIT_TYPE

    The unit type; for example, LINEAR .

  • CONVERSION_FACTOR

    A conversion factor used for internal calculations.

  • DESCRIPTION

    A description of the unit.

25.30 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE . To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0 . For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries” .

Note

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB . For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0 .

The TABLES table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the table belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table belongs.

  • TABLE_NAME

    The name of the table.

  • TABLE_TYPE

    BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.

    The TABLES table does not list TEMPORARY tables.

  • ENGINE

    The storage engine for the table. See Chapter 15, The InnoDB Storage Engine , and Chapter 16, Alternative Storage Engines .

    For partitioned tables, ENGINE shows the name of the storage engine used by all partitions.

  • VERSION

    This column is unused. With the removal of .frm files in MySQL 8.0, this column now reports a hardcoded value of 10 , which is the last .frm file version used in MySQL 5.7.

  • ROW_FORMAT

    The row-storage format ( Fixed , Dynamic , Compressed , Redundant , Compact ). For MyISAM tables, Dynamic corresponds to what myisamchk -dvv reports as Packed .

  • TABLE_ROWS

    The number of rows. Some storage engines, such as MyISAM , store the exact count. For other storage engines, such as InnoDB , this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

    TABLE_ROWS is NULL for INFORMATION_SCHEMA tables.

    For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

  • AVG_ROW_LENGTH

    The average row length.

  • DATA_LENGTH

    For MyISAM , DATA_LENGTH is the length of the data file, in bytes.

    For InnoDB , DATA_LENGTH is the approximate amount of memory allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.

    Refer to the notes at the end of this section for information regarding other storage engines.

  • MAX_DATA_LENGTH

    For MyISAM , MAX_DATA_LENGTH is maximum length of the data file. This is the total number of bytes of data that can be stored in the table, given the data pointer size used.

    Unused for InnoDB .

    Refer to the notes at the end of this section for information regarding other storage engines.

  • INDEX_LENGTH

    For MyISAM , INDEX_LENGTH is the length of the index file, in bytes.

    For InnoDB , INDEX_LENGTH is the approximate amount of memory allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.

    Refer to the notes at the end of this section for information regarding other storage engines.

  • DATA_FREE

    The number of allocated but unused bytes.

    InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table. Free space means the number of bytes in completely free extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated.

    For NDB Cluster, DATA_FREE shows the space allocated on disk for, but not used by, a Disk Data table or fragment on disk. (In-memory data resource usage is reported by the DATA_LENGTH column.)

    For partitioned tables, this value is only an estimate and may not be absolutely correct. A more accurate method of obtaining this information in such cases is to query the INFORMATION_SCHEMA PARTITIONS table, as shown in this example:

    SELECT SUM(DATA_FREE)
        FROM  INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = 'mydb'
        AND   TABLE_NAME   = 'mytable';
                                    

    更多信息,请见 Section 25.17, “The INFORMATION_SCHEMA PARTITIONS Table” .

  • AUTO_INCREMENT

    The next AUTO_INCREMENT value.

  • CREATE_TIME

    When the table was created.

  • UPDATE_TIME

    When the data file was last updated. For some storage engines, this value is NULL . For example, InnoDB stores multiple tables in its system tablespace and the data file timestamp does not apply. Even with file-per-table mode with each InnoDB table in a separate .ibd file, change buffering can delay the write to the data file, so the file modification time is different from the time of the last insert, update, or delete. For MyISAM , the data file timestamp is used; however, on Windows the timestamp is not updated by updates, so the value is inaccurate.

    UPDATE_TIME displays a timestamp value for the last UPDATE , INSERT , or DELETE performed on InnoDB tables that are not partitioned. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

  • CHECK_TIME

    When the table was last checked. Not all storage engines update this time, in which case, the value is always NULL .

    For partitioned InnoDB tables, CHECK_TIME is always NULL .

  • TABLE_COLLATION

    The table default collation. The output does not explicitly list the table default character set, but the collation name begins with the character set name.

  • CHECKSUM

    The live checksum value, if any.

  • CREATE_OPTIONS

    Extra options used with CREATE TABLE . The original options specified when CREATE TABLE was executed are retained. The information reported may differ from current table settings and options.

    For InnoDB tables, the actual ROW_FORMAT and KEY_BLOCK_SIZE options are shown. Prior to MySQL 8.0, CREATE_OPTIONS shows the originally supplied ROW_FORMAT and KEY_BLOCK_SIZE . For more information, see Section 13.1.20, “CREATE TABLE Syntax” .

    CREATE_OPTIONS shows partitioned if the table is partitioned. It also shows the ENCRYPTION option if it was used when creating or altering a file-per-table tablespace. The CREATE_OPTIONS column does not show the encryption option specified when creating or altering a general tablespace. To identify encrypted file-per-table and general tablespaces, query the INNODB_TABLESPACES ENCRYPTION column.

  • TABLE_COMMENT

    The comment used when creating the table (or information as to why MySQL could not access the table information).

Notes

  • For NDB tables, the output of this statement shows appropriate values for the AVG_ROW_LENGTH and DATA_LENGTH columns, with the exception that BLOB columns are not taken into account.

  • For NDB tables, DATA_LENGTH includes data stored in main memory only; the MAX_DATA_LENGTH and DATA_FREE columns apply to Disk Data.

  • For NDB Cluster Disk Data tables, MAX_DATA_LENGTH shows the space allocated for the disk part of a Disk Data table or fragment. (In-memory data resource usage is reported by the DATA_LENGTH column.)

  • For MEMORY tables, the DATA_LENGTH , MAX_DATA_LENGTH , and INDEX_LENGTH values approximate the actual amount of allocated memory. The allocation algorithm reserves memory in large amounts to reduce the number of allocation operations.

  • For views, most TABLES columns are 0 or NULL except that TABLE_NAME indicates the view name, CREATE_TIME indicates the creation time, and TABLE_COMMENT says VIEW .

Table information is also available from the SHOW TABLE STATUS and SHOW TABLES statements. See Section 13.7.6.36, “SHOW TABLE STATUS Syntax” , and Section 13.7.6.37, “SHOW TABLES Syntax” . The following statements are equivalent:

SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']
SHOW TABLE STATUS
  FROM db_name
  [LIKE 'wild']
                    

The following statements are equivalent:

SELECT
  TABLE_NAME, TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']
SHOW FULL TABLES
  FROM db_name
  [LIKE 'wild']
                    

25.31 The INFORMATION_SCHEMA TABLESPACES Table

The TABLESPACES table provides information about active MySQL Cluster tablespaces.

The TABLESPACES table has these columns:

  • TABLESPACE_NAME

    The name of the tablespace.

  • ENGINE

    The name of the storage engine that uses the tablespace.

  • TABLESPACE_TYPE

    The tablespace type.

  • LOGFILE_GROUP_NAME

    The name of the logfile group assigned to the tablespace.

  • EXTENT_SIZE

    The size in bytes of the extents used by files that belong to the tablespace.

  • AUTOEXTEND_SIZE

    Unused.

  • MAXIMUM_SIZE

    Unused.

  • NODEGROUP_ID

    Unused.

  • TABLESPACE_COMMENT

    Unused.

Notes

  • The TABLESPACES table is a nonstandard INFORMATION_SCHEMA table.

  • The TABLESPACES table does not provide information about InnoDB tablespaces. For InnoDB tablespace metadata, see the INFORMATION_SCHEMA INNODB_TABLESPACES and INNODB_DATAFILES tables. The FILES table also provides metadata for InnoDB tablespaces.

25.32 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

The TABLE_CONSTRAINTS table describes which tables have constraints.

The TABLE_CONSTRAINTS table has these columns:

  • CONSTRAINT_CATALOG

    The name of the catalog to which the constraint belongs. This value is always def .

  • CONSTRAINT_SCHEMA

    The name of the schema (database) to which the constraint belongs.

  • TABLE_SCHEMA

    The name of the schema (database) to which the table belongs.

  • TABLE_NAME

    The name of the table.

  • The CONSTRAINT_TYPE

    The type of constraint. The value can be UNIQUE , PRIMARY KEY , FOREIGN KEY , or (as of MySQL 8.0.16) CHECK . This is a CHAR (not ENUM ) column.

    The UNIQUE and PRIMARY KEY information is about the same as what you get from the Key_name column in the output from SHOW INDEX when the Non_unique column is 0 .

  • ENFORCED

    For CHECK constraints, the value is YES or NO to indicate whether the constraint is enforced. For other constraints, the value is always YES .

    This column was added in MySQL 8.0.16.

25.33 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table

The TABLE_PRIVILEGES table provides information about table privileges. It takes its values from the mysql.tables_priv system table.

The TABLE_PRIVILEGES table has these columns:

  • GRANTEE

    The name of the account to which the privilege is granted, in ' user_name '@' host_name ' format.

  • TABLE_CATALOG

    The name of the catalog to which the table belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table belongs.

  • TABLE_NAME

    The name of the table.

  • PRIVILEGE_TYPE

    The privilege granted. The value can be any privilege that can be granted at the table level; see Section 13.7.1.6, “GRANT Syntax” . Each row lists a single privilege, so there is one row per table privilege held by the grantee.

  • IS_GRANTABLE

    YES if the user has the GRANT OPTION privilege, NO otherwise. The output does not list GRANT OPTION as a separate row with PRIVILEGE_TYPE='GRANT OPTION' .

Notes

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SHOW GRANTS ...
                    

25.34 The INFORMATION_SCHEMA TRIGGERS Table

The TRIGGERS table provides information about triggers. To see information about a table's triggers, you must have the TRIGGER privilege for the table.

The TRIGGERS table has these columns:

  • TRIGGER_CATALOG

    The name of the catalog to which the trigger belongs. This value is always def .

  • TRIGGER_SCHEMA

    The name of the schema (database) to which the trigger belongs.

  • TRIGGER_NAME

    The name of the trigger.

  • EVENT_MANIPULATION

    The trigger event. This is the type of operation on the associated table for which the trigger activates. The value is INSERT (a row was inserted), DELETE (a row was deleted), or UPDATE (a row was modified).

  • EVENT_OBJECT_CATALOG , EVENT_OBJECT_SCHEMA , and EVENT_OBJECT_TABLE

    As noted in Section 24.3, “Using Triggers” , every trigger is associated with exactly one table. These columns indicate the catalog and schema (database) in which this table occurs, and the table name, respectively. The EVENT_OBJECT_CATALOG value is always def .

  • ACTION_ORDER

    The ordinal position of the trigger's action within the list of triggers on the same table with the same EVENT_MANIPULATION and ACTION_TIMING values.

  • ACTION_CONDITION

    This value is always NULL .

  • ACTION_STATEMENT

    The trigger body; that is, the statement executed when the trigger activates. This text uses UTF-8 encoding.

  • ACTION_ORIENTATION

    This value is always ROW .

  • ACTION_TIMING

    Whether the trigger activates before or after the triggering event. The value is BEFORE or AFTER .

  • ACTION_REFERENCE_OLD_TABLE

    This value is always NULL .

  • ACTION_REFERENCE_NEW_TABLE

    This value is always NULL .

  • ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW

    The old and new column identifiers, respectively. The ACTION_REFERENCE_OLD_ROW value is always OLD and the ACTION_REFERENCE_NEW_ROW value is always NEW .

  • CREATED

    The date and time when the trigger was created. This is a TIMESTAMP(2) value (with a fractional part in hundredths of seconds) for triggers.

  • SQL_MODE

    The SQL mode in effect when the trigger was created, and under which the trigger executes. For the permitted values, see Section 5.1.11, “Server SQL Modes” .

  • DEFINER

    The account of the user who created the trigger, in ' user_name '@' host_name ' format.

  • CHARACTER_SET_CLIENT

    The session value of the character_set_client system variable when the trigger was created.

  • COLLATION_CONNECTION

    The session value of the collation_connection system variable when the trigger was created.

  • DATABASE_COLLATION

    The collation of the database with which the trigger is associated.

Example

The following example uses the ins_sum trigger defined in Section 24.3, “Using Triggers” :

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
       WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ins_sum'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ins_sum
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: account
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SET @sum = @sum + NEW.amount
        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-08-08 10:10:12.61
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                            NO_ZERO_IN_DATE,NO_ZERO_DATE,
                            ERROR_FOR_DIVISION_BY_ZERO,
                            NO_ENGINE_SUBSTITUTION
                   DEFINER: me@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
                    

Trigger information is also available from the SHOW TRIGGERS statement. See Section 13.7.6.38, “SHOW TRIGGERS Syntax” .

25.35 The INFORMATION_SCHEMA USER_PRIVILEGES Table

The USER_PRIVILEGES table provides information about global privileges. It takes its values from the mysql.user system table.

The USER_PRIVILEGES table has these columns:

  • GRANTEE

    The name of the account to which the privilege is granted, in ' user_name '@' host_name ' format.

  • TABLE_CATALOG

    The name of the catalog. This value is always def .

  • PRIVILEGE_TYPE

    The privilege granted. The value can be any privilege that can be granted at the global level; see Section 13.7.1.6, “GRANT Syntax” . Each row lists a single privilege, so there is one row per global privilege held by the grantee.

  • IS_GRANTABLE

    YES if the user has the GRANT OPTION privilege, NO otherwise. The output does not list GRANT OPTION as a separate row with PRIVILEGE_TYPE='GRANT OPTION' .

Notes

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.USER_PRIVILEGES
SHOW GRANTS ...
                    

25.36 The INFORMATION_SCHEMA VIEWS Table

The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.

The VIEWS table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the view belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the view belongs.

  • TABLE_NAME

    The name of the view.

  • VIEW_DEFINITION

    The SELECT statement that provides the definition of the view. This column has most of what you see in the Create Table column that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION . Suppose that the original statement was:

    CREATE VIEW v AS
      SELECT s2,s1 FROM t
      WHERE s1 > 5
      ORDER BY s1
      WITH CHECK OPTION;
                                    

    Then the view definition looks like this:

    SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
                                    
  • CHECK_OPTION

    The value of the CHECK_OPTION attribute. The value is one of NONE , CASCADE , or LOCAL .

  • IS_UPDATABLE

    MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the VIEWS table displays the status of this flag. It means that the server always knows whether a view is updatable.

    If a view is not updatable, statements such UPDATE , DELETE , and INSERT are illegal and are rejected. (Even if a view is updatable, it might not be possible to insert into it; for details, refer to Section 24.5.3, “Updatable and Insertable Views” .)

  • DEFINER

    The account of the user who created the view, in ' user_name '@' host_name ' format.

  • SECURITY_TYPE

    The view SQL SECURITY characteristic. The value is one of DEFINER or INVOKER .

  • CHARACTER_SET_CLIENT

    The session value of the character_set_client system variable when the view was created.

  • COLLATION_CONNECTION

    The session value of the collation_connection system variable when the view was created.

Notes

MySQL permits different sql_mode settings to tell the server the type of SQL syntax to support. For example, you might use the ANSI SQL mode to ensure MySQL correctly interprets the standard SQL concatenation operator, the double bar ( || ), in your queries. If you then create a view that concatenates items, you might worry that changing the sql_mode setting to a value different from ANSI could cause the view to become invalid. But this is not the case. No matter how you write out a view definition, MySQL always stores it the same way, in a canonical form. Here is an example that shows how the server changes a double bar concatenation operator to a CONCAT() function:

mysql> SET sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
       WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
+----------------------------------+
| VIEW_DEFINITION                  |
+----------------------------------+
| select concat('a','b') AS `col1` |
+----------------------------------+
1 row in set (0.00 sec)
                    

The advantage of storing a view definition in canonical form is that changes made later to the value of sql_mode do not affect the results from the view. However, an additional consequence is that comments prior to SELECT are stripped from the definition by the server.

25.37 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table

The VIEW_ROUTINE_USAGE table (available as of MySQL 8.0.13) provides access to information about stored functions used in view definitions. The table does not list information about built-in SQL functions or user-defined functions (UDFs) used in the definitions.

You can see information only for views for which you have some privilege, and only for functions for which you have some privilege.

The VIEW_ROUTINE_USAGE table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the view belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the view belongs.

  • TABLE_NAME

    The name of the view.

  • SPECIFIC_CATALOG

    The name of the catalog to which the function used in the view definition belongs. This value is always def .

  • SPECIFIC_SCHEMA

    The name of the schema (database) to which the function used in the view definition belongs.

  • SPECIFIC_NAME

    The name of the function used in the view definition.

25.38 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table

The VIEW_TABLE_USAGE table (available as of MySQL 8.0.13) provides access to information about tables and views used in view definitions.

You can see information only for views for which you have some privilege, and only for tables for which you have some privilege.

The VIEW_TABLE_USAGE table has these columns:

  • VIEW_CATALOG

    The name of the catalog to which the view belongs. This value is always def .

  • VIEW_SCHEMA

    The name of the schema (database) to which the view belongs.

  • VIEW_NAME

    The name of the view.

  • TABLE_CATALOG

    The name of the catalog to which the table or view used in the view definition belongs. This value is always def .

  • TABLE_SCHEMA

    The name of the schema (database) to which the table or view used in the view definition belongs.

  • TABLE_NAME

    The name of the table or view used in the view definition.

25.39 INFORMATION_SCHEMA InnoDB Tables

25.39.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
25.39.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
25.39.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
25.39.4 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table
25.39.5 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
25.39.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
25.39.7 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
25.39.8 The INFORMATION_SCHEMA INNODB_COLUMNS Table
25.39.9 The INFORMATION_SCHEMA INNODB_DATAFILES Table
25.39.10 The INFORMATION_SCHEMA INNODB_FIELDS Table
25.39.11 The INFORMATION_SCHEMA INNODB_FOREIGN Table
25.39.12 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table
25.39.13 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
25.39.14 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
25.39.15 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
25.39.16 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
25.39.17 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
25.39.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
25.39.19 The INFORMATION_SCHEMA INNODB_INDEXES Table
25.39.20 The INFORMATION_SCHEMA INNODB_LOCKS Table
25.39.21 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
25.39.22 The INFORMATION_SCHEMA INNODB_METRICS Table
25.39.23 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table
25.39.24 The INFORMATION_SCHEMA INNODB_TABLES Table
25.39.25 The INFORMATION_SCHEMA INNODB_TABLESPACES Table
25.39.26 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table
25.39.27 The INFORMATION_SCHEMA INNODB_TABLESTATS View
25.39.28 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
25.39.29 The INFORMATION_SCHEMA INNODB_TRX Table
25.39.30 The INFORMATION_SCHEMA INNODB_VIRTUAL Table

This section provides table definitions for InnoDB INFORMATION_SCHEMA tables. For related information and examples, see Section 15.14, “InnoDB INFORMATION_SCHEMA Tables” .

InnoDB INFORMATION_SCHEMA tables can be used to monitor ongoing InnoDB activity, to detect inefficiencies before they turn into issues, or to troubleshoot performance and capacity issues. As your database becomes bigger and busier, running up against the limits of your hardware capacity, you monitor and tune these aspects to keep the database running smoothly.

25.39.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

The INNODB_BUFFER_PAGE table provides information about each page in the InnoDB buffer pool .

For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables” .

Warning

Querying the INNODB_BUFFER_PAGE table can affect performance. Do not query this table on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.

The INNODB_BUFFER_PAGE table has these columns:

  • POOL_ID

    The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.

  • BLOCK_ID

    The buffer pool block ID.

  • SPACE

    The tablespace ID; the same value as INNODB_TABLES.SPACE .

  • PAGE_NUMBER

    The page number.

  • PAGE_TYPE

    The page type. The following table shows the permitted values.

    Table 25.1 INNODB_BUFFER_PAGE.PAGE_TYPE Values

    Page Type 描述
    ALLOCATED Freshly allocated page
    BLOB Uncompressed BLOB page
    COMPRESSED_BLOB2 Subsequent comp BLOB page
    COMPRESSED_BLOB First compressed BLOB page
    ENCRYPTED_RTREE Encrypted R-tree
    EXTENT_DESCRIPTOR Extent descriptor page
    FILE_SPACE_HEADER File space header
    FIL_PAGE_TYPE_UNUSED Unused
    IBUF_BITMAP Insert buffer bitmap
    IBUF_FREE_LIST Insert buffer free list
    IBUF_INDEX Insert buffer index
    INDEX B-tree node
    INODE Index node
    LOB_DATA Uncompressed LOB data
    LOB_FIRST First page of uncompressed LOB
    LOB_INDEX Uncompressed LOB index
    PAGE_IO_COMPRESSED Compressed page
    PAGE_IO_COMPRESSED_ENCRYPTED Compressed and encrypted page
    PAGE_IO_ENCRYPTED Encrypted page
    RSEG_ARRAY Rollback segment array
    RTREE_INDEX R-tree index
    SDI_BLOB Uncompressed SDI BLOB
    SDI_COMPRESSED_BLOB Compressed SDI BLOB
    SDI_INDEX SDI index
    SYSTEM System page
    TRX_SYSTEM Transaction system data
    UNDO_LOG Undo log page
    UNKNOWN Unknown
    ZLOB_DATA Compressed LOB data
    ZLOB_FIRST First page of compressed LOB
    ZLOB_FRAG Compressed LOB fragment
    ZLOB_FRAG_ENTRY Compressed LOB fragment index
    ZLOB_INDEX Compressed LOB index

  • FLUSH_TYPE

    The flush type.

  • FIX_COUNT

    The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.

  • IS_HASHED

    Whether a hash index has been built on this page.

  • NEWEST_MODIFICATION

    The Log Sequence Number of the youngest modification.

  • OLDEST_MODIFICATION

    The Log Sequence Number of the oldest modification.

  • ACCESS_TIME

    An abstract number used to judge the first access time of the page.

  • TABLE_NAME

    The name of the table the page belongs to. This column is applicable only to pages with a PAGE_TYPE value of INDEX .

  • INDEX_NAME

    The name of the index the page belongs to. This can be the name of a clustered index or a secondary index. This column is applicable only to pages with a PAGE_TYPE value of INDEX .

  • NUMBER_RECORDS

    The number of records within the page.

  • DATA_SIZE

    The sum of the sizes of the records. This column is applicable only to pages with a PAGE_TYPE value of INDEX .

  • COMPRESSED_SIZE

    The compressed page size. NULL for pages that are not compressed.

  • PAGE_STATE

    The page state. The following table shows the permitted values.

    Table 25.2 INNODB_BUFFER_PAGE.PAGE_STATE Values

    Page State 描述
    FILE_PAGE A buffered file page
    MEMORY Contains a main memory object
    NOT_USED In the free list
    NULL Clean compressed pages, compressed pages in the flush list, pages used as buffer pool watch sentinels
    READY_FOR_USE A free page
    REMOVE_HASH Hash index should be removed before placing in the free list

  • IO_FIX

    Whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.

  • IS_OLD

    Whether the block is in the sublist of old blocks in the LRU list.

  • FREE_PAGE_CLOCK

    The value of the freed_page_clock counter when the block was the last placed at the head of the LRU list. The freed_page_clock counter tracks the number of blocks removed from the end of the LRU list.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G
*************************** 1. row ***************************
            POOL_ID: 0
           BLOCK_ID: 0
              SPACE: 97
        PAGE_NUMBER: 2473
          PAGE_TYPE: INDEX
         FLUSH_TYPE: 1
          FIX_COUNT: 0
          IS_HASHED: YES
NEWEST_MODIFICATION: 733855581
OLDEST_MODIFICATION: 0
        ACCESS_TIME: 3378385672
         TABLE_NAME: `employees`.`salaries`
         INDEX_NAME: PRIMARY
     NUMBER_RECORDS: 468
          DATA_SIZE: 14976
    COMPRESSED_SIZE: 0
         PAGE_STATE: FILE_PAGE
             IO_FIX: IO_NONE
             IS_OLD: YES
    FREE_PAGE_CLOCK: 66
                        

Notes

  • This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

  • When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The INNODB_BUFFER_PAGE table reports information about these pages until they are evicted from the buffer pool. For more information about how the InnoDB manages buffer pool data, see Section 15.5.1, “Buffer Pool” .

25.39.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

The INNODB_BUFFER_PAGE_LRU table provides information about the pages in the InnoDB buffer pool ; in particular, how they are ordered in the LRU list that determines which pages to evict from the buffer pool when it becomes full.

The INNODB_BUFFER_PAGE_LRU table has the same columns as the INNODB_BUFFER_PAGE table, except that the INNODB_BUFFER_PAGE_LRU table has LRU_POSITION and COMPRESSED columns instead of BLOCK_ID and PAGE_STATE columns.

For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables” .

Warning

Querying the INNODB_BUFFER_PAGE_LRU table can affect performance. Do not query this table on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.

The INNODB_BUFFER_PAGE_LRU table has these columns:

  • POOL_ID

    The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.

  • LRU_POSITION

    The position of the page in the LRU list.

  • SPACE

    The tablespace ID; the same value as INNODB_TABLES.SPACE .

  • PAGE_NUMBER

    The page number.

  • PAGE_TYPE

    The page type. The following table shows the permitted values.

    Table 25.3 INNODB_BUFFER_PAGE_LRU.PAGE_TYPE Values

    Page Type 描述
    ALLOCATED Freshly allocated page
    BLOB Uncompressed BLOB page
    COMPRESSED_BLOB2 Subsequent comp BLOB page
    COMPRESSED_BLOB First compressed BLOB page
    ENCRYPTED_RTREE Encrypted R-tree
    EXTENT_DESCRIPTOR Extent descriptor page
    FILE_SPACE_HEADER File space header
    FIL_PAGE_TYPE_UNUSED Unused
    IBUF_BITMAP Insert buffer bitmap
    IBUF_FREE_LIST Insert buffer free list
    IBUF_INDEX Insert buffer index
    INDEX B-tree node
    INODE Index node
    LOB_DATA Uncompressed LOB data
    LOB_FIRST First page of uncompressed LOB
    LOB_INDEX Uncompressed LOB index
    PAGE_IO_COMPRESSED Compressed page
    PAGE_IO_COMPRESSED_ENCRYPTED Compressed and encrypted page
    PAGE_IO_ENCRYPTED Encrypted page
    RSEG_ARRAY Rollback segment array
    RTREE_INDEX R-tree index
    SDI_BLOB Uncompressed SDI BLOB
    SDI_COMPRESSED_BLOB Compressed SDI BLOB
    SDI_INDEX SDI index
    SYSTEM System page
    TRX_SYSTEM Transaction system data
    UNDO_LOG Undo log page
    UNKNOWN Unknown
    ZLOB_DATA Compressed LOB data
    ZLOB_FIRST First page of compressed LOB
    ZLOB_FRAG Compressed LOB fragment
    ZLOB_FRAG_ENTRY Compressed LOB fragment index
    ZLOB_INDEX Compressed LOB index

  • FLUSH_TYPE

    The flush type.

  • FIX_COUNT

    The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.

  • IS_HASHED

    Whether a hash index has been built on this page.

  • NEWEST_MODIFICATION

    The Log Sequence Number of the youngest modification.

  • OLDEST_MODIFICATION

    The Log Sequence Number of the oldest modification.

  • ACCESS_TIME

    An abstract number used to judge the first access time of the page.

  • TABLE_NAME

    The name of the table the page belongs to. This column is applicable only to pages with a PAGE_TYPE value of INDEX .

  • INDEX_NAME

    The name of the index the page belongs to. This can be the name of a clustered index or a secondary index. This column is applicable only to pages with a PAGE_TYPE value of INDEX .

  • NUMBER_RECORDS

    The number of records within the page.

  • DATA_SIZE

    The sum of the sizes of the records. This column is applicable only to pages with a PAGE_TYPE value of INDEX .

  • COMPRESSED_SIZE

    The compressed page size. NULL for pages that are not compressed.

  • COMPRESSED

    Whether the page is compressed.

  • IO_FIX

    Whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.

  • IS_OLD

    Whether the block is in the sublist of old blocks in the LRU list.

  • FREE_PAGE_CLOCK

    The value of the freed_page_clock counter when the block was the last placed at the head of the LRU list. The freed_page_clock counter tracks the number of blocks removed from the end of the LRU list.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G
*************************** 1. row ***************************
            POOL_ID: 0
       LRU_POSITION: 0
              SPACE: 97
        PAGE_NUMBER: 1984
          PAGE_TYPE: INDEX
         FLUSH_TYPE: 1
          FIX_COUNT: 0
          IS_HASHED: YES
NEWEST_MODIFICATION: 719490396
OLDEST_MODIFICATION: 0
        ACCESS_TIME: 3378383796
         TABLE_NAME: `employees`.`salaries`
         INDEX_NAME: PRIMARY
     NUMBER_RECORDS: 468
          DATA_SIZE: 14976
    COMPRESSED_SIZE: 0
         COMPRESSED: NO
             IO_FIX: IO_NONE
             IS_OLD: YES
    FREE_PAGE_CLOCK: 0
                        

Notes

  • This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

  • Querying this table can require MySQL to allocate a large block of contiguous memory, more than 64 bytes times the number of active pages in the buffer pool. This allocation could potentially cause an out-of-memory error, especially for systems with multi-gigabyte buffer pools.

  • Querying this table requires MySQL to lock the data structure representing the buffer pool while traversing the LRU list, which can reduce concurrency, especially for systems with multi-gigabyte buffer pools.

  • When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The INNODB_BUFFER_PAGE_LRU table reports information about these pages until they are evicted from the buffer pool. For more information about how the InnoDB manages buffer pool data, see Section 15.5.1, “Buffer Pool” .

25.39.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table

The INNODB_BUFFER_POOL_STATS table provides much of the same buffer pool information provided in SHOW ENGINE INNODB STATUS output. Much of the same information may also be obtained using InnoDB buffer pool server status variables .

The idea of making pages in the buffer pool young or not young refers to transferring them between the sublists at the head and tail of the buffer pool data structure. Pages made young take longer to age out of the buffer pool, while pages made not young are moved much closer to the point of eviction .

For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables” .

The INNODB_BUFFER_POOL_STATS table has these columns:

  • POOL_ID

    The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.

  • POOL_SIZE

    The InnoDB buffer pool size in pages.

  • FREE_BUFFERS

    The number of free pages in the InnoDB buffer pool.

  • DATABASE_PAGES

    The number of pages in the InnoDB buffer pool containing data. This number includes both dirty and clean pages.

  • OLD_DATABASE_PAGES

    The number of pages in the old buffer pool sublist.

  • MODIFIED_DATABASE_PAGES

    The number of modified (dirty) database pages.

  • PENDING_DECOMPRESS

    The number of pages pending decompression.

  • PENDING_READS

    The number of pending reads.

  • PENDING_FLUSH_LRU

    The number of pages pending flush in the LRU.

  • PENDING_FLUSH_LIST

    The number of pages pending flush in the flush list.

  • PAGES_MADE_YOUNG

    The number of pages made young.

  • PAGES_NOT_MADE_YOUNG

    The number of pages not made young.

  • PAGES_MADE_YOUNG_RATE

    The number of pages made young per second (pages made young since the last printout / time elapsed).

  • PAGES_MADE_NOT_YOUNG_RATE

    The number of pages not made per second (pages not made young since the last printout / time elapsed).

  • NUMBER_PAGES_READ

    The number of pages read.

  • NUMBER_PAGES_CREATED

    The number of pages created.

  • NUMBER_PAGES_WRITTEN

    The number of pages written.

  • PAGES_READ_RATE

    The number of pages read per second (pages read since the last printout / time elapsed).

  • PAGES_CREATE_RATE

    The number of pages created per second (pages created since the last printout / time elapsed).

  • PAGES_WRITTEN_RATE

    The number of pages written per second (pages written since the last printout / time elapsed).

  • NUMBER_PAGES_GET

    The number of logical read requests.

  • HIT_RATE

    The buffer pool hit rate.

  • YOUNG_MAKE_PER_THOUSAND_GETS

    The number of pages made young per thousand gets.

  • NOT_YOUNG_MAKE_PER_THOUSAND_GETS

    The number of pages not made young per thousand gets.

  • NUMBER_PAGES_READ_AHEAD

    The number of pages read ahead.

  • NUMBER_READ_AHEAD_EVICTED

    The number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.

  • READ_AHEAD_RATE

    The read-ahead rate per second (pages read ahead since the last printout / time elapsed).

  • READ_AHEAD_EVICTED_RATE

    The number of read-ahead pages evicted without access per second (read-ahead pages not accessed since the last printout / time elapsed).

  • LRU_IO_TOTAL

    Total LRU I/O.

  • LRU_IO_CURRENT

    LRU I/O for the current interval.

  • UNCOMPRESS_TOTAL

    The total number of pages decompressed.

  • UNCOMPRESS_CURRENT

    The number of pages decompressed in the current interval.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 8192
                    FREE_BUFFERS: 1
                  DATABASE_PAGES: 8085
              OLD_DATABASE_PAGES: 2964
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 0
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 22821
            PAGES_NOT_MADE_YOUNG: 3544303
           PAGES_MADE_YOUNG_RATE: 357.62602199870594
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 2389
            NUMBER_PAGES_CREATED: 12385
            NUMBER_PAGES_WRITTEN: 13111
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 33322210
                        HIT_RATE: 1000
    YOUNG_MAKE_PER_THOUSAND_GETS: 18
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 2024
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
                        

Notes

  • This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

25.39.4 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table

The INNODB_CACHED_INDEXES table reports the number of index pages cached in the InnoDB buffer pool for each index.

For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables” .

The INNODB_CACHED_INDEXES table has these columns:

  • SPACE_ID

    The tablespace ID.

  • INDEX_ID

    An identifier for the index. Index identifiers are unique across all the databases in an instance.

  • N_CACHED_PAGES

    The number of index pages cached in the InnoDB buffer pool.

范例

This query returns the number of index pages cached in the InnoDB buffer pool for a specific index:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CACHED_INDEXES WHERE INDEX_ID=65\G
*************************** 1. row ***************************
      SPACE_ID: 4294967294
      INDEX_ID: 65
N_CACHED_PAGES: 45
                        

This query returns the number of index pages cached in the InnoDB buffer pool for each index, using the INNODB_INDEXES and INNODB_TABLES tables to resolve the table name and index name for each INDEX_ID value.

SELECT
  tables.NAME AS table_name,
  indexes.NAME AS index_name,
  cached.N_CACHED_PAGES AS n_cached_pages
FROM
  INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
  INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,
  INFORMATION_SCHEMA.INNODB_TABLES AS tables
WHERE
  cached.INDEX_ID = indexes.INDEX_ID
  AND indexes.TABLE_ID = tables.TABLE_ID;
                        

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

25.39.5 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables

The INNODB_CMP and INNODB_CMP_RESET tables provide status information on operations related to compressed InnoDB tables.

The INNODB_CMP and INNODB_CMP_RESET tables have these columns:

  • PAGE_SIZE

    The compressed page size in bytes.

  • COMPRESS_OPS

    The number of times a B-tree page of size PAGE_SIZE has been compressed. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out.

  • COMPRESS_OPS_OK

    The number of times a B-tree page of size PAGE_SIZE has been successfully compressed. This count should never exceed COMPRESS_OPS .

  • COMPRESS_TIME

    The total time in seconds used for attempts to compress B-tree pages of size PAGE_SIZE .

  • UNCOMPRESS_OPS

    The number of times a B-tree page of size PAGE_SIZE has been uncompressed. B-tree pages are uncompressed whenever compression fails or at first access when the uncompressed page does not exist in the buffer pool.

  • UNCOMPRESS_TIME

    The total time in seconds used for uncompressing B-tree pages of the size PAGE_SIZE .

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP\G
*************************** 1. row ***************************
      page_size: 1024
   compress_ops: 0
compress_ops_ok: 0
  compress_time: 0
 uncompress_ops: 0
uncompress_time: 0
*************************** 2. row ***************************
      page_size: 2048
   compress_ops: 0
compress_ops_ok: 0
  compress_time: 0
 uncompress_ops: 0
uncompress_time: 0
*************************** 3. row ***************************
      page_size: 4096
   compress_ops: 0
compress_ops_ok: 0
  compress_time: 0
 uncompress_ops: 0
uncompress_time: 0
*************************** 4. row ***************************
      page_size: 8192
   compress_ops: 86955
compress_ops_ok: 81182
  compress_time: 27
 uncompress_ops: 26828
uncompress_time: 5
*************************** 5. row ***************************
      page_size: 16384
   compress_ops: 0
compress_ops_ok: 0
  compress_time: 0
 uncompress_ops: 0
uncompress_time: 0
                        

Notes

25.39.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables

The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables provide status information on compressed pages within the InnoDB buffer pool .

The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables have these columns:

  • PAGE_SIZE

    The block size in bytes. Each record of this table describes blocks of this size.

  • BUFFER_POOL_INSTANCE

    A unique identifier for the buffer pool instance.

  • PAGES_USED

    The number of blocks of size PAGE_SIZE that are currently in use.

  • PAGES_FREE

    The number of blocks of size PAGE_SIZE that are currently available for allocation. This column shows the external fragmentation in the memory pool. Ideally, these numbers should be at most 1.

  • RELOCATION_OPS

    The number of times a block of size PAGE_SIZE has been relocated. The buddy system can relocate the allocated buddy neighbor of a freed block when it tries to form a bigger freed block. Reading from the INNODB_CMPMEM_RESET table resets this count.

  • RELOCATION_TIME

    The total time in microseconds used for relocating blocks of size PAGE_SIZE . Reading from the table INNODB_CMPMEM_RESET resets this count.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM\G
*************************** 1. row ***************************
           page_size: 1024
buffer_pool_instance: 0
          pages_used: 0
          pages_free: 0
      relocation_ops: 0
     relocation_time: 0
*************************** 2. row ***************************
           page_size: 2048
buffer_pool_instance: 0
          pages_used: 0
          pages_free: 0
      relocation_ops: 0
     relocation_time: 0
*************************** 3. row ***************************
           page_size: 4096
buffer_pool_instance: 0
          pages_used: 0
          pages_free: 0
      relocation_ops: 0
     relocation_time: 0
*************************** 4. row ***************************
           page_size: 8192
buffer_pool_instance: 0
          pages_used: 7673
          pages_free: 15
      relocation_ops: 4638
     relocation_time: 0
*************************** 5. row ***************************
           page_size: 16384
buffer_pool_instance: 0
          pages_used: 0
          pages_free: 0
      relocation_ops: 0
     relocation_time: 0
                        

Notes

25.39.7 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables

The INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables provide status information on operations related to compressed InnoDB tables and indexes, with separate statistics for each combination of database, table, and index, to help you evaluate the performance and usefulness of compression for specific tables.

For a compressed InnoDB table, both the table data and all the secondary indexes are compressed. In this context, the table data is treated as just another index, one that happens to contain all the columns: the clustered index .

The INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables have these columns:

  • DATABASE_NAME

    The schema (database) containing the applicable table.

  • TABLE_NAME

    The table to monitor for compression statistics.

  • INDEX_NAME

    The index to monitor for compression statistics.

  • COMPRESS_OPS

    The number of compression operations attempted. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out.

  • COMPRESS_OPS_OK

    The number of successful compression operations. Subtract from the COMPRESS_OPS value to get the number of compression failures . Divide by the COMPRESS_OPS value to get the percentage of compression failures.

  • COMPRESS_TIME

    The total time in seconds used for compressing data in this index.

  • UNCOMPRESS_OPS

    The number of uncompression operations performed. Compressed InnoDB pages are uncompressed whenever compression fails , or the first time a compressed page is accessed in the buffer pool and the uncompressed page does not exist.

  • UNCOMPRESS_TIME

    The total time in seconds used for uncompressing data in this index.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX\G
*************************** 1. row ***************************
  database_name: employees
     table_name: salaries
     index_name: PRIMARY
   compress_ops: 0
compress_ops_ok: 0
  compress_time: 0
 uncompress_ops: 23451
uncompress_time: 4
*************************** 2. row ***************************
  database_name: employees
     table_name: salaries
     index_name: emp_no
   compress_ops: 0
compress_ops_ok: 0
  compress_time: 0
 uncompress_ops: 1597
uncompress_time: 0
                        

Notes

25.39.8 The INFORMATION_SCHEMA INNODB_COLUMNS Table

The INNODB_COLUMNS table provides metadata about InnoDB table columns.

For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables” .

The INNODB_COLUMNS table has these columns:

  • TABLE_ID

    An identifier representing the table associated with the column; the same value as INNODB_TABLES.TABLE_ID .

  • NAME

    The name of the column. These names can be uppercase or lowercase depending on the lower_case_table_names setting. There are no special system-reserved names for columns.

  • POS

    The ordinal position of the column within the table, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps. The POS value for a virtual generated column encodes the column sequence number and ordinal position of the column. For more information, see the POS column description in Section 25.39.30, “The INFORMATION_SCHEMA INNODB_VIRTUAL Table” .

  • MTYPE

    Stands for main type . A numeric identifier for the column type. 1 = VARCHAR , 2 = CHAR , 3 = FIXBINARY , 4 = BINARY , 5 = BLOB , 6 = INT , 7 = SYS_CHILD , 8 = SYS , 9 = FLOAT , 10 = DOUBLE , 11 = DECIMAL , 12 = VARMYSQL , 13 = MYSQL , 14 = GEOMETRY .

  • PRTYPE

    The InnoDB precise type , a binary value with bits representing MySQL data type, character set code, and nullability.

  • LEN

    The column length, for example 4 for INT and 8 for BIGINT . For character columns in multibyte character sets, this length value is the maximum length in bytes needed to represent a definition such as VARCHAR( N ) ; that is, it might be 2* N , 3* N , and so on depending on the character encoding.

  • HAS_DEFAULT

    A boolean value indicating whether a column that was added instantly using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT has a default value. All columns added instantly have a default value, which makes this column an indicator of whether the column was added instantly.

  • DEFAULT_VALUE

    The initial default value of a column that was added instantly using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT . If the default value is NULL or was not specified, this column reports NULL . An explicitly specified non- NULL default value is shown in an internal binary format. Subsequent modifications of the column default value do not change the value reported by this column.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: col1
          POS: 0
        MTYPE: 6
       PRTYPE: 1027
          LEN: 4
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 2. row ***************************
     TABLE_ID: 71
         NAME: col2
          POS: 1
        MTYPE: 2
       PRTYPE: 524542
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 3. row ***************************
     TABLE_ID: 71
         NAME: col3
          POS: 2
        MTYPE: 1
       PRTYPE: 524303
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
                        

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

25.39.9 The INFORMATION_SCHEMA INNODB_DATAFILES Table

The INNODB_DATAFILES table provides data file path information for InnoDB file-per-table and general tablespaces.

For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables” .

Note

The INFORMATION_SCHEMA FILES table reports metadata for InnoDB tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, the global temporary tablespace, and undo tablespaces.

The INNODB_DATAFILES table has these columns:

  • SPACE

    The tablespace ID.

  • PATH

    The tablespace data file path. If a file-per-table tablespace is created in a location outside the MySQL data directory, the path value is a fully qualified directory path. Otherwise, the path is relative to the data directory.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57\G
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd
                        

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

25.39.10 The INFORMATION_SCHEMA INNODB_FIELDS Table

The INNODB_FIELDS table provides metadata about the key columns (fields) of InnoDB indexes.

For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables” .

The INNODB_FIELDS table has these columns:

  • INDEX_ID

    An identifier for the index associated with this key field; the same value as INNODB_INDEXES.INDEX_ID .

  • NAME

    The name of the original column from the table; the same value as INNODB_COLUMNS.NAME .

  • POS

    The ordinal position of the key field within the index, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS WHERE INDEX_ID = 117\G
*************************** 1. row ***************************
INDEX_ID: 117
    NAME: col1
     POS: 0
                        

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

25.39.11 The INFORMATION_SCHEMA INNODB_FOREIGN Table

The INNODB_FOREIGN table provides metadata about InnoDB foreign keys .

For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables” .

The INNODB_FOREIGN table has these columns:

  • ID

    The name (not a numeric value) of the foreign key index, preceded by the schema (database) name; for example, test/products_fk .

  • FOR_NAME

    The name of the child table in this foreign key relationship.

  • REF_NAME

    The name of the parent table in this foreign key relationship.

  • N_COLS

    The number of columns in the foreign key index.

  • TYPE

    A collection of bit flags with information about the foreign key column, ORed together. 0 = ON DELETE/UPDATE RESTRICT , 1 = ON DELETE CASCADE , 2 = ON DELETE SET NULL , 4 = ON UPDATE CASCADE , 8 = ON UPDATE SET NULL , 16 = ON DELETE NO ACTION , 32 = ON UPDATE NO ACTION .

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN\G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1
                        

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

25.39.12 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table

The INNODB_FOREIGN_COLS table provides status information about InnoDB foreign key columns.

For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables” .

The INNODB_FOREIGN_COLS table has these columns:

  • ID

    The foreign key index associated with this index key field; the same value as INNODB_FOREIGN.ID .

  • FOR_COL_NAME

    The name of the associated column in the child table.

  • REF_COL_NAME

    The name of the associated column in the parent table.

  • POS

    The ordinal position of this key field within the foreign key index, starting from 0.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1'\G
*************************** 1. row ***************************
          ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0
                        

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

25.39.13 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table

The INNODB_FT_BEING_DELETED table provides a snapshot of the INNODB_FT_DELETED table; it is used only during an OPTIMIZE TABLE maintenance operation. When OPTIMIZE TABLE is run, the INNODB_FT_BEING_DELETED table is emptied, and DOC_ID values are removed from the INNODB_FT_DELETED table. Because the contents of INNODB_FT_BEING_DELETED typically have a short lifetime, this table has limited utility for monitoring or debugging. For information about running OPTIMIZE TABLE on tables with FULLTEXT indexes, see Section 12.9.6, “Fine-Tuning MySQL Full-Text Search” .

This table is empty initially. Before querying it, set the value of the innodb_ft_aux_table system variable to the name (including the database name) of the table that contains the FULLTEXT index; for example test/articles . The output appears similar to the example provided for the INNODB_FT_DELETED table.

For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables” .

The INNODB_FT_BEING_DELETED table has these columns:

  • DOC_ID

    The document ID of the row that is in the process of being deleted. This value might reflect the value of an ID column that you defined for the underlying table, or it can be a sequence value generated by InnoDB when the table contains no suitable column. This value is used when you perform text searches, to skip rows in the INNODB_FT_INDEX_TABLE table before data for deleted rows is physically removed from the FULLTEXT index by an OPTIMIZE TABLE statement. For more information, see Optimizing InnoDB Full-Text Indexes .

Notes

25.39.14 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table

The INNODB_FT_CONFIG table provides metadata about the FULLTEXT index and associated processing for an InnoDB table.

This table is empty initially. Before querying it, set the value of the innodb_ft_aux_table system variable to the name (including the database name) of the table that contains the FULLTEXT index; for example test/articles .

For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables” .

The INNODB_FT_CONFIG table has these columns:

  • KEY

    The name designating an item of metadata for an InnoDB table containing a FULLTEXT index.

    The values for this column might change, depending on the needs for performance tuning and debugging for InnoDB full-text processing. The key names and their meanings include: