Chapter 19. INFORMATION_SCHEMA Tables

TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.

TABLE_TYPE should be BASE TABLE or VIEW. Currently, the TABLES table does not list TEMPORARY tables.

For partitioned tables, the ENGINE column shows the name of the storage engine used by all partitions. (Previously, this column showed PARTITION for such tables.)

The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database.

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

Beginning with MySQL 6.0.6, the DATA_FREE column shows the free space in bytes for InnoDB tables.

We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

The CREATE_OPTIONS column shows partitioned if the table is partitioned.

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

ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW, SELECT does not have automatic ordering.

CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multi-byte character sets.

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

STORAGE and FORMAT apply to NDB tables. STORAGE indicates whether a column is stored on disk or memory, and FORMAT indicates the column storage format (FIXED, DYNAMIC, or DEFAULT).

There is no standard table for indexes. The preceding list is similar to what SQL Server 2000 returns for sp_statistics, except that we replaced the name QUALIFIER with CATALOG and we replaced the name OWNER with SCHEMA.

Clearly, the preceding table and the output from SHOW INDEX are derived from the same parent. So the correlation is already close.

The INDEX_COMMENT column indicates any comment provided for the index with a COMMENT attribute when the index was created.

This is a nonstandard table. It takes its values from the mysql.user table.

This is a nonstandard table. It takes its values from the mysql.db table.

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES, ALTER, INDEX, DROP, CREATE VIEW.

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

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.

If the user has GRANT OPTION privilege, IS_GRANTABLE should be YES. Otherwise, IS_GRANTABLE should be NO. The output does not list GRANT OPTION as a separate privilege.

The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.

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

The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. This is a CHAR (not ENUM) column. The CHECK value is not available until we support CHECK.

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

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

The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced.

For example, 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:

MySQL calculates EXTERNAL_LANGUAGE thus:

CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the routine was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the routine was created. DATABASE_COLLATION is the collation of the database with which the routine is associated.

The DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, and COLLATION_NAME columns provide information about the data type for the RETURNS clause of stored functions. If a stored routine is a stored procedure, these columns all are NULL. These columns were added in MySQL 5.2.6.

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

The VIEW_DEFINITION column has most of what you see in the Create Table field 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

The CHECK_OPTION column has a value of NONE, CASCADE, or LOCAL.

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 the view is not updatable, statements such UPDATE, DELETE, and INSERT are illegal and will be rejected. (Note that even if a view is updatable, it might not be possible to insert into it; for details, refer to Section 12.1.16, “CREATE VIEW Syntax”.)

The DEFINER column indicates who defined the view. SECURITY_TYPE has a value of DEFINER or INVOKER.

CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the view was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the view was created.

The TRIGGER_SCHEMA and TRIGGER_NAME columns contain the name of the database in which the trigger occurs and the trigger name, respectively.

The EVENT_MANIPULATION column contains one of the values 'INSERT', 'DELETE', or 'UPDATE'.

As noted in Section 18.3, “Using Triggers”, every trigger is associated with exactly one table. The EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE columns contain the database in which this table occurs, and the table's name.

The ACTION_ORDER statement contains the ordinal position of the trigger's action within the list of all similar triggers on the same table. Currently, this value is always 0, because it is not possible to have more than one trigger with the same EVENT_MANIPULATION and ACTION_TIMING on the same table.

The ACTION_STATEMENT column contains the statement to be executed when the trigger is invoked. This is the same as the text displayed in the Statement column of the output from SHOW TRIGGERS. Note that this text uses UTF-8 encoding.

The ACTION_ORIENTATION column always contains the value 'ROW'.

The ACTION_TIMING column contains one of the two values 'BEFORE' or 'AFTER'.

The columns ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW contain the old and new column identifiers, respectively. This means that ACTION_REFERENCE_OLD_ROW always contains the value 'OLD' and ACTION_REFERENCE_NEW_ROW always contains the value 'NEW'.

