Chapter 24 INFORMATION_SCHEMA Tables

COLLATION_NAME is the collation name.

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

ID is the collation ID.

IS_DEFAULT indicates whether the collation is the default for its character set.

IS_COMPILED indicates whether the character set is compiled into the server.

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

PAD_ATTRIBUTE indicates the collation pad attribute.

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 multibyte 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 utf8_swedish_ci, the character set is what is before the first underscore: utf8.

GENERATION_EXPRESSION is nonempty for generated columns and displays the expression used to compute column values. For information about generated columns, see Section 13.1.18.8, “CREATE TABLE and Generated Columns”.

The EXTRA column contains VIRTUAL GENERATED or VIRTUAL STORED for generated columns.

SRS_ID 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. SRS_ID was added in MySQL 8.0-3.

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.

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.

The ENGINES table is a nonstandard table. Its contents correspond to the columns of the SHOW ENGINES statement. For descriptions of its columns, see Section 13.7.6.16, “SHOW ENGINES Syntax”.

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 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; in MySQL 8.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: 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 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 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 is 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.10, “Server SQL Modes”.

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 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: 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 17.4.1.16, “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 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 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 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 a comment, if the event has one. If not, 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: 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.

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

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

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

FILE_ID is the tablespace ID, also referred to as the space_id or fil_space_t::id.

FILE_NAME is the name of the data file. File-per-table and general tablespaces have a .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. Temporary tablespaces are prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (datadir).

FILE_TYPE is 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.

TABLESPACE_NAME is 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.

ENGINE is the storage engine. For InnoDB files, the value is always InnoDB.

FREE_EXTENTS is the number of fully free extents in the current data file.

TOTAL_EXTENTS is the number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

EXTENT_SIZE is 1048576 (1MB) for files with a 4k, 8k, or 16k page size. Extent size is 2097152 bytes (2MB) for files with a 32k page size, and 4194304 (4MB) for files with a 64k page size. INFORMATION_SCHEMA.FILES does not report InnoDB page size. Page size is defined by the innodb_page_size option. Extent size information can also be retrieved from INNODB_TABLESPACES where FILES.FILE_ID = INNODB_TABLESPACES.SPACE_ID.

INITIAL_SIZE is the initial size of the file, in bytes.

MAXIMUM_SIZE is the maximum number of bytes allowed 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 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.

AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or defined by innodb_temp_data_file_path for temporary tablespaces.

DATA_FREE is 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.

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

The following query returns all data pertinent to InnoDB tablespaces.

mysql>SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS,
EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE
FROM INFORMATION_SCHEMA.FILES \G

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.

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:

WORD

The keyword.

RESERVED

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

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 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 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 22.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 22.2.6, “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.

For partitioned InnoDB tables, this column is always NULL.

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.

In MySQL 8.0, 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 (Bug #11748924, Bug #37728).

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 the tablespace to which the partition belongs. 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 PLUGINS table is a nonstandard table.

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

PLUGIN_VERSION is the version from the plugin's general type descriptor.

PLUGIN_STATUS indicates the plugin status, one of ACTIVE, INACTIVE, DISABLED, DELETING, or DELETED.

PLUGIN_TYPE indicates the type of plugin, such as STORAGE ENGINE, INFORMATION_SCHEMA, or AUTHENTICATION.

PLUGIN_TYPE_VERSION is the version from the plugin's type-specific descriptor.

PLUGIN_LIBRARY is 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 indicates the plugin API interface version.

PLUGIN_AUTHOR names the plugin author.

PLUGIN_DESCRIPTION provides a short description of the plugin.

PLUGIN_LICENSE indicates how the plugin is licensed; for example, GPL.

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

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, 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, though.

Process information is also available from the performance_schema.threads table. However, access to threads does not require a mutex and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences because they require a mutex. threads also shows information about background threads, which INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST do not. This means that threads can be used to monitor activity the other thread information sources cannot.

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.

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.

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.

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

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 column holds the same value as the CREATED column.

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.10, “Server SQL Modes”.

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.

The DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, 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.

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.

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

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.

TABLE_CATALOG

The COLUMNS.TABLE_CATALOG value.

TABLE_SCHEMA

The COLUMNS.TABLE_SCHEMA value.

TABLE_NAME

The COLUMNS.TABLE_NAME value.

COLUMN_NAME

The COLUMNS.TABLE_COLUMN_NAME value.

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.

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.

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, as a WKT value.

DESCRIPTION

The spatial reference system description.

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.

Refer to SHOW TABLE STATUS for field descriptions.

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

The DATA_FREE column shows the free space in bytes for InnoDB tables.

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.

For partitioned InnoDB tables, the CHECK_TIME column is always NULL.

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.

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.

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

The names in the “SHOW Name” column refer to the SHOW TRIGGERS statement, not SHOW CREATE TRIGGER. See Section 13.7.6.38, “SHOW TRIGGERS Syntax”.

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

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_SCHEMA and EVENT_OBJECT_TABLE: As noted in Section 23.3, “Using Triggers”, every trigger is associated with exactly one table. These columns indicate the database in which this table occurs, and the table name, respectively.

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_STATEMENT: The trigger body; that is, the statement executed when the trigger activates. This text uses UTF-8 encoding.

ACTION_ORIENTATION: Always contains the value 'ROW'.

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

ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW: 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'.

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.10, “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.

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

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

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 a 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 23.5.3, “Updatable and Insertable Views”.)

DEFINER: The account of the user who created the view, in 'user_name'@'host_name' format. SECURITY_TYPE has a value 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.