Chapter 24 INFORMATION_SCHEMA Tables

INFORMATION_SCHEMA Usage Notes

Character Set Considerations

INFORMATION_SCHEMA as Alternative to SHOW Statements

INFORMATION_SCHEMA and Privileges

Performance Considerations

Standards Considerations

Conventions in the INFORMATION_SCHEMA Reference Sections

Related Information

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.

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.

COLLATION_NAME

The collation name.

CHARACTER_SET_NAME

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

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 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:

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

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.

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

The COLUMN_PRIVILEGES table is a nonstandard INFORMATION_SCHEMA table.

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.

ENGINE

The name of the storage engine.

SUPPORT

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

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.

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.

The ENGINES table is a nonstandard INFORMATION_SCHEMA table.

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

The EVENTS table is a nonstandard INFORMATION_SCHEMA table.

Times in the EVENTS table are displayed using the event time zone or the current session time zone, as described in Section 23.4.4, “Event Metadata”.

For more information about SLAVESIDE_DISABLED and the ORIGINATOR column, see Section 17.4.1.16, “Replication of Invoked Features”.

FILE_ID

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

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. Temporary tablespaces are 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).

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.

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.

TABLE_CATALOG

This value is always empty.

TABLE_SCHEMA

This value is always NULL.

TABLE_NAME

For InnoDB: This value is always NULL.

LOGFILE_GROUP_NAME

For InnoDB: This value is always NULL.

LOGFILE_GROUP_NUMBER

For InnoDB: This value is always NULL.

ENGINE

For InnoDB: This value is always InnoDB.

FULLTEXT_KEYS

For InnoDB: This value is always NULL.

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.

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.

EXTENT_SIZE

For InnoDB: 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. 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.

INITIAL_SIZE

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

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

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 temporary tablespaces.

CREATION_TIME

For InnoDB: This value is always NULL.

LAST_UPDATE_TIME

For InnoDB: This value is always NULL.

LAST_ACCESS_TIME

For InnoDB: This value is always NULL.

RECOVER_TIME

For InnoDB: This value is always NULL.

TRANSACTION_COUNTER

For InnoDB: This value is always NULL.

VERSION

For InnoDB: This value is always NULL.

ROW_FORMAT

For InnoDB: This value is always NULL.

TABLE_ROWS

For InnoDB: This value is always NULL.

AVG_ROW_LENGTH

For InnoDB: This value is always NULL.

DATA_LENGTH

For InnoDB: This value is always NULL.

MAX_DATA_LENGTH

For InnoDB: This value is always NULL.

INDEX_LENGTH

For InnoDB: 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.

CREATE_TIME

For InnoDB: This value is always NULL.

UPDATE_TIME

For InnoDB: This value is always NULL.

CHECK_TIME

For InnoDB: This value is always NULL.

CHECKSUM

For InnoDB: 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.

EXTRA

For InnoDB: This value is always NULL.

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.

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.

WORD

The keyword.

RESERVED

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

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.

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:

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.

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

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.

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

The PARTITIONS table is a nonstandard INFORMATION_SCHEMA table.

A nonpartitioned table 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.

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

The PLUGINS table is a nonstandard INFORMATION_SCHEMA table.

For plugins installed with INSTALL PLUGIN, the PLUGIN_NAME and PLUGIN_LIBRARY values are also registered in the mysql.plugin table.

For information about plugin data structures that form the basis of the information in the PLUGINS table, see Section 28.2, “The MySQL Plugin API”.

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 23.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.9, “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.

The PROCESSLIST table is a nonstandard INFORMATION_SCHEMA table.

Like the output from the SHOW PROCESSLIST 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 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.

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.

The PROFILING table is a nonstandard INFORMATION_SCHEMA table.

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.

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.

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

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.

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.

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

The SCHEMA_PRIVILEGES table is a nonstandard INFORMATION_SCHEMA table.

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.

For additional information about index prefixes, see Section 8.3.5, “Column Indexes”, and Section 13.1.14, “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:

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.

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.

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.

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.

CREATE SPATIAL REFERENCE SYSTEM: See Section 13.1.17, “CREATE SPATIAL REFERENCE SYSTEM Syntax”. The description for this statement includes additional information about SRS components.

DROP SPATIAL REFERENCE SYSTEM: See Section 13.1.28, “DROP SPATIAL REFERENCE SYSTEM Syntax”.

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

For more information, see Section 24.15, “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 from when CREATE TABLE was executed are retained and the options reported here may differ from the active table settings and options.

For InnoDB tables, the actual ROW_FORMAT and KEY_BLOCK_SIZE options are reported. Prior to MySQL 8.0, Create_options reports the originally supplied ROW_FORMAT and KEY_BLOCK_SIZE. For more information, see Section 13.1.18, “CREATE TABLE Syntax”.

CREATE_OPTIONS shows partitioned if the table is partitioned. It also shows the ENCRYPTION option specified when creating or altering a file-per-table tablespace. This column does not show the encryption option specified when creating or altering a general tablespace. The ENCRYPTION column of the INNODB_TABLESPACES table is applicable to both file-per-table and general tablespaces.

TABLE_COMMENT

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

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.

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.

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.

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 CHECK. This is a CHAR (not ENUM) column. The CHECK value is not available until MySQL supports CHECK.

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.

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

The TABLE_PRIVILEGES table is a nonstandard INFORMATION_SCHEMA table.

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

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

The USER_PRIVILEGES table is a nonstandard INFORMATION_SCHEMA table.

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

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.

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.

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.