The SQL_MODE column shows the server SQL mode that was in effect at the time when the trigger was created (and thus which remains in effect for this trigger whenever it is invoked, regardless of the current server SQL mode). The possible range of values for this column is the same as that of the sql_mode system variable. See Section 5.1.8, “Server SQL Modes”.

The DEFINER column indicates who defined the trigger.

CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the trigger was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the trigger was created. DATABASE_COLLATION is the collation of the database with which the trigger is associated.

The following columns currently always contain NULL: ACTION_CONDITION, ACTION_REFERENCE_OLD_TABLE, ACTION_REFERENCE_NEW_TABLE, and CREATED.

The PLUGINS table is a nonstandard table.

The ENGINES table is a nonstandard table.

The PARTITIONS table is a nonstandard table.

Each record in this table corresponds to an individual partition or subpartition of a partitioned table.

TABLE_CATALOG: This column is always def.

TABLE_SCHEMA: This column contains the name of the database to which the table belongs.

TABLE_NAME: This column contains the name of the table containing the partition.

PARTITION_NAME: The name of the partition.

SUBPARTITION_NAME: If the PARTITIONS table record represents a subpartition, then this column contains the name of subpartition; otherwise it is 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 17.2, “Partition 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 17.2.5, “Subpartitioning”.

PARTITION_EXPRESSION: This is 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 record 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              |
+----------------------+
1 row in set (0.09 sec)

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, then 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 comma-separated list of 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.

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.

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.

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

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

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

CREATE_TIME: The time of the partition's or subpartition's creation.

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.

CHECKSUM: The checksum value, if any; otherwise, this column is NULL.

PARTITION_COMMENT: This column contains the text of any comment made for the partition.

The default value for this column is an empty string.

NODEGROUP: This is the nodegroup to which the partition belongs. This is relevant only to MySQL Cluster tables; otherwise the value of this column is always 0.

TABLESPACE_NAME: This column contains the name of tablespace to which the partition belongs. In MySQL 6.0, the value of this column is always DEFAULT.

A nonpartitioned table has one record in INFORMATION_SCHEMA.PARTITIONS; 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. (The PARTITION_COMMENT column in this case is blank.)

The EVENTS table is a nonstandard table.

EVENT_CATALOG: The value of this column is always def.

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

EVENT_NAME: The name of the event.

DEFINER: The user who created the event. Always displayed in 'user_name'@'host_name' format.

TIME_ZONE: The time zone in effect when schedule for the event was last modified. If the event's schedule has not been modified since the event was created, then this is the time zone that was in effect at the event's creation. The default value is SYSTEM.

EVENT_BODY: The language used for the statements in the event's DO clause; in MySQL 6.0, this is always SQL.

This column is not to be confused with the column of the same name (now named EVENT_DEFINITION) that existed in earlier MySQL versions.

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: One of the two values ONE TIME or RECURRING.

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 2006-02-09 14:05:30, the value shown in this column would be '2006-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 recurring events, this column contains the numeric portion of the event's EVERY clause.

For a one-time event (that is, an event whose timing is determined by an AT clause), this column's value is NULL.

INTERVAL_FIELD: For recurring events, this column contains the units portion of the EVERY clause governing the timing of the event. Thus, this column contains a value such as 'YEAR', 'QUARTER', 'DAY', and so on.

For a one-time event (that is, an event whose timing is determined by an AT clause), this column's value is NULL.

SQL_MODE: The SQL mode in effect at the time the event was created or altered.

STARTS: 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 empty.

ENDS: For a recurring event whose definition includes a ENDS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column (see previous example), this value resolves any expressions used.

If there is no ENDS clause affecting the timing of the event, this column contains NULL.

STATUS: One of the three values 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 was replicated to the current MySQL server which is acting as a slave, but the event is not presently being executed on the slave. See Section 16.3.1.7, “Replication of Invoked Features”, for more 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 DATETIME value.

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

LAST_EXECUTED: The date and time when the event last executed. A DATETIME value. If the event has never executed, this column's value is NULL.

Before MySQL 6.0.5, LAST_EXECUTED indicates when event finished executing. As of 6.0.5, LAST_EXECUTED instead indicates when the event started. As a result, the ENDS column is never less than LAST_EXECUTED.

EVENT_COMMENT: The text of a comment, if the event has one. If there is no comment, the value of this column is an empty string.

ORIGINATOR: The server ID of the MySQL server on which the event was created; used in replication. The default value is 0.

CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the event was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the event was created. DATABASE_COLLATION is the collation of the database with which the event is associated.

FILE_ID column values are auto-generated.

FILE_NAME is the name of a data file created by CREATE TABLESPACE or ALTER TABLESPACE.

FILE_TYPE is SYSTEM DATAFILE for the FALCON_MASTER, FALCON_USER and FALCON_TEMPORARY tablespace files, or USER DATAFILE for any user-create tablespace files.

TABLESPACE_NAME is the name of the tablespace with which the file is associated.

Currently, the value of the TABLESPACE_CATALOG column is always NULL.

TABLE_NAME is the name of the table with which the file is associated, if any.

The EXTENT_SIZE is always 0.

For MySQL Falcon tablespace files, the following columns are always NULL:

For Falcon tablespaces, the value of the STATUS column is always NORMAL.

There are no SHOW commands associated with the FILES table.

The TABLESPACE_NAME will be filled with the name of the Falcon tablespace.

The Engine will be set to Falcon.

The TABLESPACE_TYPE will be set tp DEFAULT for the default tablespace used by Falcon; to TEMPORARY for temporary Falcon tables; to MASTER CATALOG for the internal Falcon files and USER DEFINED for all user tablespaces

The following fields will be set to NULL for Falcon tables:

The PROCESSLIST table is a nonstandard table.

Like the output from the corresponding SHOW statement, the PROCESSLIST table will only show information 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 INFORMATION_SCHEMA.PROCESSLIST, then MySQL will populate 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, though.

TABLE_NAME has the same value as TABLE_NAME in INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

CONSTRAINT_SCHEMA and CONSTRAINT_NAME identify the foreign key.

UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, and REFERENCED_TABLE_NAME identify the referenced key.

The only valid value at this time for MATCH_OPTION is NONE.

The possible values for UPDATE_RULE or DELETE_RULE are CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.

The VARIABLE_VALUE column for each of these tables is defined as VARCHAR(20480).

The VARIABLE_VALUE column for each of these tables is defined as VARCHAR(20480).

The PARAMETERS table was added in MySQL 5.2.6.

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

The ROUTINE_TYPE column was added in MySQL 6.0.5. (Bug#33106)

The PROFILING table was added in MySQL 6.0.5.

QUERY_ID is a numeric statement identifier.

SEQ is a sequence number indicating the display order for rows with the same QUERY_ID value.

STATE is the profiling state to which the row measurements apply.

DURATION indicates how long statement execution remained in the given state, in seconds.

CPU_USER and CPU_SYSTEM indicate user and system CPU use, in seconds.

CONTEXT_VOLUNTARY and CONTEXT_INVOLUNTARY indicate how many voluntary and involuntary context switches occurred.

BLOCK_OPS_IN and BLOCK_OPS_OUT indicate the number of block input and output operations.

MESSAGES_SENT and MESSAGES_RECEIVED indicate the number of communication messages sent and received.

PAGE_FAULTS_MAJOR and PAGE_FAULTS_MINOR indicate the number of major and minor page faults.

SWAPS indicates how many swaps occurred.

SOURCE_FUNCTION, SOURCE_FILE, and SOURCE_LINE provide information indicating where in the source code the profiled state executes.

SHOW can be used to get information about the structure of INFORMATION_SCHEMA itself.

Several SHOW statements accept a WHERE clause that provides more flexibility in specifying which rows to display.