Table of Contents
INFORMATION_SCHEMA provides access to database
metadata, information about
the MySQL server such as the name of a database or table, the data
type of a column, or access privileges. Other terms that are
sometimes used for this information are
data dictionary and
system catalog.
INFORMATION_SCHEMA provides access to database
metadata, information about
the MySQL server such as the name of a database or table, the data
type of a column, or access privileges. Other terms that are
sometimes used for this information are
data dictionary and
system catalog.
INFORMATION_SCHEMA is a database within each
MySQL instance, the place that stores information about all the
other databases that the MySQL server maintains. The
INFORMATION_SCHEMA database contains several
read-only tables. They are actually views, not base tables, so
there are no files associated with them, and you cannot set
triggers on them. Also, there is no database directory with that
name.
Although you can select INFORMATION_SCHEMA as
the default database with a USE
statement, you can only read the contents of tables, not perform
INSERT,
UPDATE, or
DELETE operations on them.
Here is an example of a statement that retrieves information
from INFORMATION_SCHEMA:
mysql>SELECT table_name, table_type, engineFROM information_schema.tablesWHERE table_schema = 'db5'ORDER BY table_name;+------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | fk | BASE TABLE | InnoDB | | fk2 | BASE TABLE | InnoDB | | goto | BASE TABLE | MyISAM | | into | BASE TABLE | MyISAM | | k | BASE TABLE | MyISAM | | kurs | BASE TABLE | MyISAM | | loop | BASE TABLE | MyISAM | | pk | BASE TABLE | InnoDB | | t | BASE TABLE | MyISAM | | t2 | BASE TABLE | MyISAM | | t3 | BASE TABLE | MyISAM | | t7 | BASE TABLE | MyISAM | | tables | BASE TABLE | MyISAM | | v | VIEW | NULL | | v2 | VIEW | NULL | | v3 | VIEW | NULL | | v56 | VIEW | NULL | +------------+------------+--------+ 17 rows in set (0.01 sec)
Explanation: The statement requests a list of all the tables in
database db5, showing just three pieces of
information: the name of the table, its type, and its storage
engine.
The definition for character columns (for example,
TABLES.TABLE_NAME) is generally
VARCHAR( where N) CHARACTER SET
utf8N is at least
64. MySQL uses the default collation for this character set
(utf8_general_ci) for all searches, sorts,
comparisons, and other string operations on such columns.
Because some MySQL objects are represented as files, searches in
INFORMATION_SCHEMA string columns can be
affected by file system case sensitivity. For more information,
see Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.
The SELECT ... FROM INFORMATION_SCHEMA
statement is intended as a more consistent way to provide access
to the information provided by the various
SHOW statements that MySQL
supports (SHOW DATABASES,
SHOW TABLES, and so forth). Using
SELECT has these advantages,
compared to SHOW:
It conforms to Codd's rules, because all access is done on tables.
You can use the familiar syntax of the
SELECT statement, and only
need to learn some table and column names.
The implementor need not worry about adding keywords.
You can filter, sort, concatenate, and transform the results
from INFORMATION_SCHEMA queries into
whatever format your application needs, such as a data
structure or a text representation to parse.
This technique is more interoperable with other database systems. For example, Oracle Database users are familiar with querying tables in the Oracle data dictionary.
Because SHOW is familiar and
widely used, the SHOW statements
remain as an alternative. In fact, along with the implementation
of INFORMATION_SCHEMA, there are enhancements
to SHOW as described in
Section 25.42, “Extensions to SHOW Statements”.
Each MySQL user has the right to access these tables, but can
see only the rows in the tables that correspond to objects for
which the user has the proper access privileges. In some cases
(for example, the ROUTINE_DEFINITION column
in the INFORMATION_SCHEMA
ROUTINES table), users who have
insufficient privileges see NULL. These
restrictions do not apply for
InnoDB tables; you can see them
with only the PROCESS privilege.
The same privileges apply to selecting information from
INFORMATION_SCHEMA and viewing the same
information through SHOW
statements. In either case, you must have some privilege on an
object to see information about it.
INFORMATION_SCHEMA queries that search for
information from more than one database might take a long time
and impact performance. To check the efficiency of a query, you
can use EXPLAIN. For information
about using EXPLAIN output to
tune INFORMATION_SCHEMA queries, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
The implementation for the INFORMATION_SCHEMA
table structures in MySQL follows the ANSI/ISO SQL:2003 standard
Part 11 Schemata. Our intent is
approximate compliance with SQL:2003 core feature F021
Basic information schema.
Users of SQL Server 2000 (which also follows the standard) may
notice a strong similarity. However, MySQL has omitted many
columns that are not relevant for our implementation, and added
columns that are MySQL-specific. One such added column is the
ENGINE column in the
INFORMATION_SCHEMA
TABLES table.
Although other DBMSs use a variety of names, like
syscat or system, the
standard name is INFORMATION_SCHEMA.
To avoid using any name that is reserved in the standard or in
DB2, SQL Server, or Oracle, we changed the names of some columns
marked “MySQL extension”. (For example, we changed
COLLATION to
TABLE_COLLATION in the
TABLES table.) See the list of
reserved words near the end of this article:
https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5.
The following sections describe each of the tables and columns
in INFORMATION_SCHEMA. For each column, there
are three pieces of information:
“INFORMATION_SCHEMA Name”
indicates the name for the column in the
INFORMATION_SCHEMA table. This
corresponds to the standard SQL name unless the
“Remarks” field says “MySQL
extension.”
“SHOW Name”
indicates the equivalent field name in the closest
SHOW statement, if there is
one.
“Remarks” provides additional information where
applicable. If this field is NULL, it
means that the value of the column is always
NULL. If this field says “MySQL
extension,” the column is a MySQL extension to
standard SQL.
Many sections indicate what SHOW
statement is equivalent to a
SELECT that retrieves information
from INFORMATION_SCHEMA. For
SHOW statements that display
information for the default database if you omit a FROM
clause, you can
often select information for the default database by adding an
db_nameAND TABLE_SCHEMA = SCHEMA() condition to the
WHERE clause of a query that retrieves
information from an INFORMATION_SCHEMA table.
These sections discuss additional
INFORMATION_SCHEMA-related topics:
information about INFORMATION_SCHEMA
tables specific to the InnoDB
storage engine: Section 25.39, “INFORMATION_SCHEMA InnoDB Tables”
information about INFORMATION_SCHEMA
tables specific to the thread pool plugin:
Section 25.40, “INFORMATION_SCHEMA Thread Pool Tables”
information about INFORMATION_SCHEMA
tables specific to the CONNECTION_CONTROL
plugin: Section 25.41, “INFORMATION_SCHEMA Connection-Control Tables”
Answers to questions that are often asked concerning the
INFORMATION_SCHEMA database:
Section A.7, “MySQL 8.0 FAQ: INFORMATION_SCHEMA”
INFORMATION_SCHEMA queries and the
optimizer: Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”
The effect of collation on
INFORMATION_SCHEMA comparisons:
Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”
The CHARACTER_SETS table provides
information about available character sets.
The CHARACTER_SETS table has these
columns:
CHARACTER_SET_NAME
The character set name.
DEFAULT_COLLATE_NAME
The default collation for the character set.
DESCRIPTION
A description of the character set.
MAXLEN
The maximum number of bytes required to store one character.
Character set information is also available from the
SHOW CHARACTER SET statement. See
Section 13.7.7.3, “SHOW CHARACTER SET Syntax”. The following statements are
equivalent:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS [WHERE CHARACTER_SET_NAME LIKE 'wild'] SHOW CHARACTER SET [LIKE 'wild']
The CHECK_CONSTRAINTS table
(available as of MySQL 8.0.16) provides information about
CHECK constraints defined on tables.
The CHECK_CONSTRAINTS table has these
columns:
CONSTRAINT_CATALOG
The name of the catalog to which the constraint belongs. This
value is always def.
CONSTRAINT_SCHEMA
The name of the schema (database) to which the constraint belongs.
CONSTRAINT_NAME
The name of the constraint.
CHECK_CLAUSE
The expression that specifies the constraint condition.
The COLLATIONS table provides
information about collations for each character set.
The COLLATIONS table has these
columns:
COLLATION_NAME
The collation name.
CHARACTER_SET_NAME
The name of the character set with which the collation is associated.
ID
The collation ID.
IS_DEFAULT
Whether the collation is the default for its character set.
IS_COMPILED
Whether the character set is compiled into the server.
SORTLEN
This is related to the amount of memory required to sort strings expressed in the character set.
PAD_ATTRIBUTE
The collation pad attribute.
Collation information is also available from the
SHOW COLLATION statement. See
Section 13.7.7.4, “SHOW COLLATION Syntax”. The following statements are
equivalent:
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS [WHERE COLLATION_NAME LIKE 'wild'] SHOW COLLATION [LIKE 'wild']
The
COLLATION_CHARACTER_SET_APPLICABILITY
table indicates what character set is applicable for what
collation.
The
COLLATION_CHARACTER_SET_APPLICABILITY
table has these columns:
COLLATION_NAME
The collation name.
CHARACTER_SET_NAME
The name of the character set with which the collation is associated.
The
COLLATION_CHARACTER_SET_APPLICABILITY
columns are equivalent to the first two columns displayed by the
SHOW COLLATION statement.
The COLUMNS table provides
information about columns in tables. The related
ST_GEOMETRY_COLUMNS table provides
information about table columns that store spatial data. See
Section 25.27, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”.
The COLUMNS table has these columns:
TABLE_CATALOG
The name of the catalog to which the table containing the
column belongs. This value is always def.
TABLE_SCHEMA
The name of the schema (database) to which the table containing the column belongs.
TABLE_NAME
The name of the table containing the column.
COLUMN_NAME
The name of the column.
ORDINAL_POSITION
The position of the column within the table.
ORDINAL_POSITION is necessary because you
might want to say ORDER BY
ORDINAL_POSITION. Unlike SHOW
COLUMNS, SELECT from
the COLUMNS table does not have
automatic ordering.
COLUMN_DEFAULT
The default value for the column. This is
NULL if the column has an explicit default
of NULL, or if the column definition
includes no DEFAULT clause.
IS_NULLABLE
The column nullability. The value is YES if
NULL values can be stored in the column,
NO if not.
DATA_TYPE
The column data type.
The DATA_TYPE value is the type name only
with no other information. The COLUMN_TYPE
value contains the type name and possibly other information
such as the precision or length.
CHARACTER_MAXIMUM_LENGTH
For string columns, the maximum length in characters.
CHARACTER_OCTET_LENGTH
For string columns, the maximum length in bytes.
NUMERIC_PRECISION
For numeric columns, the numeric precision.
NUMERIC_SCALE
For numeric columns, the numeric scale.
DATETIME_PRECISION
For temporal columns, the fractional seconds precision.
CHARACTER_SET_NAME
For character string columns, the character set name.
COLLATION_NAME
For character string columns, the collation name.
COLUMN_TYPE
The column data type.
The DATA_TYPE value is the type name only
with no other information. The COLUMN_TYPE
value contains the type name and possibly other information
such as the precision or length.
COLUMN_KEY
Whether the column is indexed:
If COLUMN_KEY is empty, the column
either is not indexed or is indexed only as a secondary
column in a multiple-column, nonunique index.
If COLUMN_KEY is
PRI, the column is a PRIMARY
KEY or is one of the columns in a
multiple-column PRIMARY KEY.
If COLUMN_KEY is
UNI, the column is the first column of
a UNIQUE index. (A
UNIQUE index permits multiple
NULL values, but you can tell whether
the column permits NULL by checking the
Null column.)
If COLUMN_KEY is
MUL, the column is the first column of
a nonunique index in which multiple occurrences of a given
value are permitted within the column.
If more than one of the COLUMN_KEY values
applies to a given column of a table,
COLUMN_KEY displays the one with the
highest priority, in the order PRI,
UNI, MUL.
A UNIQUE index may be displayed as
PRI if it cannot contain
NULL values and there is no
PRIMARY KEY in the table. A
UNIQUE index may display as
MUL if several columns form a composite
UNIQUE index; although the combination of
the columns is unique, each column can still hold multiple
occurrences of a given value.
EXTRA
Any additional information that is available about a given column. The value is nonempty in these cases:
auto_increment for columns that have
the AUTO_INCREMENT attribute.
on update CURRENT_TIMESTAMP for
TIMESTAMP or
DATETIME columns that have
the ON UPDATE CURRENT_TIMESTAMP
attribute.
VIRTUAL GENERATED or VIRTUAL
STORED for generated columns.
DEFAULT_GENERATED for columns that have
an expression default value.
PRIVILEGES
The privileges you have for the column.
COLUMN_COMMENT
Any comment included in the column definition.
GENERATION_EXPRESSION
For generated columns, displays the expression used to compute column values. Empty for nongenerated columns. For information about generated columns, see Section 13.1.20.9, “CREATE TABLE and Generated Columns”.
SRS_ID
This value applies to spatial columns. It contains the column
SRID value that indicates the 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.
Column information is also available from the
SHOW COLUMNS statement. See
Section 13.7.7.5, “SHOW COLUMNS Syntax”. The following statements are
nearly equivalent:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' [AND table_schema = 'db_name'] [AND column_name LIKE 'wild'] SHOW COLUMNS FROMtbl_name[FROMdb_name] [LIKE 'wild']
The COLUMN_PRIVILEGES table provides
information about column privileges. It takes its values from the
mysql.columns_priv system table.
The COLUMN_PRIVILEGES table has these
columns:
GRANTEE
The name of the account to which the privilege is granted, in
'
format.
user_name'@'host_name'
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.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES SHOW GRANTS ...
The COLUMN_STATISTICS table provides
access to histogram statistics for column values.
For information about histogram statistics, see Section 8.9.6, “Optimizer Statistics”, and Section 13.7.3.1, “ANALYZE TABLE Syntax”.
You can see information only for columns for which you have some privilege.
The COLUMN_STATISTICS table has these
columns:
SCHEMA_NAME
The names of the schema for which the statistics apply.
TABLE_NAME
The names of the column for which the statistics apply.
COLUMN_NAME
The names of the column for which the statistics apply.
HISTOGRAM
A JSON object describing the
column statistics, stored as a histogram.
The ENGINES table provides
information about storage engines. This is particularly useful for
checking whether a storage engine is supported, or to see what the
default engine is.
The ENGINES table has these columns:
ENGINE
The name of the storage engine.
SUPPORT
The server's level of support for the storage engine, as shown in the following table.
| Value | Meaning |
|---|---|
YES |
The engine is supported and is active |
DEFAULT |
Like YES, plus this is the default engine |
NO |
The engine is not supported |
DISABLED |
The engine is supported but has been disabled |
A value of NO means that the server was
compiled without support for the engine, so it cannot be
enabled at runtime.
A value of DISABLED occurs either because
the server was started with an option that disables the
engine, or because not all options required to enable it were
given. In the latter case, the error log should contain a
reason indicating why the option is disabled. See
Section 5.4.2, “The Error Log”.
You might also see DISABLED for a storage
engine if the server was compiled to support it, but was
started with a
--skip-
option. For the engine_nameNDB storage
engine, DISABLED means the server was
compiled with support for NDB Cluster, but was not started
with the --ndbcluster option.
All MySQL servers support MyISAM tables. It
is not possible to disable MyISAM.
COMMENT
A brief description of the storage engine.
TRANSACTIONS
Whether the storage engine supports transactions.
XA
Whether the storage engine supports XA transactions.
SAVEPOINTS
Whether the storage engine supports savepoints.
The ENGINES table is a
nonstandard INFORMATION_SCHEMA table.
Storage engine information is also available from the
SHOW ENGINES statement. See
Section 13.7.7.16, “SHOW ENGINES Syntax”. The following statements are
equivalent:
SELECT * FROM INFORMATION_SCHEMA.ENGINES SHOW ENGINES
The EVENTS table provides information
about Event Manager events, which are discussed in
Section 24.4, “Using the Event Scheduler”.
The EVENTS table has these columns:
EVENT_CATALOG
The name of the catalog to which the event belongs. This value
is always def.
EVENT_SCHEMA
The name of the schema (database) to which the event belongs.
EVENT_NAME
The name of the event.
DEFINER
The account of the user who created the event, in
'
format.
user_name'@'host_name'
TIME_ZONE
The event time zone, which is the time zone used for
scheduling the event and that is in effect within the event as
it executes. The default value is SYSTEM.
EVENT_BODY
The language used for the statements in the event's
DO clause. The value is always
SQL.
EVENT_DEFINITION
The text of the SQL statement making up the event's
DO clause; in other words, the
statement executed by this event.
EVENT_TYPE
The event repetition type, either ONE TIME
(transient) or RECURRING (repeating).
EXECUTE_AT
For a one-time event, this is the
DATETIME value specified in the
AT clause of the
CREATE EVENT statement used to
create the event, or of the last ALTER
EVENT statement that modified the event. The value
shown in this column reflects the addition or subtraction of
any INTERVAL value included in the event's
AT clause. For example, if an event is
created using ON SCHEDULE AT CURRENT_TIMESTAMP +
'1:6' DAY_HOUR, and the event was created at
2018-02-09 14:05:30, the value shown in this column would be
'2018-02-10 20:05:30'. If the event's
timing is determined by an EVERY clause
instead of an AT clause (that is, if the
event is recurring), the value of this column is
NULL.
INTERVAL_VALUE
For a recurring event, the number of intervals to wait between
event executions. For a transient event, the value is always
NULL.
INTERVAL_FIELD
The time units used for the interval which a recurring event
waits before repeating. For a transient event, the value is
always NULL.
SQL_MODE
The SQL mode in effect when the event was created or altered, and under which the event executes. For the permitted values, see Section 5.1.11, “Server SQL Modes”.
STARTS
The start date and time for a recurring event. This is
displayed as a DATETIME value,
and is NULL if no start date and time are
defined for the event. For a transient event, this column is
always NULL. For a recurring event whose
definition includes a STARTS clause, this
column contains the corresponding
DATETIME value. As with the
EXECUTE_AT column, this value resolves any
expressions used. If there is no STARTS
clause affecting the timing of the event, this column is
NULL
ENDS
For a recurring event whose definition includes a
ENDS clause, this column contains the
corresponding DATETIME value.
As with the EXECUTE_AT column, this value
resolves any expressions used. If there is no
ENDS clause affecting the timing of the
event, this column is NULL.
STATUS
The event status. One of ENABLED,
DISABLED, or
SLAVESIDE_DISABLED.
SLAVESIDE_DISABLED indicates that the
creation of the event occurred on another MySQL server acting
as a replication master and replicated to the current MySQL
server which is acting as a slave, but the event is not
presently being executed on the slave. For more information,
see Section 17.5.1.16, “Replication of Invoked Features”.
information.
ON_COMPLETION
One of the two values PRESERVE or
NOT PRESERVE.
CREATED
The date and time when the event was created. This is a
TIMESTAMP value.
LAST_ALTERED
The date and time when the event was last modified. This is a
TIMESTAMP value. If the event
has not been modified since its creation, this value is the
same as the CREATED value.
LAST_EXECUTED
The date and time when the event last executed. This is a
DATETIME value. If the event
has never executed, this column is NULL.
LAST_EXECUTED indicates when the event
started. As a result, the ENDS column is
never less than LAST_EXECUTED.
EVENT_COMMENT
The text of the comment, if the event has one. If not, this value is empty.
ORIGINATOR
The server ID of the MySQL server on which the event was
created; used in replication. This value may be updated by
ALTER EVENT to the server ID of
the server on which that statement occurs, if executed on a
master server. The default value is 0.
CHARACTER_SET_CLIENT
The session value of the
character_set_client system
variable when the event was created.
COLLATION_CONNECTION
The session value of the
collation_connection system
variable when the event was created.
DATABASE_COLLATION
The collation of the database with which the event is associated.
The EVENTS table is a nonstandard
INFORMATION_SCHEMA table.
Times in the EVENTS table are
displayed using the event time zone, the current session time
zone, or UTC, as described in
Section 24.4.4, “Event Metadata”.
For more information about
SLAVESIDE_DISABLED and the
ORIGINATOR column, see
Section 17.5.1.16, “Replication of Invoked Features”.
Suppose that the user 'jon'@'ghidora' creates
an event named e_daily, and then modifies it a
few minutes later using an ALTER
EVENT statement, as shown here:
DELIMITER |
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |
DELIMITER ;
ALTER EVENT e_daily
ENABLE;
(Note that comments can span multiple lines.)
This user can then run the following
SELECT statement, and obtain the
output shown:
mysql>SELECT * FROM INFORMATION_SCHEMA.EVENTSWHERE EVENT_NAME = 'e_daily'AND EVENT_SCHEMA = 'myschema'\G*************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: myschema EVENT_NAME: e_daily DEFINER: jon@ghidora TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: DAY SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION STARTS: 2018-08-08 11:06:34 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2018-08-08 11:06:34 LAST_ALTERED: 2018-08-08 11:06:34 LAST_EXECUTED: 2018-08-08 16:06:34 EVENT_COMMENT: Saves total number of sessions then clears the table each day ORIGINATOR: 1 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci
Event information is also available from the
SHOW EVENTS statement. See
Section 13.7.7.18, “SHOW EVENTS Syntax”. The following statements are
equivalent:
SELECT
EVENT_SCHEMA, EVENT_NAME, DEFINER, TIME_ZONE, EVENT_TYPE, EXECUTE_AT,
INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ORIGINATOR,
CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION
FROM INFORMATION_SCHEMA.EVENTS
WHERE table_schema = 'db_name'
[AND column_name LIKE 'wild']
SHOW EVENTS
[FROM db_name]
[LIKE 'wild']
The FILES table provides information
about the files in which MySQL tablespace data is stored.
The FILES table provides information
about InnoDB data files. In NDB Cluster, this
table also provides information about the files in which NDB
Cluster Disk Data tables are stored. For additional information
specific to InnoDB, see
InnoDB Notes, later in this section;
for additional information specific to NDB Cluster, see
NDB Notes.
The FILES table has these columns:
FILE_ID
For InnoDB: The tablespace ID, also
referred to as the space_id or
fil_space_t::id.
For NDB: A file identifier.
FILE_ID column values are auto-generated.
FILE_NAME
For InnoDB: The name of the data file.
File-per-table and general tablespaces have an
.ibd file name extension. Undo
tablespaces are prefixed by undo. The
system tablespace is prefixed by ibdata.
The global temporary tablespace is prefixed by
ibtmp. The file name includes the file
path, which may be relative to the MySQL data directory (the
value of the datadir system
variable).
For NDB: The name of an
UNDO log file created by
CREATE LOGFILE GROUP or
ALTER LOGFILE GROUP, or of a
data file created by CREATE
TABLESPACE or ALTER
TABLESPACE.
FILE_TYPE
For InnoDB: The tablespace file type. There
are three possible file types for InnoDB
files. TABLESPACE is the file type for any
system, general, or file-per-table tablespace file that holds
tables, indexes, or other forms of user data.
TEMPORARY is the file type for temporary
tablespaces. UNDO LOG is the file type for
undo tablespaces, which hold undo records.
For NDB: One of the values UNDO
LOG, DATAFILE, or
TABLESPACE.
TABLESPACE_NAME
For InnoDB: The SQL name for the
tablespace. A general tablespace name is the
SYS_TABLESPACES.NAME value. For other
tablespace files, names start with innodb_,
such as innodb_system,
innodb_undo, and
innodb_file_per_table. The file-per-table
tablespace name format is
innodb_file_per_table_,
where #### is the tablespace ID.
For NDB: The name of the tablespace with
which the file is associated.
TABLE_CATALOG
This value is always empty.
TABLE_SCHEMA
This value is always NULL.
TABLE_NAME
For InnoDB: This value is always
NULL.
For NDB: The name of the Disk Data table
with which the file is associated, if any.
LOGFILE_GROUP_NAME
For InnoDB: This value is always
NULL.
For NDB: The name of the log file group to
which the log file or data file belongs.
LOGFILE_GROUP_NUMBER
For InnoDB: This value is always
NULL.
For NDB: For an UNDO log
file, the auto-generated ID number of the log file group to
which the log file belongs.
ENGINE
For InnoDB: This value is always
InnoDB.
For NDB: For an NDB Cluster Disk Data log
file or data file, this value is always NDB
or NDBCLUSTER.
FULLTEXT_KEYS
For InnoDB: This value is always
NULL.
For NDB: For an NDB Cluster Disk Data log
file or data file, this value is always empty.
DELETED_ROWS
For InnoDB: This value is always
NULL.
UPDATE_COUNT
For InnoDB: This value is always
NULL.
FREE_EXTENTS
For InnoDB: The number of fully free
extents in the current data file.
For NDB: The number of extents which have
not yet been used by the file.
TOTAL_EXTENTS
For InnoDB: The number of full extents used
in the current data file. Any partial extent at the end of the
file is not counted.
For NDB: The total number of extents
allocated to the file.
EXTENT_SIZE
For InnoDB: Extent size is 1048576 (1MB)
for files with a 4KB, 8KB, or 16KB page size. Extent size is
2097152 bytes (2MB) for files with a 32KB page size, and
4194304 (4MB) for files with a 64KB page size.
FILES does not report
InnoDB page size. Page size is defined by
the innodb_page_size system
variable. Extent size information can also be retrieved from
the INNODB_TABLESPACES table
where FILES.FILE_ID =
INNODB_TABLESPACES.SPACE.
For NDB: The size of an extent for the file
in bytes.
INITIAL_SIZE
For InnoDB: The initial size of the file in
bytes.
For NDB: The size of the file in bytes.
This is the same value that was used in the
INITIAL_SIZE clause of the
CREATE LOGFILE GROUP,
ALTER LOGFILE GROUP,
CREATE TABLESPACE, or
ALTER TABLESPACE statement used
to create the file.
MAXIMUM_SIZE
For InnoDB: The maximum number of bytes
permitted in the file. The value is NULL
for all data files except for predefined system tablespace
data files. Maximum system tablespace file size is defined by
innodb_data_file_path.
Maximum global temporary tablespace file size is defined by
innodb_temp_data_file_path. A
NULL value for a predefined system
tablespace data file indicates that a file size limit was not
defined explicitly.
For NDB: For NDB Cluster Disk Data files,
this value is always the same as the
INITIAL_SIZE value.
AUTOEXTEND_SIZE
For InnoDB:
AUTOEXTEND_SIZE is the auto-extend size
defined by
innodb_data_file_path for the
system tablespace, or by
innodb_temp_data_file_path
for the global temporary tablespace.
For NDB: For NDB Cluster Disk Data files,
this value is always empty.
CREATION_TIME
For InnoDB: This value is always
NULL.
For NDB: The date and time when the file
was created.
LAST_UPDATE_TIME
For InnoDB: This value is always
NULL.
For NDB: The date and time when the file
was last modified.
LAST_ACCESS_TIME
For InnoDB: This value is always
NULL.
For NDB: The date and time when the file
was last accessed by the server.
RECOVER_TIME
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always 0.
TRANSACTION_COUNTER
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always 0.
VERSION
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
ROW_FORMAT
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
TABLE_ROWS
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
AVG_ROW_LENGTH
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
DATA_LENGTH
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
MAX_DATA_LENGTH
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
INDEX_LENGTH
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
DATA_FREE
For InnoDB: The total amount of free space
(in bytes) for the entire tablespace. Predefined system
tablespaces, which include the system tablespace and temporary
table tablespaces, may have one or more data files.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
CREATE_TIME
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
UPDATE_TIME
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
CHECK_TIME
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
CHECKSUM
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
this value is always NULL.
STATUS
For InnoDB: This value is
NORMAL by default.
InnoDB file-per-table tablespaces may
report IMPORTING, which indicates that the
tablespace is not yet available.
For NDB: For NDB Cluster Disk Data files,
this value is always NORMAL.
EXTRA
For InnoDB: This value is always
NULL.
For NDB: For NDB Cluster Disk Data files,
the EXTRA column shows which data node the
file belongs to (each data node having its own copy), as well
as the size of its undo buffer. Suppose that you use this
statement on an NDB Cluster with four data nodes:
CREATE LOGFILE GROUP mygroup
ADD UNDOFILE 'new_undo.dat'
INITIAL_SIZE 2G
ENGINE NDB;
After running the CREATE LOGFILE
GROUP statement successfully, you should see a
result similar to the one shown here for this query against
the FILES table:
mysql>SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRAFROM INFORMATION_SCHEMA.FILESWHERE FILE_NAME = 'new_undo.dat';+--------------------+-----------+-----------------------------------------+ | LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA | +--------------------+-----------+-----------------------------------------+ | mygroup | UNDO LOG | CLUSTER_NODE=5;UNDO_BUFFER_SIZE=8388608 | | mygroup | UNDO LOG | CLUSTER_NODE=6;UNDO_BUFFER_SIZE=8388608 | | mygroup | UNDO LOG | CLUSTER_NODE=7;UNDO_BUFFER_SIZE=8388608 | | mygroup | UNDO LOG | CLUSTER_NODE=8;UNDO_BUFFER_SIZE=8388608 | +--------------------+-----------+-----------------------------------------+
This information was not included in NDB Cluster 8.0 prior to NDB 8.0.15. (Bug #92796, Bug #28800252)
The FILES table is a
nonstandard INFORMATION_SCHEMA table.
The following notes apply to InnoDB data
files.
Data reported by FILES is
reported from the InnoDB in-memory cache
for open files. By comparison,
INNODB_DATAFILES reports data
from the InnoDB
SYS_DATAFILES internal data dictionary
table.
The data reported by FILES
includes global temporary tablespace data. This data is not
available in the InnoDB
SYS_DATAFILES internal data dictionary
table, and is therefore not reported by
INNODB_DATAFILES.
Undo tablespace data is reported by
FILES when separate undo
tablespaces are present, which they are by default in MySQL
8.0
The following query returns all data pertinent to
InnoDB tablespaces.
SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS FROM INFORMATION_SCHEMA.FILES WHERE ENGINE='InnoDB'\G
The FILES table provides information
about Disk Data files only; you cannot
use it for determining disk space allocation or availability
for individual NDB tables. However, it is
possible to see how much space is allocated for each
NDB table having data stored on
disk—as well as how much remains available for storage
of data on disk for that table—using
ndb_desc. For more information, see
Section 22.4.9, “ndb_desc — Describe NDB Tables”.
The CREATION_TIME,
LAST_UPDATE_TIME, and
LAST_ACCESSED values are as reported by
the operating system, and are not supplied by the
NDB storage engine. Where no
value is provided by the operating system, these columns
display 0000-00-00 00:00:00.
The difference between the TOTAL EXTENTS
and FREE_EXTENTS columns is the number of
extents currently in use by the file:
SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME = 'myfile.dat';
To approximate the amount of disk space in use by the file,
multiply that difference by the value of the
EXTENT_SIZE column, which gives the size
of an extent for the file in bytes:
SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME = 'myfile.dat';
Similarly, you can estimate the amount of space that remains
available in a given file by multiplying
FREE_EXTENTS by
EXTENT_SIZE:
SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME = 'myfile.dat';
The byte values produced by the preceding queries are
approximations only, and their precision is inversely
proportional to the value of
EXTENT_SIZE. That is, the larger
EXTENT_SIZE becomes, the less accurate
the approximations are.
It is also important to remember that once an extent is used, it cannot be freed again without dropping the data file of which it is a part. This means that deletes from a Disk Data table do not release disk space.
The extent size can be set in a CREATE
TABLESPACE statement. For more information, see
Section 13.1.21, “CREATE TABLESPACE Syntax”.
An additional row is present in the
FILES table following the
creation of a logfile group. This row has
NULL for the value of the
FILE_NAME column. For this row, the value
of the FILE_ID column is always
0, that of the
FILE_TYPE column is always UNDO
FILE, and that of the STATUS
column is always NORMAL. The value of the
ENGINE column is always
NDBCLUSTER.
The FREE_EXTENTS column in this row shows
the total number of free extents available to all undo files
belonging to a given log file group whose name and number
are shown in the LOGFILE_GROUP_NAME and
LOGFILE_GROUP_NUMBER columns,
respectively.
Suppose there are no existing log file groups on your NDB Cluster, and you create one using the following statement:
mysql>CREATE LOGFILE GROUP lg1ADD UNDOFILE 'undofile.dat'INITIAL_SIZE = 16MUNDO_BUFFER_SIZE = 1MENGINE = NDB;
You can now see this NULL row when you
query the FILES table:
mysql>SELECT DISTINCTFILE_NAME AS File,FREE_EXTENTS AS Free,TOTAL_EXTENTS AS Total,EXTENT_SIZE AS Size,INITIAL_SIZE AS InitialFROM INFORMATION_SCHEMA.FILES;+--------------+---------+---------+------+----------+ | File | Free | Total | Size | Initial | +--------------+---------+---------+------+----------+ | undofile.dat | NULL | 4194304 | 4 | 16777216 | | NULL | 4184068 | NULL | 4 | NULL | +--------------+---------+---------+------+----------+
The total number of free extents available for undo logging
is always somewhat less than the sum of the
TOTAL_EXTENTS column values for all undo
files in the log file group due to overhead required for
maintaining the undo files. This can be seen by adding a
second undo file to the log file group, then repeating the
previous query against the
FILES table:
mysql>ALTER LOGFILE GROUP lg1ADD UNDOFILE 'undofile02.dat'INITIAL_SIZE = 4MENGINE = NDB;mysql>SELECT DISTINCTFILE_NAME AS File,FREE_EXTENTS AS Free,TOTAL_EXTENTS AS Total,EXTENT_SIZE AS Size,INITIAL_SIZE AS InitialFROM INFORMATION_SCHEMA.FILES;+----------------+---------+---------+------+----------+ | File | Free | Total | Size | Initial | +----------------+---------+---------+------+----------+ | undofile.dat | NULL | 4194304 | 4 | 16777216 | | undofile02.dat | NULL | 1048576 | 4 | 4194304 | | NULL | 5223944 | NULL | 4 | NULL | +----------------+---------+---------+------+----------+
The amount of free space in bytes which is available for undo logging by Disk Data tables using this log file group can be approximated by multiplying the number of free extents by the initial size:
mysql>SELECTFREE_EXTENTS AS 'Free Extents',FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'FROM INFORMATION_SCHEMA.FILESWHERE LOGFILE_GROUP_NAME = 'lg1'AND FILE_NAME IS NULL;+--------------+------------+ | Free Extents | Free Bytes | +--------------+------------+ | 5223944 | 20895776 | +--------------+------------+
If you create an NDB Cluster Disk Data table and then insert some rows into it, you can see approximately how much space remains for undo logging afterward, for example:
mysql>CREATE TABLESPACE ts1ADD DATAFILE 'data1.dat'USE LOGFILE GROUP lg1INITIAL_SIZE 512MENGINE = NDB;mysql>CREATE TABLE dd (c1 INT NOT NULL PRIMARY KEY,c2 INT,c3 DATE)TABLESPACE ts1 STORAGE DISKENGINE = NDB;mysql>INSERT INTO dd VALUES(NULL, 1234567890, '2007-02-02'),(NULL, 1126789005, '2007-02-03'),(NULL, 1357924680, '2007-02-04'),(NULL, 1642097531, '2007-02-05');mysql>SELECTFREE_EXTENTS AS 'Free Extents',FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'FROM INFORMATION_SCHEMA.FILESWHERE LOGFILE_GROUP_NAME = 'lg1'AND FILE_NAME IS NULL;+--------------+------------+ | Free Extents | Free Bytes | +--------------+------------+ | 5207565 | 20830260 | +--------------+------------+
An additional row is present in the
FILES table for any NDB Cluster
tablespace, whether or not any data files are associated
with the tablespace. This row has NULL
for the value of the FILE_NAME column.
For this row, the value of the FILE_ID
column is always 0, that of the
FILE_TYPE column is always
TABLESPACE, and that of the
STATUS column is always
NORMAL. The value of the
ENGINE column is always
NDBCLUSTER.
For additional information, and examples of creating and dropping NDB Cluster Disk Data objects, see Section 22.5.13, “NDB Cluster Disk Data Tables”.
The KEY_COLUMN_USAGE table describes
which key columns have constraints. This table provides no
information about functional key parts because they are
expressions and the table provides information only about columns.
The KEY_COLUMN_USAGE table has these
columns:
CONSTRAINT_CATALOG
The name of the catalog to which the constraint belongs. This
value is always def.
CONSTRAINT_SCHEMA
The name of the schema (database) to which the constraint belongs.
CONSTRAINT_NAME
The name of the constraint.
TABLE_CATALOG
The name of the catalog to which the table belongs. This value
is always def.
TABLE_SCHEMA
The name of the schema (database) to which the table belongs.
TABLE_NAME
The name of the table that has the constraint.
COLUMN_NAME
The name of the column that has the constraint.
If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.
ORDINAL_POSITION
The column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.
POSITION_IN_UNIQUE_CONSTRAINT
NULL for unique and primary-key
constraints. For foreign-key constraints, this column is the
ordinal position in key of the table that is being referenced.
REFERENCED_TABLE_SCHEMA
The name of the schema referenced by the constraint.
REFERENCED_TABLE_NAME
The name of the table referenced by the constraint.
REFERENCED_COLUMN_NAME
The name of the column referenced by the constraint.
Suppose that there are two tables name t1 and
t3 that have the following definitions:
CREATE TABLE t1
(
s1 INT,
s2 INT,
s3 INT,
PRIMARY KEY(s3)
) ENGINE=InnoDB;
CREATE TABLE t3
(
s1 INT,
s2 INT,
s3 INT,
KEY(s1),
CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;
For those two tables, the
KEY_COLUMN_USAGE table has two rows:
One row with CONSTRAINT_NAME =
'PRIMARY', TABLE_NAME =
't1', COLUMN_NAME =
's3', ORDINAL_POSITION =
1,
POSITION_IN_UNIQUE_CONSTRAINT =
NULL.
For NDB: This value is always
NULL.
One row with CONSTRAINT_NAME =
'CO', TABLE_NAME =
't3', COLUMN_NAME =
's2', ORDINAL_POSITION =
1,
POSITION_IN_UNIQUE_CONSTRAINT =
1.
The ndb_transid_mysql_connection_map table
provides a mapping between NDB transactions,
NDB transaction coordinators, and MySQL Servers
attached to an NDB Cluster as API nodes. This information is used
when populating the
server_operations and
server_transactions tables of
the ndbinfo NDB Cluster
information database.
INFORMATION_SCHEMA Name |
SHOW Name |
Remarks |
|---|---|---|
mysql_connection_id |
MySQL Server connection ID | |
node_id |
Transaction coordinator node ID | |
ndb_transid |
NDB transaction ID |
The mysql_connection_id is the same as the
connection or session ID shown in the output of
SHOW PROCESSLIST.
There are no SHOW statements associated with
this table.
This is a nonstandard table, specific to NDB Cluster. It is
implemented as an INFORMATION_SCHEMA plugin;
you can verify that it is supported by checking the output of
SHOW PLUGINS. If
ndb_transid_mysql_connection_map support is
enabled, the output from this statement includes a plugin having
this name, of type INFORMATION SCHEMA, and
having status ACTIVE, as shown here (using
emphasized text):
mysql> SHOW PLUGINS;
+----------------------------------+--------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+--------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbcluster | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbinfo | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndb_transid_mysql_connection_map | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+----------------------------------+--------+--------------------+---------+---------+
22 rows in set (0.00 sec)
The plugin is enabled by default. You can disable it (or force the
server not to run unless the plugin starts) by starting the server
with the
--ndb-transid-mysql-connection-map
option. If the plugin is disabled, the status is shown by
SHOW PLUGINS as
DISABLED. The plugin cannot be enabled or
disabled at runtime.
Although the names of this table and its columns are displayed using lowercase, you can use uppercase or lowercase when referring to them in SQL statements.
For this table to be created, the MySQL Server must be a binary
supplied with the NDB Cluster distribution, or one built from the
NDB Cluster sources with NDB storage
engine support enabled. It is not available in the standard MySQL
8.0 Server.
The KEYWORDS table lists the words
considered keywords by MySQL and, for each one, indicates whether
it is reserved. Reserved keywords may require special treatment in
some contexts, such as special quoting when used as identifiers
(see Section 9.3, “Keywords and Reserved Words”). This table provides applications
a runtime source of MySQL keyword information.
Prior to MySQL 8.0.13, selecting from the
KEYWORDS table with no default database
selected produced an error. (Bug #90160, Bug #27729859)
The KEYWORDS table has these columns:
WORD
The keyword.
RESERVED
An integer indicating whether the keyword is reserved (1) or nonreserved (0).
These queries lists all keywords, all reserved keywords, and all nonreserved keywords, respectively:
SELECT * FROM INFORMATION_SCHEMA.KEYWORDS; SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED = 1; SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED = 0;
The latter two queries are equivalent to:
SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED; SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE NOT RESERVED;
If you build MySQL from source, the build process generates a
keyword_list.h header file containing an
array of keywords and their reserved status. This file can be
found in the sql directory under the build
directory. This file may be useful for applications that require a
static source for the keyword list.
The OPTIMIZER_TRACE table provides
information produced by the optimizer tracing capability for
traced statements. To enable tracking, use the
optimizer_trace system variable.
For details, see
MySQL
Internals: Tracing the Optimizer.
The OPTIMIZER_TRACE table has these
columns:
QUERY
The text of the traced statement.
TRACE
The trace, in JSON format.
MISSING_BYTES_BEYOND_MAX_MEM_SIZE
Each remembered trace is a string that is extended as
optimization progresses and appends data to it. The
optimizer_trace_max_mem_size
variable sets a limit on the total amount of memory used by
all currently remembered traces. If this limit is reached, the
current trace is not extended (and thus is incomplete), and
the MISSING_BYTES_BEYOND_MAX_MEM_SIZE
column shows the number of bytes missing from the trace.
INSUFFICIENT_PRIVILEGES
If a traced query uses views or stored routines that have
SQL SECURITY with a value of
DEFINER, it may be that a user other than
the definer is denied from seeing the trace of the query. In
that case, the trace is shown as empty and
INSUFFICIENT_PRIVILEGES has a value of 1.
Otherwise, the value is 0.
The PARAMETERS table provides
information about parameters for stored routines (stored
procedures and stored functions), and about return values for
stored functions. The PARAMETERS
table does not include built-in SQL functions or user-defined
functions (UDFs).
The PARAMETERS table has these
columns:
SPECIFIC_CATALOG
The name of the catalog to which the routine containing the
parameter belongs. This value is always
def.
SPECIFIC_SCHEMA
The name of the schema (database) to which the routine containing the parameter belongs.
SPECIFIC_NAME
The name of the routine containing the parameter.
ORDINAL_POSITION
For successive parameters of a stored procedure or function,
the ORDINAL_POSITION values are 1, 2, 3,
and so forth. For a stored function, there is also a row that
applies to the function return value (as described by the
RETURNS clause). The return value is not a
true parameter, so the row that describes it has these unique
characteristics:
The ORDINAL_POSITION value is 0.
The PARAMETER_NAME and
PARAMETER_MODE values are
NULL because the return value has no
name and the mode does not apply.
PARAMETER_MODE
The mode of the parameter. This value is one of
IN, OUT, or
INOUT. For a stored function return value,
this value is NULL.
PARAMETER_NAME
The name of the parameter. For a stored function return value,
this value is NULL.
DATA_TYPE
The parameter data type.
The DATA_TYPE value is the type name only
with no other information. The
DTD_IDENTIFIER value contains the type name
and possibly other information such as the precision or
length.
CHARACTER_MAXIMUM_LENGTH
For string parameters, the maximum length in characters.
CHARACTER_OCTET_LENGTH
For string parameters, the maximum length in bytes.
NUMERIC_PRECISION
For numeric parameters, the numeric precision.
NUMERIC_SCALE
For numeric parameters, the numeric scale.
DATETIME_PRECISION
For temporal parameters, the fractional seconds precision.
CHARACTER_SET_NAME
For character string parameters, the character set name.
COLLATION_NAME
For character string parameters, the collation name.
DTD_IDENTIFIER
The parameter data type.
The DATA_TYPE value is the type name only
with no other information. The
DTD_IDENTIFIER value contains the type name
and possibly other information such as the precision or
length.
ROUTINE_TYPE
PROCEDURE for stored procedures,
FUNCTION for stored functions.
The PARTITIONS table provides
information about table partitions. Each row in this table
corresponds to an individual partition or subpartition of a
partitioned table. For more information about partitioning tables,
see Chapter 23, Partitioning.
The PARTITIONS table has these
columns:
TABLE_CATALOG
The name of the catalog to which the table belongs. This value
is always def.
TABLE_SCHEMA
The name of the schema (database) to which the table belongs.
TABLE_NAME
The name of the table containing the partition.
PARTITION_NAME
The name of the partition.
SUBPARTITION_NAME
If the PARTITIONS table row
represents a subpartition, the name of subpartition; otherwise
NULL.
For NDB: This value is always
NULL.
PARTITION_ORDINAL_POSITION
All partitions are indexed in the same order as they are
defined, with 1 being the number assigned
to the first partition. The indexing can change as partitions
are added, dropped, and reorganized; the number shown is this
column reflects the current order, taking into account any
indexing changes.
SUBPARTITION_ORDINAL_POSITION
Subpartitions within a given partition are also indexed and reindexed in the same manner as partitions are indexed within a table.
PARTITION_METHOD
One of the values RANGE,
LIST, HASH,
LINEAR HASH, KEY, or
LINEAR KEY; that is, one of the available
partitioning types as discussed in
Section 23.2, “Partitioning Types”.
SUBPARTITION_METHOD
One of the values HASH, LINEAR
HASH, KEY, or LINEAR
KEY; that is, one of the available subpartitioning
types as discussed in
Section 23.2.6, “Subpartitioning”.
PARTITION_EXPRESSION
The expression for the partitioning function used in the
CREATE TABLE or
ALTER TABLE statement that
created the table's current partitioning scheme.
For example, consider a partitioned table created in the
test database using this statement:
CREATE TABLE tp (
c1 INT,
c2 INT,
c3 VARCHAR(25)
)
PARTITION BY HASH(c1 + c2)
PARTITIONS 4;
The PARTITION_EXPRESSION column in a
PARTITIONS table row for a
partition from this table displays c1 + c2,
as shown here:
mysql>SELECT DISTINCT PARTITION_EXPRESSIONFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';+----------------------+ | PARTITION_EXPRESSION | +----------------------+ | c1 + c2 | +----------------------+
SUBPARTITION_EXPRESSION
This works in the same fashion for the subpartitioning
expression that defines the subpartitioning for a table as
PARTITION_EXPRESSION does for the
partitioning expression used to define a table's partitioning.
If the table has no subpartitions, this column is
NULL.
PARTITION_DESCRIPTION
This column is used for RANGE and LIST partitions. For a
RANGE partition, it contains the value set
in the partition's VALUES LESS THAN clause,
which can be either an integer or MAXVALUE.
For a LIST partition, this column contains
the values defined in the partition's VALUES
IN clause, which is a list of comma-separated
integer values.
For partitions whose PARTITION_METHOD is
other than RANGE or
LIST, this column is always
NULL.
TABLE_ROWS
The number of table rows in the partition.
For partitioned InnoDB tables,
the row count given in the TABLE_ROWS
column is only an estimated value used in SQL optimization,
and may not always be exact.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
AVG_ROW_LENGTH
The average length of the rows stored in this partition or
subpartition, in bytes. This is the same as
DATA_LENGTH divided by
TABLE_ROWS.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
DATA_LENGTH
The total length of all rows stored in this partition or subpartition, in bytes; that is, the total number of bytes stored in the partition or subpartition.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
MAX_DATA_LENGTH
The maximum number of bytes that can be stored in this partition or subpartition.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
INDEX_LENGTH
The length of the index file for this partition or subpartition, in bytes.
For partitions of NDB tables,
whether the tables use implicit or explicit partitioning, the
INDEX_LENGTH column value is always 0.
However, you can obtain equivalent information using the
ndb_desc utility.
DATA_FREE
The number of bytes allocated to the partition or subpartition but not used.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
CREATE_TIME
The time that the partition or subpartition was created.
UPDATE_TIME
The time that the partition or subpartition was last modified.
CHECK_TIME
The last time that the table to which this partition or subpartition belongs was checked.
For partitioned InnoDB tables,
the value is always NULL.
CHECKSUM
The checksum value, if any; otherwise NULL.
PARTITION_COMMENT
The text of the comment, if the partition has one. If not, this value is empty.
The maximum length for a partition comment is defined as 1024
characters, and the display width of the
PARTITION_COMMENT column is also 1024,
characters to match this limit.
NODEGROUP
This is the nodegroup to which the partition belongs. This is
relevant only to NDB Cluster tables; otherwise, the value is
always 0.
TABLESPACE_NAME
The name of the tablespace to which the partition belongs. The
value is always DEFAULT, unless the table
uses the NDB storage engine (see the
Notes at the end of this section).
The PARTITIONS table is a
nonstandard INFORMATION_SCHEMA table.
A table using any storage engine other than
NDB and which is not partitioned
has one row in the PARTITIONS
table. However, the values of the
PARTITION_NAME,
SUBPARTITION_NAME,
PARTITION_ORDINAL_POSITION,
SUBPARTITION_ORDINAL_POSITION,
PARTITION_METHOD,
SUBPARTITION_METHOD,
PARTITION_EXPRESSION,
SUBPARTITION_EXPRESSION, and
PARTITION_DESCRIPTION columns are all
NULL. Also, the
PARTITION_COMMENT column in this case is
blank.
An NDB table which is not explicitly
partitioned has one row in the PARTITIONS
table for each data node in the NDB cluster. For each such
row:
The SUBPARTITION_NAME,
SUBPARTITION_ORDINAL_POSITION,
SUBPARTITION_METHOD,
PARTITION_EXPRESSION,
SUBPARTITION_EXPRESSION,
CREATE_TIME,
UPDATE_TIME,
CHECK_TIME,
CHECKSUM, and
TABLESPACE_NAME columns are all
NULL.
The PARTITION_METHOD is always
AUTO.
The NODEGROUP column is
default.
The PARTITION_EXPRESSION and
PARTITION_COMMENT columns are empty.
The PLUGINS table provides
information about server plugins.
The PLUGINS table has these columns:
PLUGIN_NAME
The name used to refer to the plugin in statements such as
INSTALL PLUGIN and
UNINSTALL PLUGIN.
PLUGIN_VERSION
The version from the plugin's general type descriptor.
PLUGIN_STATUS
The plugin status, one of ACTIVE,
INACTIVE, DISABLED,
DELETING, or DELETED.
PLUGIN_TYPE
The type of plugin, such as STORAGE ENGINE,
INFORMATION_SCHEMA, or
AUTHENTICATION.
PLUGIN_TYPE_VERSION
The version from the plugin's type-specific descriptor.
PLUGIN_LIBRARY
The name of the plugin shared library file. This is the name
used to refer to the plugin file in statements such as
INSTALL PLUGIN and
UNINSTALL PLUGIN. This file is
located in the directory named by the
plugin_dir system variable.
If the library name is NULL, the plugin is
compiled in and cannot be uninstalled with
UNINSTALL PLUGIN.
PLUGIN_LIBRARY_VERSION
The plugin API interface version.
PLUGIN_AUTHOR
The plugin author.
PLUGIN_DESCRIPTION
A short description of the plugin.
PLUGIN_LICENSE
How the plugin is licensed (for example,
GPL).
LOAD_OPTION
How the plugin was loaded. The value is
OFF, ON,
FORCE, or
FORCE_PLUS_PERMANENT. See
Section 5.6.1, “Installing and Uninstalling Plugins”.
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 29.2, “The MySQL Plugin API”.
Plugin information is also available from the
SHOW PLUGINS statement. See
Section 13.7.7.25, “SHOW PLUGINS Syntax”. These statements are equivalent:
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_LICENSE FROM INFORMATION_SCHEMA.PLUGINS; SHOW PLUGINS;
The PROCESSLIST table provides
information about which threads are running.
The PROCESSLIST table has these
columns:
ID
The connection identifier. This is the same type of value
displayed in the Id column of the
SHOW PROCESSLIST statement, the
PROCESSLIST_ID column of the Performance
Schema threads table, and
returned by the CONNECTION_ID()
function.
USER
The MySQL user who issued the statement. A value of
system user refers to a nonclient thread
spawned by the server to handle tasks internally. This could
be the I/O or SQL thread used on replication slaves or a
delayed-row handler. For system user, there
is no host specified in the Host column.
unauthenticated user refers to a thread
that has become associated with a client connection but for
which authentication of the client user has not yet been done.
event_scheduler refers to the thread that
monitors scheduled events (see
Section 24.4, “Using the Event Scheduler”).
A USER value of system
user is distinct from the
SYSTEM_USER privilege. The
former designates internal threads. The latter distinguishes
the system user and regular user account categories (see
Section 6.2.11, “Account Categories”).
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
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_ commands
of the client/server protocol and
xxxCom_ status
variables. See Section 5.1.10, “Server Status Variables”
xxx
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 shows
information only about your own threads, unless you have the
PROCESS privilege, in which
case you will see information about other threads, too. As an
anonymous user, you cannot see any rows at all.
If an SQL statement refers to the
PROCESSLIST table, MySQL
populates the entire table once, when statement execution
begins, so there is read consistency during the statement.
There is no read consistency for a multi-statement
transaction.
Process information is also available from the mysqladmin
processlist command, the SHOW
PROCESSLIST statement, and the Performance Schema
threads table (see
Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”, Section 13.7.7.29, “SHOW PROCESSLIST Syntax”,
and Section 26.12.18.5, “The threads Table”). In contrast to the
INFORMATION_SCHEMA
PROCESSLIST table and
SHOW PROCESSLIST statement, which
have negative performance consequences because they require a
mutex, access to threads does not
require a mutex and has minimal impact on server performance. The
threads table also shows information
about background threads, which the
PROCESSLIST table and
SHOW PROCESSLIST do not. This means
that threads can be used to monitor
activity the other thread information sources cannot.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST SHOW FULL PROCESSLIST
The PROFILING table provides
statement profiling information. Its contents correspond to the
information produced by the SHOW
PROFILE and SHOW PROFILES
statements (see Section 13.7.7.30, “SHOW PROFILE Syntax”). The table is
empty unless the profiling
session variable is set to 1.
This table is deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Section 26.19.1, “Query Profiling Using Performance Schema”.
The PROFILING table has these
columns:
QUERY_ID
A numeric statement identifier.
SEQ
A sequence number indicating the display order for rows with
the same QUERY_ID value.
STATE
The profiling state to which the row measurements apply.
DURATION
How long statement execution remained in the given state, in seconds.
CPU_USER, CPU_SYSTEM
User and system CPU use, in seconds.
CONTEXT_VOLUNTARY,
CONTEXT_INVOLUNTARY
How many voluntary and involuntary context switches occurred.
BLOCK_OPS_IN,
BLOCK_OPS_OUT
The number of block input and output operations.
MESSAGES_SENT,
MESSAGES_RECEIVED
The number of communication messages sent and received.
PAGE_FAULTS_MAJOR,
PAGE_FAULTS_MINOR
The number of major and minor page faults.
SWAPS
How many swaps occurred.
SOURCE_FUNCTION,
SOURCE_FILE, and
SOURCE_LINE
Information indicating where in the source code the profiled state executes.
The PROFILING table is a
nonstandard INFORMATION_SCHEMA table.
Profiling information is also available from the
SHOW PROFILE and
SHOW PROFILES statements. See
Section 13.7.7.30, “SHOW PROFILE Syntax”. For example, the following queries
are equivalent:
SHOW PROFILE FOR QUERY 2; SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;
The REFERENTIAL_CONSTRAINTS table
provides information about foreign keys.
The REFERENTIAL_CONSTRAINTS table has
these columns:
CONSTRAINT_CATALOG
The name of the catalog to which the constraint belongs. This
value is always def.
CONSTRAINT_SCHEMA
The name of the schema (database) to which the constraint belongs.
CONSTRAINT_NAME
The name of the constraint.
UNIQUE_CONSTRAINT_CATALOG
The name of the catalog containing the unique constraint that
the constraint references. This value is always
def.
UNIQUE_CONSTRAINT_SCHEMA
The name of the schema containing the unique constraint that the constraint references.
UNIQUE_CONSTRAINT_NAME
The name of the unique constraint that the constraint references.
MATCH_OPTION
The value of the constraint MATCH
attribute. The only valid value at this time is
NONE.
UPDATE_RULE
The value of the constraint ON UPDATE
attribute. The possible values are CASCADE,
SET NULL, SET DEFAULT,
RESTRICT, NO ACTION.
DELETE_RULE
The value of the constraint ON DELETE
attribute. The possible values are CASCADE,
SET NULL, SET DEFAULT,
RESTRICT, NO ACTION.
TABLE_NAME
The name of the table. This value is the same as in the
TABLE_CONSTRAINTS table.
REFERENCED_TABLE_NAME
The name of the table referenced by the constraint.
The RESOURCE_GROUPS table provides
access to information about resource groups. For general
discussion of the resource group capability, see
Section 8.12.5, “Resource Groups”.
You can see information only for columns for which you have some privilege.
The RESOURCE_GROUPS table has these
columns:
RESOURCE_GROUP_NAME
The name of the resource group.
RESOURCE_GROUP_TYPE
The resource group type, either SYSTEM or
USER.
RESOURCE_GROUP_ENABLED
Whether the resource group is enabled (1) or disabled (0);
VCPU_IDS
The CPU affinity; that is, the set of virtual CPUs that the resource group can use. The value is a list of comma-separated CPU numbers or ranges.
THREAD_PRIORITY
The priority for threads assigned to the resource group. The priority ranges from -20 (highest priority) to 19 (lowest priority). System resource groups have a priority that ranges from -20 to 0. User resource groups have a priority that ranges from 0 to 19.
The ROUTINES table provides
information about stored routines (stored procedures and stored
functions). The ROUTINES table does
not include built-in SQL functions or user-defined functions
(UDFs).
The ROUTINES table has these columns:
SPECIFIC_NAME
The name of the routine.
ROUTINE_CATALOG
The name of the catalog to which the routine belongs. This
value is always def.
ROUTINE_SCHEMA
The name of the schema (database) to which the routine belongs.
ROUTINE_NAME
The name of the routine.
ROUTINE_TYPE
PROCEDURE for stored procedures,
FUNCTION for stored functions.
DATA_TYPE
If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.
The DATA_TYPE value is the type name only
with no other information. The
DTD_IDENTIFIER value contains the type name
and possibly other information such as the precision or
length.
CHARACTER_MAXIMUM_LENGTH
For stored function string return values, the maximum length
in characters. If the routine is a stored procedure, this
value is NULL.
CHARACTER_OCTET_LENGTH
For stored function string return values, the maximum length
in bytes. If the routine is a stored procedure, this value is
NULL.
NUMERIC_PRECISION
For stored function numeric return values, the numeric
precision. If the routine is a stored procedure, this value is
NULL.
NUMERIC_SCALE
For stored function numeric return values, the numeric scale.
If the routine is a stored procedure, this value is
NULL.
DATETIME_PRECISION
For stored function temporal return values, the fractional
seconds precision. If the routine is a stored procedure, this
value is NULL.
CHARACTER_SET_NAME
For stored function character string return values, the
character set name. If the routine is a stored procedure, this
value is NULL.
COLLATION_NAME
For stored function character string return values, the
collation name. If the routine is a stored procedure, this
value is NULL.
DTD_IDENTIFIER
If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.
The DATA_TYPE value is the type name only
with no other information. The
DTD_IDENTIFIER value contains the type name
and possibly other information such as the precision or
length.
ROUTINE_BODY
The language used for the routine definition. This value is
always SQL.
ROUTINE_DEFINITION
The text of the SQL statement executed by the routine.
EXTERNAL_NAME
This value is always NULL.
EXTERNAL_LANGUAGE
The language of the stored routine. The value is read from the
external_language column of the
mysql.routines data dictionary table.
PARAMETER_STYLE
This value is always SQL.
IS_DETERMINISTIC
YES or NO, depending on
whether the routine is defined with the
DETERMINISTIC characteristic.
SQL_DATA_ACCESS
The data access characteristic for the routine. The value is
one of CONTAINS SQL, NO
SQL, READS SQL DATA, or
MODIFIES SQL DATA.
SQL_PATH
This value is always NULL.
SECURITY_TYPE
The routine SQL SECURITY characteristic.
The value is one of DEFINER or
INVOKER.
CREATED
The date and time when the routine was created. This is a
TIMESTAMP value.
LAST_ALTERED
The date and time when the routine was last modified. This is
a TIMESTAMP value. If the
routine has not been modified since its creation, this value
is the same as the CREATED value.
SQL_MODE
The SQL mode in effect when the routine was created or altered, and under which the routine executes. For the permitted values, see Section 5.1.11, “Server SQL Modes”.
ROUTINE_COMMENT
The text of the comment, if the routine has one. If not, this value is empty.
DEFINER
The account of the user who created the routine, in
'
format.
user_name'@'host_name'
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.
A schema is a database, so the
SCHEMATA table provides information
about databases.
The SCHEMATA table has these columns:
CATALOG_NAME
The name of the catalog to which the schema belongs. This
value is always def.
SCHEMA_NAME
The name of the schema.
DEFAULT_CHARACTER_SET_NAME
The schema default character set.
DEFAULT_COLLATION_NAME
The schema default collation.
SQL_PATH
This value is always NULL.
DEFAULT_ENCRYPTION
The schema default encryption. This column was added in MySQL 8.0.16.
Schema names are also available from the SHOW
DATABASES statement. See
Section 13.7.7.14, “SHOW DATABASES Syntax”. The following statements are
equivalent:
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild'] SHOW DATABASES [LIKE 'wild']
You see only those databases for which you have some kind of
privilege, unless you have the global SHOW
DATABASES privilege.
Because any static global privilege is considered a privilege
for all databases, any static global privilege enables a user to
see all database names with SHOW
DATABASES or by examining the
SCHEMATA table of
INFORMATION_SCHEMA, except databases that
have been restricted at the database level by partial revokes.
The SCHEMA_PRIVILEGES table provides
information about schema (database) privileges. It takes its
values from the mysql.db system table.
The SCHEMA_PRIVILEGES table has these
columns:
GRANTEE
The name of the account to which the privilege is granted, in
'
format.
user_name'@'host_name'
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.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES SHOW GRANTS ...
The STATISTICS table provides
information about table indexes.
Columns in STATISTICS that represent
table statistics hold cached values. The
information_schema_stats_expiry
system variable defines the period of time before cached table
statistics expire. The default is 86400 seconds (24 hours). If
there are no cached statistics or statistics have expired,
statistics are retrieved from storage engines when querying table
statistics columns. To update cached values at any time for a
given table, use ANALYZE TABLE. To
always retrieve the latest statistics directly from storage
engines, set
information_schema_stats_expiry=0.
For more information, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
If the innodb_read_only system
variable is enabled, ANALYZE
TABLE may fail because it cannot update statistics
tables in the data dictionary, which use
InnoDB. For ANALYZE
TABLE operations that update the key distribution,
failure may occur even if the operation updates the table itself
(for example, if it is a MyISAM table). To
obtain the updated distribution statistics, set
information_schema_stats_expiry=0.
The STATISTICS table has these
columns:
TABLE_CATALOG
The name of the catalog to which the table containing the
index belongs. This value is always def.
TABLE_SCHEMA
The name of the schema (database) to which the table containing the index belongs.
TABLE_NAME
The name of the table containing the index.
NON_UNIQUE
0 if the index cannot contain duplicates, 1 if it can.
INDEX_SCHEMA
The name of the schema (database) to which the index belongs.
INDEX_NAME
The name of the index. If the index is the primary key, the
name is always PRIMARY.
SEQ_IN_INDEX
The column sequence number in the index, starting with 1.
COLUMN_NAME
The column name. See also the description for the
EXPRESSION column.
COLLATION
How the column is sorted in the index. This can have values
A (ascending), D
(descending), or NULL (not sorted).
CARDINALITY
An estimate of the number of unique values in the index. To
update this number, run ANALYZE
TABLE or (for MyISAM tables)
myisamchk -a.
CARDINALITY is counted based on statistics
stored as integers, so the value is not necessarily exact even
for small tables. The higher the cardinality, the greater the
chance that MySQL uses the index when doing joins.
SUB_PART
The index prefix. That is, the number of indexed characters if
the column is only partly indexed, NULL if
the entire column is indexed.
Prefix limits are measured in bytes.
However, prefix lengths for index
specifications in CREATE
TABLE, ALTER TABLE,
and CREATE INDEX statements
are interpreted as number of characters for nonbinary string
types (CHAR,
VARCHAR,
TEXT) and number of bytes for
binary string types (BINARY,
VARBINARY,
BLOB). Take this into account
when specifying a prefix length for a nonbinary string
column that uses a multibyte character set.
For additional information about index prefixes, see Section 8.3.5, “Column Indexes”, and Section 13.1.15, “CREATE INDEX Syntax”.
PACKED
Indicates how the key is packed. NULL if it
is not.
NULLABLE
Contains YES if the column may contain
NULL values and '' if
not.
INDEX_TYPE
The index method used (BTREE,
FULLTEXT, HASH,
RTREE).
COMMENT
Information about the index not described in its own column,
such as disabled if the index is disabled.
INDEX_COMMENT
Any comment provided for the index with a
COMMENT attribute when the index was
created.
IS_VISIBLE
Whether the index is visible to the optimizer. See Section 8.3.12, “Invisible Indexes”.
EXPRESSION
MySQL 8.0.13 and higher supports functional key parts (see
Functional Key Parts), which
affects both the COLUMN_NAME and
EXPRESSION columns:
For a nonfunctional key part,
COLUMN_NAME indicates the column
indexed by the key part and EXPRESSION
is NULL.
For a functional key part, COLUMN_NAME
column is NULL and
EXPRESSION indicates the expression for
the key part.
There is no standard INFORMATION_SCHEMA
table for indexes. The MySQL column list is similar to what
SQL Server 2000 returns for sp_statistics,
except that QUALIFIER and
OWNER are replaced with
CATALOG and SCHEMA,
respectively.
Information about table indexes is also available from the
SHOW INDEX statement. See
Section 13.7.7.22, “SHOW INDEX Syntax”. The following statements are
equivalent:
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name' SHOW INDEX FROMtbl_nameFROMdb_name
The ST_GEOMETRY_COLUMNS table
provides information about table columns that store spatial data.
This table is based on the SQL/MM (ISO/IEC 13249-3) standard, with
extensions as noted. MySQL implements
ST_GEOMETRY_COLUMNS as a view on the
INFORMATION_SCHEMA
COLUMNS table.
The ST_GEOMETRY_COLUMNS table has
these columns:
TABLE_CATALOG
The name of the catalog to which the table containing the
column belongs. This value is always def.
TABLE_SCHEMA
The name of the schema (database) to which the table containing the column belongs.
TABLE_NAME
The name of the table containing the column.
COLUMN_NAME
The name of the column.
SRS_NAME
The spatial reference system (SRS) name.
SRS_ID
The spatial reference system ID (SRID).
GEOMETRY_TYPE_NAME
The column data type. Permitted values are:
geometry, point,
linestring, polygon,
multipoint,
multilinestring,
multipolygon,
geometrycollection. This column is a MySQL
extension to the standard.
The ST_SPATIAL_REFERENCE_SYSTEMS
table provides information about available spatial reference
systems for spatial data. This table is based on the SQL/MM
(ISO/IEC 13249-3) standard.
Entries in the
ST_SPATIAL_REFERENCE_SYSTEMS table
are based on the European Petroleum
Survey Group (EPSG) data set, except for SRID 0, which
corresponds to a special SRS used in MySQL that represents an
infinite flat Cartesian plane with no units assigned to its axes.
For additional information about SRSs, see
Section 11.5.5, “Spatial Reference System Support”.
The ST_SPATIAL_REFERENCE_SYSTEMS
table has these columns:
SRS_NAME
The spatial reference system name. This value is unique.
SRS_ID
The spatial reference system numeric ID. This value is unique.
SRS_ID values represent the same kind of
values passed as the SRID argument to spatial functions. SRID
0 (the unitless Cartesian plane) is special. It is always a
legal spatial reference system ID and can be used in any
computations on spatial data that depend on SRID values.
ORGANIZATION
The name of the organization that defined the coordinate system on which the spatial reference system is based.
ORGANIZATION_COORDSYS_ID
The numeric ID given to the spatial reference system by the organization that defined it.
DEFINITION
The spatial reference system definition.
DEFINITION values are WKT values,
represented as specified in the
Open Geospatial
Consortium document
OGC
12-063r5.
SRS definition parsing occurs on demand when definitions are needed by GIS functions. Parsed definitions are cached in the data dictionary cache so that parsing overhead is not incurred for every statement that needs SRS information.
DESCRIPTION
The spatial reference system description.
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.
mysql>SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMSWHERE SRS_ID = 4326\G*************************** 1. row *************************** SRS_NAME: WGS 84 SRS_ID: 4326 ORGANIZATION: EPSG ORGANIZATION_COORDSYS_ID: 4326 DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]], AXIS["Lat",NORTH],AXIS["Long",EAST], AUTHORITY["EPSG","4326"]] DESCRIPTION:
This entry describes the SRS used for GPS systems. It has a name
(SRS_NAME) of WGS 84 and an ID
(SRS_ID) of 4326, which is the ID used by the
European Petroleum Survey
Group (EPSG).
The DEFINITION values for projected and
geographic SRSs begin with PROJCS and
GEOGCS, respectively. The definition for SRID 0
is special and has an empty DEFINITION value.
The following query determines how many entries in the
ST_SPATIAL_REFERENCE_SYSTEMS table
correspond to projected, geographic, and other SRSs, based on
DEFINITION values:
mysql>SELECTCOUNT(*),CASE LEFT(DEFINITION, 6)WHEN 'PROJCS' THEN 'Projected'WHEN 'GEOGCS' THEN 'Geographic'ELSE 'Other'END AS SRS_TYPEFROM ST_SPATIAL_REFERENCE_SYSTEMSGROUP BY SRS_TYPE;+----------+------------+ | COUNT(*) | SRS_TYPE | +----------+------------+ | 1 | Other | | 4668 | Projected | | 483 | Geographic | +----------+------------+
To enable manipulation of SRS entries stored in the data dictionary, MySQL provides these SQL statements:
CREATE SPATIAL REFERENCE
SYSTEM: See
Section 13.1.19, “CREATE SPATIAL REFERENCE SYSTEM Syntax”. The
description for this statement includes additional information
about SRS components.
DROP SPATIAL REFERENCE SYSTEM:
See Section 13.1.31, “DROP SPATIAL REFERENCE SYSTEM Syntax”.
The ST_UNITS_OF_MEASURE table
(available as of MySQL 8.0.14) provides information about
acceptable units for the
ST_Distance() function.
The ST_UNITS_OF_MEASURE table has
these columns:
UNIT_NAME
The name of the unit.
UNIT_TYPE
The unit type (for example, LINEAR).
CONVERSION_FACTOR
A conversion factor used for internal calculations.
DESCRIPTION
A description of the unit.
The TABLES table provides information
about tables in databases.
Columns in TABLES that represent
table statistics hold cached values. The
information_schema_stats_expiry
system variable defines the period of time before cached table
statistics expire. The default is 86400 seconds (24 hours). If
there are no cached statistics or statistics have expired,
statistics are retrieved from storage engines when querying table
statistics columns. To update cached values at any time for a
given table, use ANALYZE TABLE. To
always retrieve the latest statistics directly from storage
engines, set
information_schema_stats_expiry
to 0. For more information, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
If the innodb_read_only system
variable is enabled, ANALYZE
TABLE may fail because it cannot update statistics
tables in the data dictionary, which use
InnoDB. For ANALYZE
TABLE operations that update the key distribution,
failure may occur even if the operation updates the table itself
(for example, if it is a MyISAM table). To
obtain the updated distribution statistics, set
information_schema_stats_expiry=0.
The TABLES table has these columns:
TABLE_CATALOG
The name of the catalog to which the table belongs. This value
is always def.
TABLE_SCHEMA
The name of the schema (database) to which the table belongs.
TABLE_NAME
The name of the table.
TABLE_TYPE
BASE TABLE for a table,
VIEW for a view, or SYSTEM
VIEW for an INFORMATION_SCHEMA
table.
The TABLES table does not list
TEMPORARY tables.
ENGINE
The storage engine for the table. See Chapter 15, The InnoDB Storage Engine, and Chapter 16, Alternative Storage Engines.
For partitioned tables, ENGINE shows the
name of the storage engine used by all partitions.
VERSION
This column is unused. With the removal of
.frm files in MySQL 8.0, this column now
reports a hardcoded value of 10, which is
the last .frm file version used in MySQL
5.7.
ROW_FORMAT
The row-storage format (Fixed,
Dynamic, Compressed,
Redundant, Compact). For
MyISAM tables, Dynamic
corresponds to what myisamchk -dvv reports
as Packed.
TABLE_ROWS
The number of rows. Some storage engines, such as
MyISAM, store the exact count. For other
storage engines, such as InnoDB, this value
is an approximation, and may vary from the actual value by as
much as 40% to 50%. In such cases, use SELECT
COUNT(*) to obtain an accurate count.
TABLE_ROWS is NULL for
INFORMATION_SCHEMA tables.
For InnoDB tables, the row count
is only a rough estimate used in SQL optimization. (This is
also true if the InnoDB table is
partitioned.)
AVG_ROW_LENGTH
The average row length.
DATA_LENGTH
For MyISAM, DATA_LENGTH
is the length of the data file, in bytes.
For InnoDB, DATA_LENGTH
is the approximate amount of memory allocated for the
clustered index, in bytes. Specifically, it is the clustered
index size, in pages, multiplied by the
InnoDB page size.
Refer to the notes at the end of this section for information regarding other storage engines.
MAX_DATA_LENGTH
For MyISAM,
MAX_DATA_LENGTH is maximum length of the
data file. This is the total number of bytes of data that can
be stored in the table, given the data pointer size used.
Unused for InnoDB.
Refer to the notes at the end of this section for information regarding other storage engines.
INDEX_LENGTH
For MyISAM, INDEX_LENGTH
is the length of the index file, in bytes.
For InnoDB, INDEX_LENGTH
is the approximate amount of memory allocated for
non-clustered indexes, in bytes. Specifically, it is the sum
of non-clustered index sizes, in pages, multiplied by the
InnoDB page size.
Refer to the notes at the end of this section for information regarding other storage engines.
DATA_FREE
The number of allocated but unused bytes.
InnoDB tables report the free space of the
tablespace to which the table belongs. For a table located in
the shared tablespace, this is the free space of the shared
tablespace. If you are using multiple tablespaces and the
table has its own tablespace, the free space is for only that
table. Free space means the number of bytes in completely free
extents minus a safety margin. Even if free space displays as
0, it may be possible to insert rows as long as new extents
need not be allocated.
For NDB Cluster, DATA_FREE shows the space
allocated on disk for, but not used by, a Disk Data table or
fragment on disk. (In-memory data resource usage is reported
by the DATA_LENGTH column.)
For partitioned tables, this value is only an estimate and may
not be absolutely correct. A more accurate method of obtaining
this information in such cases is to query the
INFORMATION_SCHEMA
PARTITIONS table, as shown in
this example:
SELECT SUM(DATA_FREE)
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'mytable';
For more information, see Section 25.17, “The INFORMATION_SCHEMA PARTITIONS Table”.
AUTO_INCREMENT
The next AUTO_INCREMENT value.
CREATE_TIME
When the table was created.
UPDATE_TIME
When the data file was last updated. For some storage engines,
this value is NULL. For example,
InnoDB stores multiple tables in its
system
tablespace and the data file timestamp does not apply.
Even with
file-per-table mode
with each InnoDB table in a separate
.ibd file,
change buffering
can delay the write to the data file, so the file modification
time is different from the time of the last insert, update, or
delete. For MyISAM, the data file timestamp
is used; however, on Windows the timestamp is not updated by
updates, so the value is inaccurate.
UPDATE_TIME displays a timestamp value for
the last UPDATE,
INSERT, or
DELETE performed on
InnoDB tables that are not partitioned. For
MVCC, the timestamp value reflects the
COMMIT time, which is
considered the last update time. Timestamps are not persisted
when the server is restarted or when the table is evicted from
the InnoDB data dictionary cache.
CHECK_TIME
When the table was last checked. Not all storage engines
update this time, in which case, the value is always
NULL.
For partitioned InnoDB tables,
CHECK_TIME is always
NULL.
TABLE_COLLATION
The table default collation. The output does not explicitly list the table default character set, but the collation name begins with the character set name.
CHECKSUM
The live checksum value, if any.
CREATE_OPTIONS
Extra options used with CREATE
TABLE. The original options specified when
CREATE TABLE was executed are
retained. The information reported may differ from current
table settings and options.
For InnoDB tables, the actual
ROW_FORMAT and
KEY_BLOCK_SIZE options are shown. Prior to
MySQL 8.0, CREATE_OPTIONS shows the
originally supplied ROW_FORMAT and
KEY_BLOCK_SIZE. For more information, see
Section 13.1.20, “CREATE TABLE Syntax”.
CREATE_OPTIONS shows
partitioned for a partitioned table. Prior
to MySQL 8.0.16, it also shows the
ENCRYPTION clause specified for tables
created in file-per-table tablespaces. As of MySQL 8.0.16, it
only shows the encryption clause for file-per-table
tablespaces if the table is encrypted or if the specified
encryption differs from the schema encryption. The encryption
clause is not shown for tables created in general tablespaces.
To identify encrypted file-per-table and general tablespaces,
query the INNODB_TABLESPACES
ENCRYPTION column.
TABLE_COMMENT
The comment used when creating the table (or information as to why MySQL could not access the table information).
For NDB tables, the output of
this statement shows appropriate values for the
AVG_ROW_LENGTH and
DATA_LENGTH columns, with the exception
that BLOB columns are not taken
into account.
For NDB tables,
DATA_LENGTH includes data stored in main
memory only; the MAX_DATA_LENGTH and
DATA_FREE columns apply to Disk Data.
For NDB Cluster Disk Data tables,
MAX_DATA_LENGTH shows the space allocated
for the disk part of a Disk Data table or fragment. (In-memory
data resource usage is reported by the
DATA_LENGTH column.)
For MEMORY tables, the
DATA_LENGTH,
MAX_DATA_LENGTH, and
INDEX_LENGTH values approximate the actual
amount of allocated memory. The allocation algorithm reserves
memory in large amounts to reduce the number of allocation
operations.
For views, most TABLES columns
are 0 or NULL except that
TABLE_NAME indicates the view name,
CREATE_TIME indicates the creation time,
and TABLE_COMMENT says
VIEW.
Table information is also available from the
SHOW TABLE STATUS and
SHOW TABLES statements. See
Section 13.7.7.36, “SHOW TABLE STATUS Syntax”, and
Section 13.7.7.37, “SHOW TABLES Syntax”. The following statements are
equivalent:
SELECT
TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
CREATE_OPTIONS, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name'
[AND table_name LIKE 'wild']
SHOW TABLE STATUS
FROM db_name
[LIKE 'wild']
The following statements are equivalent:
SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' [AND table_name LIKE 'wild'] SHOW FULL TABLES FROMdb_name[LIKE 'wild']
The TABLESPACES table provides
information about active MySQL Cluster tablespaces.
The TABLESPACES table has these
columns:
TABLESPACE_NAME
The name of the tablespace.
ENGINE
The name of the storage engine that uses the tablespace.
TABLESPACE_TYPE
The tablespace type.
LOGFILE_GROUP_NAME
The name of the logfile group assigned to the tablespace.
EXTENT_SIZE
The size in bytes of the extents used by files that belong to the tablespace.
AUTOEXTEND_SIZE
Unused.
MAXIMUM_SIZE
Unused.
NODEGROUP_ID
Unused.
TABLESPACE_COMMENT
Unused.
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.
The TABLE_CONSTRAINTS table describes
which tables have constraints.
The TABLE_CONSTRAINTS table has these
columns:
CONSTRAINT_CATALOG
The name of the catalog to which the constraint belongs. This
value is always def.
CONSTRAINT_SCHEMA
The name of the schema (database) to which the constraint belongs.
TABLE_SCHEMA
The name of the schema (database) to which the table belongs.
TABLE_NAME
The name of the table.
The CONSTRAINT_TYPE
The type of constraint. The value can be
UNIQUE, PRIMARY KEY,
FOREIGN KEY, or (as of MySQL 8.0.16)
CHECK. This is a
CHAR (not
ENUM) column.
The UNIQUE and PRIMARY
KEY information is about the same as what you get
from the Key_name column in the output from
SHOW INDEX when the
Non_unique column is 0.
ENFORCED
For CHECK constraints, the value is
YES or NO to indicate
whether the constraint is enforced. For other constraints, the
value is always YES.
This column was added in MySQL 8.0.16.
The TABLE_PRIVILEGES table provides
information about table privileges. It takes its values from the
mysql.tables_priv system table.
The TABLE_PRIVILEGES table has these
columns:
GRANTEE
The name of the account to which the privilege is granted, in
'
format.
user_name'@'host_name'
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.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES SHOW GRANTS ...
The TRIGGERS table provides
information about triggers. To see information about a table's
triggers, you must have the TRIGGER
privilege for the table.
The TRIGGERS table has these columns:
TRIGGER_CATALOG
The name of the catalog to which the trigger belongs. This
value is always def.
TRIGGER_SCHEMA
The name of the schema (database) to which the trigger belongs.
TRIGGER_NAME
The name of the trigger.
EVENT_MANIPULATION
The trigger event. This is the type of operation on the
associated table for which the trigger activates. The value is
INSERT (a row was inserted),
DELETE (a row was deleted), or
UPDATE (a row was modified).
EVENT_OBJECT_CATALOG,
EVENT_OBJECT_SCHEMA, and
EVENT_OBJECT_TABLE
As noted in Section 24.3, “Using Triggers”, every trigger is
associated with exactly one table. These columns indicate the
catalog and schema (database) in which this table occurs, and
the table name, respectively. The
EVENT_OBJECT_CATALOG value is always
def.
ACTION_ORDER
The ordinal position of the trigger's action within the list
of triggers on the same table with the same
EVENT_MANIPULATION and
ACTION_TIMING values.
ACTION_CONDITION
This value is always NULL.
ACTION_STATEMENT
The trigger body; that is, the statement executed when the trigger activates. This text uses UTF-8 encoding.
ACTION_ORIENTATION
This value is always ROW.
ACTION_TIMING
Whether the trigger activates before or after the triggering
event. The value is BEFORE or
AFTER.
ACTION_REFERENCE_OLD_TABLE
This value is always NULL.
ACTION_REFERENCE_NEW_TABLE
This value is always NULL.
ACTION_REFERENCE_OLD_ROW and
ACTION_REFERENCE_NEW_ROW
The old and new column identifiers, respectively. The
ACTION_REFERENCE_OLD_ROW value is always
OLD and the
ACTION_REFERENCE_NEW_ROW value is always
NEW.
CREATED
The date and time when the trigger was created. This is a
TIMESTAMP(2) value (with a fractional part
in hundredths of seconds) for triggers.
SQL_MODE
The SQL mode in effect when the trigger was created, and under which the trigger executes. For the permitted values, see Section 5.1.11, “Server SQL Modes”.
DEFINER
The account of the user who created the trigger, in
'
format.
user_name'@'host_name'
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 example uses the ins_sum trigger
defined in Section 24.3, “Using Triggers”:
mysql>SELECT * FROM INFORMATION_SCHEMA.TRIGGERSWHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ins_sum'\G*************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: ins_sum EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: account ACTION_ORDER: 1 ACTION_CONDITION: NULL ACTION_STATEMENT: SET @sum = @sum + NEW.amount ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2018-08-08 10:10:12.61 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION DEFINER: me@localhost CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci
Trigger information is also available from the
SHOW TRIGGERS statement. See
Section 13.7.7.38, “SHOW TRIGGERS Syntax”.
The USER_PRIVILEGES table provides
information about global privileges. It takes its values from the
mysql.user system table.
The USER_PRIVILEGES table has these
columns:
GRANTEE
The name of the account to which the privilege is granted, in
'
format.
user_name'@'host_name'
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.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.USER_PRIVILEGES SHOW GRANTS ...
The VIEWS table provides information
about views in databases. You must have the
SHOW VIEW privilege to access this
table.
The VIEWS table has these columns:
TABLE_CATALOG
The name of the catalog to which the view belongs. This value
is always def.
TABLE_SCHEMA
The name of the schema (database) to which the view belongs.
TABLE_NAME
The name of the view.
VIEW_DEFINITION
The SELECT statement that
provides the definition of the view. This column has most of
what you see in the Create Table column
that SHOW CREATE VIEW produces.
Skip the words before SELECT
and skip the words WITH CHECK OPTION.
Suppose that the original statement was:
CREATE VIEW v AS SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1 WITH CHECK OPTION;
Then the view definition looks like this:
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
CHECK_OPTION
The value of the CHECK_OPTION attribute.
The value is one of NONE,
CASCADE, or LOCAL.
IS_UPDATABLE
MySQL sets a flag, called the view updatability flag, at
CREATE VIEW time. The flag is
set to YES (true) if
UPDATE and
DELETE (and similar operations)
are legal for the view. Otherwise, the flag is set to
NO (false). The
IS_UPDATABLE column in the
VIEWS table displays the status
of this flag. It means that the server always knows whether a
view is updatable.
If a view is not updatable, statements such
UPDATE,
DELETE, and
INSERT are illegal and are
rejected. (Even if a view is updatable, it might not be
possible to insert into it; for details, refer to
Section 24.5.3, “Updatable and Insertable Views”.)
DEFINER
The account of the user who created the view, in
'
format.
user_name'@'host_name'
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.
MySQL permits different sql_mode
settings to tell the server the type of SQL syntax to support. For
example, you might use the ANSI
SQL mode to ensure MySQL correctly interprets the standard SQL
concatenation operator, the double bar (||), in
your queries. If you then create a view that concatenates items,
you might worry that changing the
sql_mode setting to a value
different from ANSI could cause
the view to become invalid. But this is not the case. No matter
how you write out a view definition, MySQL always stores it the
same way, in a canonical form. Here is an example that shows how
the server changes a double bar concatenation operator to a
CONCAT() function:
mysql>SET sql_mode = 'ANSI';Query OK, 0 rows affected (0.00 sec) mysql>CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;Query OK, 0 rows affected (0.00 sec) mysql>SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';+----------------------------------+ | VIEW_DEFINITION | +----------------------------------+ | select concat('a','b') AS `col1` | +----------------------------------+ 1 row in set (0.00 sec)
The advantage of storing a view definition in canonical form is
that changes made later to the value of
sql_mode do not affect the
results from the view. However, an additional consequence is that
comments prior to SELECT are
stripped from the definition by the server.
The VIEW_ROUTINE_USAGE table
(available as of MySQL 8.0.13) provides access to information
about stored functions used in view definitions. The table does
not list information about built-in SQL functions or user-defined
functions (UDFs) used in the definitions.
You can see information only for views for which you have some privilege, and only for functions for which you have some privilege.
The VIEW_ROUTINE_USAGE table has
these columns:
TABLE_CATALOG
The name of the catalog to which the view belongs. This value
is always def.
TABLE_SCHEMA
The name of the schema (database) to which the view belongs.
TABLE_NAME
The name of the view.
SPECIFIC_CATALOG
The name of the catalog to which the function used in the view
definition belongs. This value is always
def.
SPECIFIC_SCHEMA
The name of the schema (database) to which the function used in the view definition belongs.
SPECIFIC_NAME
The name of the function used in the view definition.
The VIEW_TABLE_USAGE table (available
as of MySQL 8.0.13) provides access to information about tables
and views used in view definitions.
You can see information only for views for which you have some privilege, and only for tables for which you have some privilege.
The VIEW_TABLE_USAGE table has these
columns:
VIEW_CATALOG
The name of the catalog to which the view belongs. This value
is always def.
VIEW_SCHEMA
The name of the schema (database) to which the view belongs.
VIEW_NAME
The name of the view.
TABLE_CATALOG
The name of the catalog to which the table or view used in the
view definition belongs. This value is always
def.
TABLE_SCHEMA
The name of the schema (database) to which the table or view used in the view definition belongs.
TABLE_NAME
The name of the table or view used in the view definition.
This section provides table definitions for
InnoDB INFORMATION_SCHEMA
tables. For related information and examples, see
Section 15.14, “InnoDB INFORMATION_SCHEMA Tables”.
InnoDB INFORMATION_SCHEMA
tables can be used to monitor ongoing InnoDB
activity, to detect inefficiencies before they turn into issues, or
to troubleshoot performance and capacity issues. As your database
becomes bigger and busier, running up against the limits of your
hardware capacity, you monitor and tune these aspects to keep the
database running smoothly.
The INNODB_BUFFER_PAGE table provides
information about each page in
the InnoDB
buffer pool.
For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Querying the INNODB_BUFFER_PAGE
table can affect performance. Do not query this table on a
production system unless you are aware of the performance impact
and have determined it to be acceptable. To avoid impacting
performance on a production system, reproduce the issue you want
to investigate and query buffer pool statistics on a test
instance.
The INNODB_BUFFER_PAGE table has
these columns:
POOL_ID
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
BLOCK_ID
The buffer pool block ID.
SPACE
The tablespace ID; the same value as
INNODB_TABLES.SPACE.
PAGE_NUMBER
The page number.
PAGE_TYPE
The page type. The following table shows the permitted values.
Table 25.1 INNODB_BUFFER_PAGE.PAGE_TYPE Values
| Page Type | Description |
|---|---|
ALLOCATED |
Freshly allocated page |
BLOB |
Uncompressed BLOB page |
COMPRESSED_BLOB2 |
Subsequent comp BLOB page |
COMPRESSED_BLOB |
First compressed BLOB page |
ENCRYPTED_RTREE |
Encrypted R-tree |
EXTENT_DESCRIPTOR |
Extent descriptor page |
FILE_SPACE_HEADER |
File space header |
FIL_PAGE_TYPE_UNUSED |
Unused |
IBUF_BITMAP |
Insert buffer bitmap |
IBUF_FREE_LIST |
Insert buffer free list |
IBUF_INDEX |
Insert buffer index |
INDEX |
B-tree node |
INODE |
Index node |
LOB_DATA |
Uncompressed LOB data |
LOB_FIRST |
First page of uncompressed LOB |
LOB_INDEX |
Uncompressed LOB index |
PAGE_IO_COMPRESSED |
Compressed page |
PAGE_IO_COMPRESSED_ENCRYPTED |
Compressed and encrypted page |
PAGE_IO_ENCRYPTED |
Encrypted page |
RSEG_ARRAY |
Rollback segment array |
RTREE_INDEX |
R-tree index |
SDI_BLOB |
Uncompressed SDI BLOB |
SDI_COMPRESSED_BLOB |
Compressed SDI BLOB |
SDI_INDEX |
SDI index |
SYSTEM |
System page |
TRX_SYSTEM |
Transaction system data |
UNDO_LOG |
Undo log page |
UNKNOWN |
Unknown |
ZLOB_DATA |
Compressed LOB data |
ZLOB_FIRST |
First page of compressed LOB |
ZLOB_FRAG |
Compressed LOB fragment |
ZLOB_FRAG_ENTRY |
Compressed LOB fragment index |
ZLOB_INDEX |
Compressed LOB index |
FLUSH_TYPE
The flush type.
FIX_COUNT
The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
IS_HASHED
Whether a hash index has been built on this page.
NEWEST_MODIFICATION
The Log Sequence Number of the youngest modification.
OLDEST_MODIFICATION
The Log Sequence Number of the oldest modification.
ACCESS_TIME
An abstract number used to judge the first access time of the page.
TABLE_NAME
The name of the table the page belongs to. This column is
applicable only to pages with a PAGE_TYPE
value of INDEX.
INDEX_NAME
The name of the index the page belongs to. This can be the
name of a clustered index or a secondary index. This column is
applicable only to pages with a PAGE_TYPE
value of INDEX.
NUMBER_RECORDS
The number of records within the page.
DATA_SIZE
The sum of the sizes of the records. This column is applicable
only to pages with a PAGE_TYPE value of
INDEX.
COMPRESSED_SIZE
The compressed page size. NULL for pages
that are not compressed.
PAGE_STATE
The page state. The following table shows the permitted values.
Table 25.2 INNODB_BUFFER_PAGE.PAGE_STATE Values
| Page State | Description |
|---|---|
FILE_PAGE |
A buffered file page |
MEMORY |
Contains a main memory object |
NOT_USED |
In the free list |
NULL |
Clean compressed pages, compressed pages in the flush list, pages used as buffer pool watch sentinels |
READY_FOR_USE |
A free page |
REMOVE_HASH |
Hash index should be removed before placing in the free list |
IO_FIX
Whether any I/O is pending for this page:
IO_NONE = no pending I/O,
IO_READ = read pending,
IO_WRITE = write pending.
IS_OLD
Whether the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCK
The value of the freed_page_clock counter
when the block was the last placed at the head of the LRU
list. The freed_page_clock counter tracks
the number of blocks removed from the end of the LRU list.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G
*************************** 1. row ***************************
POOL_ID: 0
BLOCK_ID: 0
SPACE: 97
PAGE_NUMBER: 2473
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 733855581
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3378385672
TABLE_NAME: `employees`.`salaries`
INDEX_NAME: PRIMARY
NUMBER_RECORDS: 468
DATA_SIZE: 14976
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 66
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
When tables, table rows, partitions, or indexes are deleted,
associated pages remain in the buffer pool until space is
required for other data. The
INNODB_BUFFER_PAGE table reports
information about these pages until they are evicted from the
buffer pool. For more information about how the
InnoDB manages buffer pool data, see
Section 15.5.1, “Buffer Pool”.
The INNODB_BUFFER_PAGE_LRU table
provides information about the pages in the
InnoDB buffer
pool; in particular, how they are ordered in the LRU list
that determines which pages to
evict from the buffer pool
when it becomes full.
The INNODB_BUFFER_PAGE_LRU table has
the same columns as the
INNODB_BUFFER_PAGE table, except that
the INNODB_BUFFER_PAGE_LRU table has
LRU_POSITION and COMPRESSED
columns instead of BLOCK_ID and
PAGE_STATE columns.
For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Querying the INNODB_BUFFER_PAGE_LRU
table can affect performance. Do not query this table on a
production system unless you are aware of the performance impact
and have determined it to be acceptable. To avoid impacting
performance on a production system, reproduce the issue you want
to investigate and query buffer pool statistics on a test
instance.
The INNODB_BUFFER_PAGE_LRU table has
these columns:
POOL_ID
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
LRU_POSITION
The position of the page in the LRU list.
SPACE
The tablespace ID; the same value as
INNODB_TABLES.SPACE.
PAGE_NUMBER
The page number.
PAGE_TYPE
The page type. The following table shows the permitted values.
Table 25.3 INNODB_BUFFER_PAGE_LRU.PAGE_TYPE Values
| Page Type | Description |
|---|---|
ALLOCATED |
Freshly allocated page |
BLOB |
Uncompressed BLOB page |
COMPRESSED_BLOB2 |
Subsequent comp BLOB page |
COMPRESSED_BLOB |
First compressed BLOB page |
ENCRYPTED_RTREE |
Encrypted R-tree |
EXTENT_DESCRIPTOR |
Extent descriptor page |
FILE_SPACE_HEADER |
File space header |
FIL_PAGE_TYPE_UNUSED |
Unused |
IBUF_BITMAP |
Insert buffer bitmap |
IBUF_FREE_LIST |
Insert buffer free list |
IBUF_INDEX |
Insert buffer index |
INDEX |
B-tree node |
INODE |
Index node |
LOB_DATA |
Uncompressed LOB data |
LOB_FIRST |
First page of uncompressed LOB |
LOB_INDEX |
Uncompressed LOB index |
PAGE_IO_COMPRESSED |
Compressed page |
PAGE_IO_COMPRESSED_ENCRYPTED |
Compressed and encrypted page |
PAGE_IO_ENCRYPTED |
Encrypted page |
RSEG_ARRAY |
Rollback segment array |
RTREE_INDEX |
R-tree index |
SDI_BLOB |
Uncompressed SDI BLOB |
SDI_COMPRESSED_BLOB |
Compressed SDI BLOB |
SDI_INDEX |
SDI index |
SYSTEM |
System page |
TRX_SYSTEM |
Transaction system data |
UNDO_LOG |
Undo log page |
UNKNOWN |
Unknown |
ZLOB_DATA |
Compressed LOB data |
ZLOB_FIRST |
First page of compressed LOB |
ZLOB_FRAG |
Compressed LOB fragment |
ZLOB_FRAG_ENTRY |
Compressed LOB fragment index |
ZLOB_INDEX |
Compressed LOB index |
FLUSH_TYPE
The flush type.
FIX_COUNT
The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
IS_HASHED
Whether a hash index has been built on this page.
NEWEST_MODIFICATION
The Log Sequence Number of the youngest modification.
OLDEST_MODIFICATION
The Log Sequence Number of the oldest modification.
ACCESS_TIME
An abstract number used to judge the first access time of the page.
TABLE_NAME
The name of the table the page belongs to. This column is
applicable only to pages with a PAGE_TYPE
value of INDEX.
INDEX_NAME
The name of the index the page belongs to. This can be the
name of a clustered index or a secondary index. This column is
applicable only to pages with a PAGE_TYPE
value of INDEX.
NUMBER_RECORDS
The number of records within the page.
DATA_SIZE
The sum of the sizes of the records. This column is applicable
only to pages with a PAGE_TYPE value of
INDEX.
COMPRESSED_SIZE
The compressed page size. NULL for pages
that are not compressed.
COMPRESSED
Whether the page is compressed.
IO_FIX
Whether any I/O is pending for this page:
IO_NONE = no pending I/O,
IO_READ = read pending,
IO_WRITE = write pending.
IS_OLD
Whether the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCK
The value of the freed_page_clock counter
when the block was the last placed at the head of the LRU
list. The freed_page_clock counter tracks
the number of blocks removed from the end of the LRU list.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G
*************************** 1. row ***************************
POOL_ID: 0
LRU_POSITION: 0
SPACE: 97
PAGE_NUMBER: 1984
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 719490396
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3378383796
TABLE_NAME: `employees`.`salaries`
INDEX_NAME: PRIMARY
NUMBER_RECORDS: 468
DATA_SIZE: 14976
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 0
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
Querying this table can require MySQL to allocate a large block of contiguous memory, more than 64 bytes times the number of active pages in the buffer pool. This allocation could potentially cause an out-of-memory error, especially for systems with multi-gigabyte buffer pools.
Querying this table requires MySQL to lock the data structure representing the buffer pool while traversing the LRU list, which can reduce concurrency, especially for systems with multi-gigabyte buffer pools.
When tables, table rows, partitions, or indexes are deleted,
associated pages remain in the buffer pool until space is
required for other data. The
INNODB_BUFFER_PAGE_LRU table
reports information about these pages until they are evicted
from the buffer pool. For more information about how the
InnoDB manages buffer pool data, see
Section 15.5.1, “Buffer Pool”.
The INNODB_BUFFER_POOL_STATS table
provides much of the same buffer pool information provided in
SHOW ENGINE INNODB
STATUS output. Much of the same information may also be
obtained using InnoDB buffer pool
server status
variables.
The idea of making pages in the buffer pool “young” or “not young” refers to transferring them between the sublists at the head and tail of the buffer pool data structure. Pages made “young” take longer to age out of the buffer pool, while pages made “not young” are moved much closer to the point of eviction.
For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
The INNODB_BUFFER_POOL_STATS table
has these columns:
POOL_ID
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
POOL_SIZE
The InnoDB buffer pool size in pages.
FREE_BUFFERS
The number of free pages in the InnoDB
buffer pool.
DATABASE_PAGES
The number of pages in the InnoDB buffer
pool containing data. This number includes both dirty and
clean pages.
OLD_DATABASE_PAGES
The number of pages in the old buffer pool
sublist.
MODIFIED_DATABASE_PAGES
The number of modified (dirty) database pages.
PENDING_DECOMPRESS
The number of pages pending decompression.
PENDING_READS
The number of pending reads.
PENDING_FLUSH_LRU
The number of pages pending flush in the LRU.
PENDING_FLUSH_LIST
The number of pages pending flush in the flush list.
PAGES_MADE_YOUNG
The number of pages made young.
PAGES_NOT_MADE_YOUNG
The number of pages not made young.
PAGES_MADE_YOUNG_RATE
The number of pages made young per second (pages made young since the last printout / time elapsed).
PAGES_MADE_NOT_YOUNG_RATE
The number of pages not made per second (pages not made young since the last printout / time elapsed).
NUMBER_PAGES_READ
The number of pages read.
NUMBER_PAGES_CREATED
The number of pages created.
NUMBER_PAGES_WRITTEN
The number of pages written.
PAGES_READ_RATE
The number of pages read per second (pages read since the last printout / time elapsed).
PAGES_CREATE_RATE
The number of pages created per second (pages created since the last printout / time elapsed).
PAGES_WRITTEN_RATE
The number of pages written per second (pages written since the last printout / time elapsed).
NUMBER_PAGES_GET
The number of logical read requests.
HIT_RATE
The buffer pool hit rate.
YOUNG_MAKE_PER_THOUSAND_GETS
The number of pages made young per thousand gets.
NOT_YOUNG_MAKE_PER_THOUSAND_GETS
The number of pages not made young per thousand gets.
NUMBER_PAGES_READ_AHEAD
The number of pages read ahead.
NUMBER_READ_AHEAD_EVICTED
The number of pages read into the InnoDB
buffer pool by the read-ahead background thread that were
subsequently evicted without having been accessed by queries.
READ_AHEAD_RATE
The read-ahead rate per second (pages read ahead since the last printout / time elapsed).
READ_AHEAD_EVICTED_RATE
The number of read-ahead pages evicted without access per second (read-ahead pages not accessed since the last printout / time elapsed).
LRU_IO_TOTAL
Total LRU I/O.
LRU_IO_CURRENT
LRU I/O for the current interval.
UNCOMPRESS_TOTAL
The total number of pages decompressed.
UNCOMPRESS_CURRENT
The number of pages decompressed in the current interval.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8192
FREE_BUFFERS: 1
DATABASE_PAGES: 8085
OLD_DATABASE_PAGES: 2964
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 22821
PAGES_NOT_MADE_YOUNG: 3544303
PAGES_MADE_YOUNG_RATE: 357.62602199870594
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 2389
NUMBER_PAGES_CREATED: 12385
NUMBER_PAGES_WRITTEN: 13111
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 33322210
HIT_RATE: 1000
YOUNG_MAKE_PER_THOUSAND_GETS: 18
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 2024
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_CACHED_INDEXES table
reports the number of index pages cached in the
InnoDB buffer pool for each index.
For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
The INNODB_CACHED_INDEXES table has
these columns:
SPACE_ID
The tablespace ID.
INDEX_ID
An identifier for the index. Index identifiers are unique across all the databases in an instance.
N_CACHED_PAGES
The number of index pages cached in the
InnoDB buffer pool.
This query returns the number of index pages cached in the
InnoDB buffer pool for a specific index:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CACHED_INDEXES WHERE INDEX_ID=65\G
*************************** 1. row ***************************
SPACE_ID: 4294967294
INDEX_ID: 65
N_CACHED_PAGES: 45
This query returns the number of index pages cached in the
InnoDB buffer pool for each index, using the
INNODB_INDEXES and
INNODB_TABLES tables to resolve the
table name and index name for each INDEX_ID
value.
SELECT tables.NAME AS table_name, indexes.NAME AS index_name, cached.N_CACHED_PAGES AS n_cached_pages FROM INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached, INFORMATION_SCHEMA.INNODB_INDEXES AS indexes, INFORMATION_SCHEMA.INNODB_TABLES AS tables WHERE cached.INDEX_ID = indexes.INDEX_ID AND indexes.TABLE_ID = tables.TABLE_ID;
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_CMP and
INNODB_CMP_RESET
tables provide status information on operations related to
compressed
InnoDB tables.
The INNODB_CMP and
INNODB_CMP_RESET
tables have these columns:
PAGE_SIZE
The compressed page size in bytes.
COMPRESS_OPS
The number of times a B-tree page of size
PAGE_SIZE has been compressed. Pages are
compressed whenever an empty page is created or the space for
the uncompressed modification log runs out.
COMPRESS_OPS_OK
The number of times a B-tree page of size
PAGE_SIZE has been successfully compressed.
This count should never exceed
COMPRESS_OPS.
COMPRESS_TIME
The total time in seconds used for attempts to compress B-tree
pages of size PAGE_SIZE.
UNCOMPRESS_OPS
The number of times a B-tree page of size
PAGE_SIZE has been uncompressed. B-tree
pages are uncompressed whenever compression fails or at first
access when the uncompressed page does not exist in the buffer
pool.
UNCOMPRESS_TIME
The total time in seconds used for uncompressing B-tree pages
of the size PAGE_SIZE.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP\G
*************************** 1. row ***************************
page_size: 1024
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
*************************** 2. row ***************************
page_size: 2048
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
*************************** 3. row ***************************
page_size: 4096
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
*************************** 4. row ***************************
page_size: 8192
compress_ops: 86955
compress_ops_ok: 81182
compress_time: 27
uncompress_ops: 26828
uncompress_time: 5
*************************** 5. row ***************************
page_size: 16384
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
Use these tables to measure the effectiveness of
InnoDB table
compression in your
database.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
For usage information, see
Section 15.9.1.4, “Monitoring InnoDB Table Compression at Runtime” and
Section 15.14.1.3, “Using the Compression Information Schema Tables”.
For general information about InnoDB table
compression, see Section 15.9, “InnoDB Table and Page Compression”.
The INNODB_CMPMEM and
INNODB_CMPMEM_RESET
tables provide status information on compressed
pages within the
InnoDB buffer
pool.
The INNODB_CMPMEM and
INNODB_CMPMEM_RESET
tables have these columns:
PAGE_SIZE
The block size in bytes. Each record of this table describes blocks of this size.
BUFFER_POOL_INSTANCE
A unique identifier for the buffer pool instance.
PAGES_USED
The number of blocks of size PAGE_SIZE that
are currently in use.
PAGES_FREE
The number of blocks of size PAGE_SIZE that
are currently available for allocation. This column shows the
external fragmentation in the memory pool. Ideally, these
numbers should be at most 1.
RELOCATION_OPS
The number of times a block of size
PAGE_SIZE has been relocated. The buddy
system can relocate the allocated “buddy
neighbor” of a freed block when it tries to form a
bigger freed block. Reading from the
INNODB_CMPMEM_RESET
table resets this count.
RELOCATION_TIME
The total time in microseconds used for relocating blocks of
size PAGE_SIZE. Reading from the table
INNODB_CMPMEM_RESET resets this count.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM\G
*************************** 1. row ***************************
page_size: 1024
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 2. row ***************************
page_size: 2048
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 3. row ***************************
page_size: 4096
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 4. row ***************************
page_size: 8192
buffer_pool_instance: 0
pages_used: 7673
pages_free: 15
relocation_ops: 4638
relocation_time: 0
*************************** 5. row ***************************
page_size: 16384
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
Use these tables to measure the effectiveness of
InnoDB table
compression in your
database.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
For usage information, see
Section 15.9.1.4, “Monitoring InnoDB Table Compression at Runtime” and
Section 15.14.1.3, “Using the Compression Information Schema Tables”.
For general information about InnoDB table
compression, see Section 15.9, “InnoDB Table and Page Compression”.
The INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET
tables provide status information on operations related to
compressed
InnoDB tables and indexes, with separate
statistics for each combination of database, table, and index, to
help you evaluate the performance and usefulness of compression
for specific tables.
For a compressed InnoDB table, both the table
data and all the secondary
indexes are compressed. In this context, the table data is
treated as just another index, one that happens to contain all the
columns: the clustered
index.
The INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET
tables have these columns:
DATABASE_NAME
The schema (database) containing the applicable table.
TABLE_NAME
The table to monitor for compression statistics.
INDEX_NAME
The index to monitor for compression statistics.
COMPRESS_OPS
The number of compression operations attempted. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out.
COMPRESS_OPS_OK
The number of successful compression operations. Subtract from
the COMPRESS_OPS value to get the number of
compression
failures. Divide by the COMPRESS_OPS
value to get the percentage of compression failures.
COMPRESS_TIME
The total time in seconds used for compressing data in this index.
UNCOMPRESS_OPS
The number of uncompression operations performed. Compressed
InnoDB pages are uncompressed whenever
compression
fails, or the
first time a compressed page is accessed in the
buffer pool and the
uncompressed page does not exist.
UNCOMPRESS_TIME
The total time in seconds used for uncompressing data in this index.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX\G
*************************** 1. row ***************************
database_name: employees
table_name: salaries
index_name: PRIMARY
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 23451
uncompress_time: 4
*************************** 2. row ***************************
database_name: employees
table_name: salaries
index_name: emp_no
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 1597
uncompress_time: 0
Use these tables to measure the effectiveness of
InnoDB table
compression for
specific tables, indexes, or both.
You must have the PROCESS
privilege to query these tables.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of these tables,
including data types and default values.
Because collecting separate measurements for every index
imposes substantial performance overhead,
INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET
statistics are not gathered by default. You must enable the
innodb_cmp_per_index_enabled
system variable before performing the operations on compressed
tables that you want to monitor.
For usage information, see
Section 15.9.1.4, “Monitoring InnoDB Table Compression at Runtime” and
Section 15.14.1.3, “Using the Compression Information Schema Tables”.
For general information about InnoDB table
compression, see Section 15.9, “InnoDB Table and Page Compression”.
The INNODB_COLUMNS table provides
metadata about InnoDB table columns.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INNODB_COLUMNS table has these
columns:
TABLE_ID
An identifier representing the table associated with the
column; the same value as
INNODB_TABLES.TABLE_ID.
NAME
The name of the column. These names can be uppercase or
lowercase depending on the
lower_case_table_names
setting. There are no special system-reserved names for
columns.
POS
The ordinal position of the column within the table, starting
from 0 and incrementing sequentially. When a column is
dropped, the remaining columns are reordered so that the
sequence has no gaps. The POS value for a
virtual generated column encodes the column sequence number
and ordinal position of the column. For more information, see
the POS column description in
Section 25.39.30, “The INFORMATION_SCHEMA INNODB_VIRTUAL Table”.
MTYPE
Stands for “main type”. A numeric identifier for
the column type. 1 = VARCHAR, 2 =
CHAR, 3 = FIXBINARY, 4 =
BINARY, 5 = BLOB, 6 =
INT, 7 = SYS_CHILD, 8 =
SYS, 9 = FLOAT, 10 =
DOUBLE, 11 = DECIMAL, 12
= VARMYSQL, 13 = MYSQL,
14 = GEOMETRY.
PRTYPE
The InnoDB “precise type”, a
binary value with bits representing MySQL data type, character
set code, and nullability.
LEN
The column length, for example 4 for INT
and 8 for BIGINT. For character columns in
multibyte character sets, this length value is the maximum
length in bytes needed to represent a definition such as
VARCHAR(; that
is, it might be
N)2*,
N3*, and so on
depending on the character encoding.
N
HAS_DEFAULT
A boolean value indicating whether a column that was added
instantly using
ALTER TABLE ...
ADD COLUMN with ALGORITHM=INSTANT
has a default value. All columns added instantly have a
default value, which makes this column an indicator of whether
the column was added instantly.
DEFAULT_VALUE
The initial default value of a column that was added instantly
using ALTER TABLE
... ADD COLUMN with
ALGORITHM=INSTANT. If the default value is
NULL or was not specified, this column
reports NULL. An explicitly specified
non-NULL default value is shown in an
internal binary format. Subsequent modifications of the column
default value do not change the value reported by this column.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
TABLE_ID: 71
NAME: col1
POS: 0
MTYPE: 6
PRTYPE: 1027
LEN: 4
HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 2. row ***************************
TABLE_ID: 71
NAME: col2
POS: 1
MTYPE: 2
PRTYPE: 524542
LEN: 10
HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 3. row ***************************
TABLE_ID: 71
NAME: col3
POS: 2
MTYPE: 1
PRTYPE: 524303
LEN: 10
HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_DATAFILES table provides
data file path information for InnoDB
file-per-table and general tablespaces.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INFORMATION_SCHEMA
FILES table reports metadata for
InnoDB tablespace types including
file-per-table tablespaces, general tablespaces, the system
tablespace, the global temporary tablespace, and undo
tablespaces.
The INNODB_DATAFILES table has these
columns:
SPACE
The tablespace ID.
PATH
The tablespace data file path. If a file-per-table tablespace is created in a location outside the MySQL data directory, the path value is a fully qualified directory path. Otherwise, the path is relative to the data directory.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57\G
*************************** 1. row ***************************
SPACE: 57
PATH: ./test/t1.ibd
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_FIELDS table provides
metadata about the key columns (fields) of
InnoDB indexes.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INNODB_FIELDS table has these
columns:
INDEX_ID
An identifier for the index associated with this key field;
the same value as INNODB_INDEXES.INDEX_ID.
NAME
The name of the original column from the table; the same value
as INNODB_COLUMNS.NAME.
POS
The ordinal position of the key field within the index, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS WHERE INDEX_ID = 117\G
*************************** 1. row ***************************
INDEX_ID: 117
NAME: col1
POS: 0
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_FOREIGN table provides
metadata about InnoDB
foreign keys.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INNODB_FOREIGN table has these
columns:
ID
The name (not a numeric value) of the foreign key index,
preceded by the schema (database) name (for example,
test/products_fk).
FOR_NAME
The name of the child table in this foreign key relationship.
REF_NAME
The name of the parent table in this foreign key relationship.
N_COLS
The number of columns in the foreign key index.
TYPE
A collection of bit flags with information about the foreign
key column, ORed together. 0 = ON DELETE/UPDATE
RESTRICT, 1 = ON DELETE CASCADE,
2 = ON DELETE SET NULL, 4 = ON
UPDATE CASCADE, 8 = ON UPDATE SET
NULL, 16 = ON DELETE NO ACTION,
32 = ON UPDATE NO ACTION.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN\G
*************************** 1. row ***************************
ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
N_COLS: 1
TYPE: 1
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_FOREIGN_COLS table
provides status information about InnoDB
foreign key columns.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INNODB_FOREIGN_COLS table has
these columns:
ID
The foreign key index associated with this index key field;
the same value as INNODB_FOREIGN.ID.
FOR_COL_NAME
The name of the associated column in the child table.
REF_COL_NAME
The name of the associated column in the parent table.
POS
The ordinal position of this key field within the foreign key index, starting from 0.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1'\G
*************************** 1. row ***************************
ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
POS: 0
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_FT_BEING_DELETED table
provides a snapshot of the
INNODB_FT_DELETED table; it is used
only during an OPTIMIZE TABLE
maintenance operation. When OPTIMIZE
TABLE is run, the
INNODB_FT_BEING_DELETED table is
emptied, and DOC_ID values are removed from the
INNODB_FT_DELETED table. Because the
contents of INNODB_FT_BEING_DELETED
typically have a short lifetime, this table has limited utility
for monitoring or debugging. For information about running
OPTIMIZE TABLE on tables with
FULLTEXT indexes, see
Section 12.9.6, “Fine-Tuning MySQL Full-Text Search”.
This table is empty initially. Before querying it, set the value
of the innodb_ft_aux_table system
variable to the name (including the database name) of the table
that contains the FULLTEXT index (for example,
test/articles). The output appears similar to
the example provided for the
INNODB_FT_DELETED table.
For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
The INNODB_FT_BEING_DELETED table has
these columns:
DOC_ID
The document ID of the row that is in the process of being
deleted. This value might reflect the value of an ID column
that you defined for the underlying table, or it can be a
sequence value generated by InnoDB when the
table contains no suitable column. This value is used when you
perform text searches, to skip rows in the
INNODB_FT_INDEX_TABLE table
before data for deleted rows is physically removed from the
FULLTEXT index by an
OPTIMIZE TABLE statement. For
more information, see Optimizing InnoDB Full-Text Indexes.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
You must have the PROCESS
privilege to query this table.
For more information about InnoDB
FULLTEXT search, see
Section 15.6.2.4, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_CONFIG table provides
metadata about the FULLTEXT index and
associated processing for an InnoDB table.
This table is empty initially. Before querying it, set the value
of the innodb_ft_aux_table system
variable to the name (including the database name) of the table
that contains the FULLTEXT index (for example,
test/articles).
For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
The INNODB_FT_CONFIG table has these
columns:
KEY
The name designating an item of metadata for an
InnoDB table containing a
FULLTEXT index.
The values for this column might change, depending on the
needs for performance tuning and debugging for
InnoDB full-text processing. The key names
and their meanings include:
optimize_checkpoint_limit: The number
of seconds after which an OPTIMIZE
TABLE run stops.
synced_doc_id: The next
DOC_ID to be issued.
stopword_table_name: The
database/table name for a
user-defined stopword table. The VALUE
column is empty if there is no user-defined stopword
table.
use_stopword: Indicates whether a
stopword table is used, which is defined when the
FULLTEXT index is created.
VALUE
The value associated with the corresponding
KEY column, reflecting some limit or
current value for an aspect of a FULLTEXT
index for an InnoDB table.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
+---------------------------+-------------------+
| KEY | VALUE |
+---------------------------+-------------------+
| optimize_checkpoint_limit | 180 |
| synced_doc_id | 0 |
| stopword_table_name | test/my_stopwords |
| use_stopword | 1 |
+---------------------------+-------------------+
This table is intended only for internal configuration. It is not intended for statistical information purposes.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
For more information about InnoDB
FULLTEXT search, see
Section 15.6.2.4, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_DEFAULT_STOPWORD table
holds a list of stopwords
that are used by default when creating a
FULLTEXT index on InnoDB
tables. For information about the default
InnoDB stopword list and how to define your own
stopword lists, see Section 12.9.4, “Full-Text Stopwords”.
For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
The INNODB_FT_DEFAULT_STOPWORD table
has these columns:
value
A word that is used by default as a stopword for
FULLTEXT indexes on
InnoDB tables. This is not used if you
override the default stopword processing with either the
innodb_ft_server_stopword_table
or the
innodb_ft_user_stopword_table
system variable.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
+-------+
36 rows in set (0.00 sec)
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
For more information about InnoDB
FULLTEXT search, see
Section 15.6.2.4, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_DELETED table stores
rows that are deleted from the FULLTEXT index
for an InnoDB table. To avoid expensive index
reorganization during DML operations for an
InnoDB FULLTEXT index, the
information about newly deleted words is stored separately,
filtered out of search results when you do a text search, and
removed from the main search index only when you issue an
OPTIMIZE TABLE statement for the
InnoDB table. For more information, see
Optimizing InnoDB Full-Text Indexes.
This table is empty initially. Before querying it, set the value
of the innodb_ft_aux_table system
variable to the name (including the database name) of the table
that contains the FULLTEXT index (for example,
test/articles).
For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
The INNODB_FT_DELETED table has these
columns:
DOC_ID
The document ID of the newly deleted row. This value might
reflect the value of an ID column that you defined for the
underlying table, or it can be a sequence value generated by
InnoDB when the table contains no suitable
column. This value is used when you perform text searches, to
skip rows in the
INNODB_FT_INDEX_TABLE table
before data for deleted rows is physically removed from the
FULLTEXT index by an
OPTIMIZE TABLE statement. For
more information, see Optimizing InnoDB Full-Text Indexes.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 6 |
| 7 |
| 8 |
+--------+
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
For more information about InnoDB
FULLTEXT search, see
Section 15.6.2.4, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_INDEX_CACHE table
provides token information about newly inserted rows in a
FULLTEXT index. To avoid expensive index
reorganization during DML operations, the information about newly
indexed words is stored separately, and combined with the main
search index only when OPTIMIZE
TABLE is run, when the server is shut down, or when the
cache size exceeds a limit defined by the
innodb_ft_cache_size or
innodb_ft_total_cache_size system
variable.
This table is empty initially. Before querying it, set the value
of the innodb_ft_aux_table system
variable to the name (including the database name) of the table
that contains the FULLTEXT index (for example,
test/articles).
For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
The INNODB_FT_INDEX_CACHE table has
these columns:
WORD
A word extracted from the text of a newly inserted row.
FIRST_DOC_ID
The first document ID in which this word appears in the
FULLTEXT index.
LAST_DOC_ID
The last document ID in which this word appears in the
FULLTEXT index.
DOC_COUNT
The number of rows in which this word appears in the
FULLTEXT index. The same word can occur
several times within the cache table, once for each
combination of DOC_ID and
POSITION values.
DOC_ID
The document ID of the newly inserted row. This value might
reflect the value of an ID column that you defined for the
underlying table, or it can be a sequence value generated by
InnoDB when the table contains no suitable
column.
POSITION
The position of this particular instance of the word within
the relevant document identified by the
DOC_ID value. The value does not represent
an absolute position; it is an offset added to the
POSITION of the previous instance of that
word.
This table is empty initially. Before querying it, set the
value of the
innodb_ft_aux_table system
variable to the name (including the database name) of the
table that contains the FULLTEXT index (for
example test/articles). The following
example demonstrates how to use the
innodb_ft_aux_table system
variable to show information about a
FULLTEXT index for a specified table.
mysql>USE test;mysql>CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body)) ENGINE=InnoDB;mysql>INSERT INTO articles (title,body) VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');mysql>SET GLOBAL innodb_ft_aux_table = 'test/articles';mysql>SELECT WORD, DOC_COUNT, DOC_ID, POSITIONFROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5;+------------+-----------+--------+----------+ | WORD | DOC_COUNT | DOC_ID | POSITION | +------------+-----------+--------+----------+ | 1001 | 1 | 4 | 0 | | after | 1 | 2 | 22 | | comparison | 1 | 5 | 44 | | configured | 1 | 6 | 20 | | database | 2 | 1 | 31 | +------------+-----------+--------+----------+
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
For more information about InnoDB
FULLTEXT search, see
Section 15.6.2.4, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_INDEX_TABLE table
provides information about the inverted index used to process text
searches against the FULLTEXT index of an
InnoDB table.
This table is empty initially. Before querying it, set the value
of the innodb_ft_aux_table system
variable to the name (including the database name) of the table
that contains the FULLTEXT index (for example,
test/articles).
For related usage information and examples, see Section 15.14.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
The INNODB_FT_INDEX_TABLE table has
these columns:
WORD
A word extracted from the text of the columns that are part of
a FULLTEXT.
FIRST_DOC_ID
The first document ID in which this word appears in the
FULLTEXT index.
LAST_DOC_ID
The last document ID in which this word appears in the
FULLTEXT index.
DOC_COUNT
The number of rows in which this word appears in the
FULLTEXT index. The same word can occur
several times within the cache table, once for each
combination of DOC_ID and
POSITION values.
DOC_ID
The document ID of the row containing the word. This value
might reflect the value of an ID column that you defined for
the underlying table, or it can be a sequence value generated
by InnoDB when the table contains no
suitable column.
POSITION
The position of this particular instance of the word within
the relevant document identified by the
DOC_ID value.
This table is empty initially. Before querying it, set the
value of the
innodb_ft_aux_table system
variable to the name (including the database name) of the
table that contains the FULLTEXT index (for
example, test/articles). The following
example demonstrates how to use the
innodb_ft_aux_table system
variable to show information about a
FULLTEXT index for a specified table.
Before information for newly inserted rows appears in
INNODB_FT_INDEX_TABLE, the
FULLTEXT index cache must be flushed to
disk. This is accomplished by running an
OPTIMIZE TABLE operation on the
indexed table with the
innodb_optimize_fulltext_only
system variable enabled. (The example disables that variable
again at the end because it is intended to be enabled only
temporarily.)
mysql>USE test;mysql>CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body)) ENGINE=InnoDB;mysql>INSERT INTO articles (title,body) VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');mysql>SET GLOBAL innodb_optimize_fulltext_only=ON;mysql>OPTIMIZE TABLE articles;+---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.articles | optimize | status | OK | +---------------+----------+----------+----------+ mysql>SET GLOBAL innodb_ft_aux_table = 'test/articles';mysql>SELECT WORD, DOC_COUNT, DOC_ID, POSITIONFROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;+------------+-----------+--------+----------+ | WORD | DOC_COUNT | DOC_ID | POSITION | +------------+-----------+--------+----------+ | 1001 | 1 | 4 | 0 | | after | 1 | 2 | 22 | | comparison | 1 | 5 | 44 | | configured | 1 | 6 | 20 | | database | 2 | 1 | 31 | +------------+-----------+--------+----------+ mysql>SET GLOBAL innodb_optimize_fulltext_only=OFF;
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
For more information about InnoDB
FULLTEXT search, see
Section 15.6.2.4, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_INDEXES table provides
metadata about InnoDB indexes.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INNODB_INDEXES table has these
columns:
INDEX_ID
An identifier for the index. Index identifiers are unique across all the databases in an instance.
NAME
The name of the index. Most indexes created implicitly by
InnoDB have consistent names but the index
names are not necessarily unique. Examples:
PRIMARY for a primary key index,
GEN_CLUST_INDEX for the index representing
a primary key when one is not specified, and
ID_IND, FOR_IND, and
REF_IND for foreign key constraints.
TABLE_ID
An identifier representing the table associated with the
index; the same value as
INNODB_TABLES.TABLE_ID.
TYPE
A numeric value derived from bit-level information that
identifies the index type. 0 = nonunique secondary index; 1 =
automatically generated clustered index
(GEN_CLUST_INDEX); 2 = unique nonclustered
index; 3 = clustered index; 32 = full-text index; 64 = spatial
index; 128 = secondary index on a
virtual
generated column.
N_FIELDS
The number of columns in the index key. For
GEN_CLUST_INDEX indexes, this value is 0
because the index is created using an artificial value rather
than a real table column.
PAGE_NO
The root page number of the index B-tree. For full-text
indexes, the PAGE_NO column is unused and
set to -1 (FIL_NULL) because the full-text
index is laid out in several B-trees (auxiliary tables).
SPACE
An identifier for the tablespace where the index resides. 0
means the InnoDB
system
tablespace. Any other number represents a table created
with a separate .ibd file in
file-per-table
mode. This identifier stays the same after a
TRUNCATE TABLE statement.
Because all indexes for a table reside in the same tablespace
as the table, this value is not necessarily unique.
MERGE_THRESHOLD
The merge threshold value for index pages. If the amount of
data in an index page falls below the
MERGE_THRESHOLD
value when a row is deleted or when a row is shortened by an
update operation, InnoDB attempts to merge
the index page with the neighboring index page. The default
threshold value is 50%. For more information, see
Section 15.8.11, “Configuring the Merge Threshold for Index Pages”.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 34\G
*************************** 1. row ***************************
INDEX_ID: 39
NAME: GEN_CLUST_INDEX
TABLE_ID: 34
TYPE: 1
N_FIELDS: 0
PAGE_NO: 3
SPACE: 23
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
INDEX_ID: 40
NAME: i1
TABLE_ID: 34
TYPE: 0
N_FIELDS: 1
PAGE_NO: 4
SPACE: 23
MERGE_THRESHOLD: 50
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_LOCKS table provides
information about each lock that an InnoDB
transaction has requested but not yet acquired, and each lock that
a transaction holds that is blocking another transaction.
This table is deprecated and is removed as of MySQL 8.0.1. Use
the Performance Schema data_locks
table instead. See Section 26.12.12.1, “The data_locks Table”.
Differences between INNODB_LOCKS
and data_locks:
If a transaction holds a lock,
INNODB_LOCKS displays the lock
only if another transaction is waiting for it.
data_locks displays the lock
regardless of whether any transaction is waiting for it.
The data_locks table has no
columns corresponding to LOCK_SPACE,
LOCK_PAGE, or
LOCK_REC.
The INNODB_LOCKS table requires
the global PROCESS privilege.
The data_locks table requires
the usual Performance Schema privilege of
SELECT on the table to be
selected from.
The following table shows the mapping from
INNODB_LOCKS columns to
data_locks columns. Use this
information to migrate applications from one table to the other.
Table 25.4 Mapping from INNODB_LOCKS to data_locks Columns
| INNODB_LOCKS Column | data_locks Column |
|---|---|
LOCK_ID |
ENGINE_LOCK_ID |
LOCK_TRX_ID |
ENGINE_TRANSACTION_ID |
LOCK_MODE |
LOCK_MODE |
LOCK_TYPE |
LOCK_TYPE |
LOCK_TABLE (combined schema/table names) |
OBJECT_SCHEMA (schema name),
OBJECT_NAME (table name) |
LOCK_INDEX |
INDEX_NAME |
LOCK_SPACE |
None |
LOCK_PAGE |
None |
LOCK_REC |
None |
LOCK_DATA |
LOCK_DATA |
The INNODB_LOCK_WAITS table contains
one or more rows for each blocked InnoDB
transaction, indicating the lock it has requested and any locks
that are blocking that request.
This table is deprecated and is removed as of MySQL 8.0.1. Use
the Performance Schema
data_lock_waits table instead. See
Section 26.12.12.2, “The data_lock_waits Table”.
The tables differ in the privileges required: The
INNODB_LOCK_WAITS table requires
the global PROCESS privilege. The
data_lock_waits table requires the
usual Performance Schema privilege of
SELECT on the table to be
selected from.
The following table shows the mapping from
INNODB_LOCK_WAITS columns to
data_lock_waits columns. Use this
information to migrate applications from one table to the other.
Table 25.5 Mapping from INNODB_LOCK_WAITS to data_lock_waits Columns
| INNODB_LOCK_WAITS Column | data_lock_waits Column |
|---|---|
REQUESTING_TRX_ID |
REQUESTING_ENGINE_TRANSACTION_ID |
REQUESTED_LOCK_ID |
REQUESTING_ENGINE_LOCK_ID |
BLOCKING_TRX_ID |
BLOCKING_ENGINE_TRANSACTION_ID |
BLOCKING_LOCK_ID |
BLOCKING_ENGINE_LOCK_ID |
The INNODB_METRICS table provides a
wide variety of InnoDB performance information,
complementing the specific focus areas of the Performance Schema
tables for InnoDB. With simple queries, you can
check the overall health of the system. With more detailed
queries, you can diagnose issues such as performance bottlenecks,
resource shortages, and application issues.
Each monitor represents a point within the
InnoDB source code that is instrumented to
gather counter information. Each counter can be started, stopped,
and reset. You can also perform these actions for a group of
counters using their common module name.
By default, relatively little data is collected. To start, stop,
and reset counters, set one of the system variables
innodb_monitor_enable,
innodb_monitor_disable,
innodb_monitor_reset, or
innodb_monitor_reset_all, using
the name of the counter, the name of the module, a wildcard match
for such a name using the “%” character, or the
special keyword all.
For usage information, see Section 15.14.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.
The INNODB_METRICS table has these
columns:
NAME
A unique name for the counter.
SUBSYSTEM
The aspect of InnoDB that the metric
applies to.
COUNT
The value since the counter was enabled.
MAX_COUNT
The maximum value since the counter was enabled.
MIN_COUNT
The minimum value since the counter was enabled.
AVG_COUNT
The average value since the counter was enabled.
COUNT_RESET
The counter value since it was last reset. (The
_RESET columns act like the lap counter on
a stopwatch: you can measure the activity during some time
interval, while the cumulative figures are still available in
COUNT, MAX_COUNT, and so
on.)
MAX_COUNT_RESET
The maximum counter value since it was last reset.
MIN_COUNT_RESET
The minimum counter value since it was last reset.
AVG_COUNT_RESET
The average counter value since it was last reset.
TIME_ENABLED
The timestamp of the last start.
TIME_DISABLED
The timestamp of the last stop.
TIME_ELAPSED
The elapsed time in seconds since the counter started.
TIME_RESET
The timestamp of the last reset.
STATUS
Whether the counter is still running
(enabled) or stopped
(disabled).
TYPE
Whether the item is a cumulative counter, or measures the current value of some resource.
COMMENT
The counter description.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME='dml_inserts'\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 3
MAX_COUNT: 3
MIN_COUNT: NULL
AVG_COUNT: 0.046153846153846156
COUNT_RESET: 3
MAX_COUNT_RESET: 3
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2014-12-04 14:18:28
TIME_DISABLED: NULL
TIME_ELAPSED: 65
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
Transaction counter COUNT values may differ
from the number of transaction events reported in Performance
Schema EVENTS_TRANSACTIONS_SUMMARY tables.
InnoDB counts only those transactions that
it executes, whereas Performance Schema collects events for
all non-aborted transactions initiated by the server,
including empty transactions.
The INNODB_SESSION_TEMP_TABLESPACES
table provides metadata about session temporary tablespaces used
for internal and user-created temporary tables. This table was
added in MySQL 8.0.13.
The INNODB_SESSION_TEMP_TABLESPACES
table has these columns:
ID
The process or session ID.
SPACE
The tablespace ID. A range of 400 thousand space IDs is reserved for session temporary tablespaces. Session temporary tablespaces are recreated each time the server is started. Space IDs are not persisted when the server is shut down and may be reused.
PATH
The tablespace data file path. A session temporary tablespace
has an ibt file extension.
SIZE
The size of the tablespace, in bytes.
STATE
The state of the tablespace. ACTIVE
indicates that the tablespace is currently used by a session.
INACTIVE indicates that the tablespace is
in the pool of available session temporary tablespaces.
PURPOSE
The purpose of the tablespace. INTRINSIC
indicates that the tablespace is used for optimized internal
temporary tables use by the optimizer.
SLAVE indicates that the tablespace is
allocated for storing user-created temporary tables on a
replication slave. USER indicates that the
tablespace is used for user-created temporary tables.
NONE indicates that the tablespace is not
in use.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-------+----------+-----------+
| 8 | 4294566162 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
| 8 | 4294566161 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | USER |
| 0 | 4294566153 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566154 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566155 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566156 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566157 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566158 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566159 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566160 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+-------+----------+-----------+
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_TABLES table provides
metadata about InnoDB tables.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INNODB_TABLES table has these
columns:
TABLE_ID
An identifier for the InnoDB table. This
value is unique across all databases in the instance.
NAME
The name of the table, preceded by the schema (database) name
where appropriate (for example, test/t1).
Names of databases and user tables are in the same case as
they were originally defined, possibly influenced by the
lower_case_table_names
setting.
FLAG
A numeric value that represents bit-level information about table format and storage characteristics.
N_COLS
The number of columns in the table. The number reported
includes three hidden columns that are created by
InnoDB (DB_ROW_ID,
DB_TRX_ID, and
DB_ROLL_PTR). The number reported also
includes virtual
generated columns, if present.
SPACE
An identifier for the tablespace where the table resides. 0
means the InnoDB
system
tablespace. Any other number represents either a
file-per-table
tablespace or a general tablespace. This identifier stays the
same after a TRUNCATE TABLE
statement. For file-per-table tablespaces, this identifier is
unique for tables across all databases in the instance.
ROW_FORMAT
The table's row format (Compact,
Redundant, Dynamic, or
Compressed).
ZIP_PAGE_SIZE
The zip page size. Applies only to tables with a row format of
Compressed.
SPACE_TYPE
The type of tablespace to which the table belongs. Possible
values include System for the system
tablespace, General for general
tablespaces, and Single for file-per-table
tablespaces. Tables assigned to the system tablespace using
CREATE TABLE or
ALTER TABLE
TABLESPACE=innodb_system have a
SPACE_TYPE of General.
For more information, see CREATE
TABLESPACE.
INSTANT_COLS
The number of columns in the table prior to adding the first
instant column using ALTER TABLE
... ADD COLUMN with
ALGORITHM=INSTANT.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TABLE_ID = 214\G
*************************** 1. row ***************************
TABLE_ID: 214
NAME: test/t1
FLAG: 129
N_COLS: 4
SPACE: 233
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: General
INSTANT_COLS: 0
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_TABLESPACES table provides
metadata about InnoDB file-per-table, general,
and undo tablespaces.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INFORMATION_SCHEMA
FILES table reports metadata for
InnoDB tablespace types including
file-per-table tablespaces, general tablespaces, the system
tablespace, the global temporary tablespace, and undo
tablespaces.
The INNODB_TABLESPACES table has
these columns:
SPACE
The tablespace ID.
NAME
The schema (database) and table name.
FLAG
A numeric value that represents bit-level information about tablespace format and storage characteristics.
ROW_FORMAT
The tablespace row format (Compact or
Redundant, Dynamic or
Compressed, or Undo).
The data in this column is interpreted from the tablespace
flag information that resides in the data file.
There is no way to determine from this flag information if the
tablespace row format is Redundant or
Compact, which is why one of the possible
ROW_FORMAT values is Compact or
Redundant.
PAGE_SIZE
The tablespace page size. The data in this column is
interpreted from the tablespace flags information that resides
in the .ibd
file.
ZIP_PAGE_SIZE
The tablespace zip page size. The data in this column is
interpreted from the tablespace flags information that resides
in the .ibd
file.
SPACE_TYPE
The type of tablespace. Possible values include
General for general tablespaces,
Single for file-per-table tablespaces,
System for the system tablespace, and
Undo for undo tablespaces.
FS_BLOCK_SIZE
The file system block size, which is the unit size used for
hole punching. This column pertains to the
InnoDB
transparent page
compression feature.
FILE_SIZE
The apparent size of the file, which represents the maximum
size of the file, uncompressed. This column pertains to the
InnoDB
transparent page
compression feature.
ALLOCATED_SIZE
The actual size of the file, which is the amount of space
allocated on disk. This column pertains to the
InnoDB
transparent page
compression feature.
SERVER_VERSION
The MySQL version that created the tablespace, or the MySQL
version into which the tablespace was imported, or the version
of the last major MySQL version upgrade. The value is
unchanged by a release series upgrade, such as an upgrade from
MySQL 8.0.x to
8.0.y. The value can be considered
a “creation” marker or “certified”
marker for the tablespace.
SPACE_VERSION
The tablespace version, used to track changes to the tablespace format.
ENCRYPTION
Whether the tablespace is encrypted. This column was added in MySQL 8.0.13.
STATE
The tablespace state. This column was added in MySQL 8.0.14.
For file-per-table and general tablespaces, states include:
normal: The tablespace is normal and
active.
discarded: The tablespace was discarded
by an ALTER
TABLE ... DISCARD TABLESPACE statement.
corrupted: The tablespace is identified
by InnoDB as corrupted.
For undo tablespaces, states include:
active: Rollback segments in the undo
tablespace can be allocated to new transactions.
inactive: Rollback segments in the undo
tablespace are no longer used by new transactions. The
truncate process is in progress. The undo tablespace was
either selected by the purge thread implicitly or was made
inactive by an
ALTER
UNDO TABLESPACE ... SET INACTIVE statement.
empty: The undo tablespace was
truncated and is no longer active. It is ready to be
dropped or made active again by an
ALTER
UNDO TABLESPACE ... SET INACTIVE statement.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE = 26\G
*************************** 1. row ***************************
SPACE: 26
NAME: test/t1
FLAG: 0
ROW_FORMAT: Compact or Redundant
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 65536
SERVER_VERSION: 8.0.4
SPACE_VERSION: 1
ENCRYPTION: N
STATE: normal
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_TABLESPACES_BRIEF table
provides space ID, name, path, flag, and space type metadata for
file-per-table, general, undo, and system tablespaces.
INNODB_TABLESPACES provides the same
metadata but loads more slowly because other metadata provided by
the table, such as FS_BLOCK_SIZE,
FILE_SIZE, and
ALLOCATED_SIZE, must be loaded dynamically.
Space and path metadata is also provided by the
INNODB_DATAFILES table.
The INNODB_TABLESPACES_BRIEF table
has these columns:
SPACE
The tablespace ID.
NAME
The tablespace name. For file-per-table tablespaces, the name
is in the form of
schema/table_name.
PATH
The tablespace data file path. If a file-per-table tablespace is created in a location outside the MySQL data directory, the path value is a fully qualified directory path. Otherwise, the path is relative to the data directory.
FLAG
A numeric value that represents bit-level information about tablespace format and storage characteristics.
SPACE_TYPE
The type of tablespace. Possible values include
General for InnoDB
general tablespaces, Single for
InnoDB file-per-table tablespaces, and
System for the InnoDB
system tablespace.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF WHERE SPACE = 7;
+-------+---------+---------------+-------+------------+
| SPACE | NAME | PATH | FLAG | SPACE_TYPE |
+-------+---------+---------------+-------+------------+
| 7 | test/t1 | ./test/t1.ibd | 16417 | Single |
+-------+---------+---------------+-------+------------+
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_TABLESTATS table provides
a view of low-level status information about
InnoDB tables. This data is used by the MySQL
optimizer to calculate which index to use when querying an
InnoDB table. This information is derived from
in-memory data structures rather than data stored on disk. There
is no corresponding internal InnoDB system
table.
InnoDB tables are represented in this view if
they have been opened since the last server restart and have not
aged out of the table cache. Tables for which persistent stats are
available are always represented in this view.
Table statistics are updated only for
DELETE or
UPDATE operations that modify
indexed columns. Statistics are not updated by operations that
modify only nonindexed columns.
ANALYZE TABLE clears table
statistics and sets the STATS_INITIALIZED
column to Uninitialized. Statistics are
collected again the next time the table is accessed.
For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
The INNODB_TABLESTATS table has these
columns:
TABLE_ID
An identifier representing the table for which statistics are
available; the same value as
INNODB_TABLES.TABLE_ID.
NAME
The name of the table; the same value as
INNODB_TABLES.NAME.
STATS_INITIALIZED
The value is Initialized if the statistics
are already collected, Uninitialized if
not.
NUM_ROWS
The current estimated number of rows in the table. Updated after each DML operation. The value could be imprecise if uncommitted transactions are inserting into or deleting from the table.
CLUST_INDEX_SIZE
The number of pages on disk that store the clustered index,
which holds the InnoDB table data in
primary key order. This value might be null if no statistics
are collected yet for the table.
OTHER_INDEX_SIZE
The number of pages on disk that store all secondary indexes for the table. This value might be null if no statistics are collected yet for the table.
MODIFIED_COUNTER
The number of rows modified by DML operations, such as
INSERT, UPDATE,
DELETE, and also cascade operations from
foreign keys. This column is reset each time table statistics
are recalculated
AUTOINC
The next number to be issued for any auto-increment-based
operation. The rate at which the AUTOINC
value changes depends on how many times auto-increment numbers
have been requested and how many numbers are granted per
request.
REF_COUNT
When this counter reaches zero, the table metadata can be evicted from the table cache.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71\G
*************************** 1. row ***************************
TABLE_ID: 71
NAME: test/t1
STATS_INITIALIZED: Initialized
NUM_ROWS: 1
CLUST_INDEX_SIZE: 1
OTHER_INDEX_SIZE: 0
MODIFIED_COUNTER: 1
AUTOINC: 0
REF_COUNT: 1
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_TEMP_TABLE_INFO table
provides information about user-created InnoDB
temporary tables that are active in an InnoDB
instance. It does not provide information about internal
InnoDB temporary tables used by the optimizer.
The INNODB_TEMP_TABLE_INFO table is
created when first queried, exists only in memory, and is not
persisted to disk.
For usage information and examples, see Section 15.14.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.
The INNODB_TEMP_TABLE_INFO table has
these columns:
TABLE_ID
The table ID of the temporary table.
NAME
The name of the temporary table.
N_COLS
The number of columns in the temporary table. The number
includes three hidden columns created by
InnoDB (DB_ROW_ID,
DB_TRX_ID, and
DB_ROLL_PTR).
SPACE
The ID of the temporary tablespace where the temporary table resides.
mysql>CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G*************************** 1. row *************************** TABLE_ID: 97 NAME: #sql8c88_43_0 N_COLS: 4 SPACE: 76
This table is useful primarily for expert-level monitoring.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_TRX table provides
information about every transaction (excluding read-only
transactions) currently executing inside
InnoDB, including whether the transaction is
waiting for a lock, when the transaction started, and the SQL
statement the transaction is executing, if any.
For usage information, see Section 15.14.2.1, “Using InnoDB Transaction and Locking Information”.
The INNODB_TRX table has these
columns:
TRX_ID
A unique transaction ID number, internal to
InnoDB. These IDs are not created for
transactions that are read only and nonlocking. For details,
see Section 8.5.3, “Optimizing InnoDB Read-Only Transactions”.
TRX_WEIGHT
The weight of a transaction, reflecting (but not necessarily
the exact count of) the number of rows altered and the number
of rows locked by the transaction. To resolve a deadlock,
InnoDB selects the transaction with the
smallest weight as the “victim” to roll back.
Transactions that have changed nontransactional tables are
considered heavier than others, regardless of the number of
altered and locked rows.
TRX_STATE
The transaction execution state. Permitted values are
RUNNING, LOCK WAIT,
ROLLING BACK, and
COMMITTING.
TRX_STARTED
The transaction start time.
TRX_REQUESTED_LOCK_ID
The ID of the lock the transaction is currently waiting for,
if TRX_STATE is LOCK
WAIT; otherwise NULL. To obtain
details about the lock, join this column with the
ENGINE_LOCK_ID column of the Performance
Schema data_locks table.
TRX_WAIT_STARTED
The time when the transaction started waiting on the lock, if
TRX_STATE is LOCK WAIT;
otherwise NULL.
TRX_MYSQL_THREAD_ID
The MySQL thread ID. To obtain details about the thread, join
this column with the ID column of the
INFORMATION_SCHEMA
PROCESSLIST table, but see
Section 15.14.2.3, “Persistence and Consistency of InnoDB Transaction and Locking
Information”.
TRX_QUERY
The SQL statement that is being executed by the transaction.
TRX_OPERATION_STATE
The transaction's current operation, if any; otherwise
NULL.
TRX_TABLES_IN_USE
The number of InnoDB tables used while
processing the current SQL statement of this transaction.
TRX_TABLES_LOCKED
The number of InnoDB tables that the
current SQL statement has row locks on. (Because these are row
locks, not table locks, the tables can usually still be read
from and written to by multiple transactions, despite some
rows being locked.)
TRX_LOCK_STRUCTS
The number of locks reserved by the transaction.
TRX_LOCK_MEMORY_BYTES
The total size taken up by the lock structures of this transaction in memory.
TRX_ROWS_LOCKED
The approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction.
TRX_ROWS_MODIFIED
The number of modified and inserted rows in this transaction.
TRX_CONCURRENCY_TICKETS
A value indicating how much work the current transaction can
do before being swapped out, as specified by the
innodb_concurrency_tickets
system variable.
TRX_ISOLATION_LEVEL
The isolation level of the current transaction.
TRX_UNIQUE_CHECKS
Whether unique checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load.
TRX_FOREIGN_KEY_CHECKS
Whether foreign key checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load.
TRX_LAST_FOREIGN_KEY_ERROR
The detailed error message for the last foreign key error, if
any; otherwise NULL.
TRX_ADAPTIVE_HASH_LATCHED
Whether the adaptive hash index is locked by the current
transaction. When the adaptive hash index search system is
partitioned, a single transaction does not lock the entire
adaptive hash index. Adaptive hash index partitioning is
controlled by
innodb_adaptive_hash_index_parts,
which is set to 8 by default.
TRX_ADAPTIVE_HASH_TIMEOUT
Whether to relinquish the search latch immediately for the
adaptive hash index, or reserve it across calls from MySQL.
When there is no adaptive hash index contention, this value
remains zero and statements reserve the latch until they
finish. During times of contention, it counts down to zero,
and statements release the latch immediately after each row
lookup. When the adaptive hash index search system is
partitioned (controlled by
innodb_adaptive_hash_index_parts),
the value remains 0.
TRX_IS_READ_ONLY
A value of 1 indicates the transaction is read only.
TRX_AUTOCOMMIT_NON_LOCKING
A value of 1 indicates the transaction is a
SELECT statement that does not
use the FOR UPDATE or LOCK IN
SHARED MODE clauses, and is executing with
autocommit enabled so that
the transaction contains only this one statement. When this
column and TRX_IS_READ_ONLY are both 1,
InnoDB optimizes the transaction to reduce
the overhead associated with transactions that change table
data.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1510
trx_state: RUNNING
trx_started: 2014-11-19 13:24:40
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 586739
trx_mysql_thread_id: 2
trx_query: DELETE FROM employees.salaries WHERE salary > 65000
trx_operation_state: updating or deleting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 3003
trx_lock_memory_bytes: 450768
trx_rows_locked: 1407513
trx_rows_modified: 583736
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 15.14.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
The INNODB_VIRTUAL table provides
metadata about InnoDB
virtual generated
columns and columns upon which virtual generated columns
are based.
A row appears in the INNODB_VIRTUAL
table for each column upon which a virtual generated column is
based.
The INNODB_VIRTUAL table has these
columns:
TABLE_ID
An identifier representing the table associated with the
virtual column; the same value as
INNODB_TABLES.TABLE_ID.
POS
The position value of the virtual generated column. The value is large because it encodes the column sequence number and ordinal position. The formula used to calculate the value uses a bitwise operation:
((nth virtual generated column for the InnoDB instance + 1) << 16)
+ the ordinal position of the virtual generated column
For example, if the first virtual generated column in the
InnoDB instance is the third column of the
table, the formula is (0 + 1) << 16) +
2. The first virtual generated column in the
InnoDB instance is always number 0. As the
third column in the table, the ordinal position of the virtual
generated column is 2. Ordinal positions are counted from 0.
BASE_POS
The ordinal position of the columns upon which a virtual generated column is based.
mysql>CREATE TABLE `t1` (`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,`c` int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,`h` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_VIRTUALWHERE TABLE_ID IN(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLESWHERE NAME LIKE "test/t1");+----------+-------+----------+ | TABLE_ID | POS | BASE_POS | +----------+-------+----------+ | 98 | 65538 | 0 | | 98 | 65538 | 1 | +----------+-------+----------+
If a constant value is assigned to a
virtual
generated column, as in the following table, an entry
for the column does not appear in the
INNODB_VIRTUAL table. For an entry to
appear, a virtual generated column must have a base column.
CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) GENERATED ALWAYS AS (5) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
However, metadata for such a column does appear in the
INNODB_COLUMNS table.
You must have the PROCESS
privilege to query this table.
Use the INFORMATION_SCHEMA
COLUMNS table or the
SHOW COLUMNS statement to view
additional information about the columns of this table,
including data types and default values.
As of MySQL 8.0.14, the thread pool
INFORMATION_SCHEMA tables are also available as
Performance Schema tables. (See
Section 26.12.15, “Performance Schema Thread Pool Tables”.) The
INFORMATION_SCHEMA tables are deprecated and
will be removed in a future MySQL version. Applications should
transition away from the old tables to the new tables. For
example, if an application uses this query:
SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_STATE;
The application should use this query instead:
SELECT * FROM performance_schema.tp_thread_state;
The following sections describe the
INFORMATION_SCHEMA tables associated with the
thread pool plugin (see Section 5.6.3, “MySQL Enterprise Thread Pool”). They provide
information about thread pool operation:
TP_THREAD_GROUP_STATE:
Information about thread pool thread group states
TP_THREAD_GROUP_STATS:
Thread group statistics
TP_THREAD_STATE:
Information about thread pool thread states
Rows in these tables represent snapshots in time. In the case of
TP_THREAD_STATE, all rows for a thread group
comprise a snapshot in time. Thus, the MySQL server holds the mutex
of the thread group while producing the snapshot. But it does not
hold mutexes on all thread groups at the same time, to prevent a
statement against TP_THREAD_STATE from blocking
the entire MySQL server.
The INFORMATION_SCHEMA thread pool tables are
implemented by individual plugins and the decision whether to load
one can be made independently of the others (see
Section 5.6.3.2, “Thread Pool Installation”). However, the content of
all the tables depends on the thread pool plugin being enabled. If a
table plugin is enabled but the thread pool plugin is not, the table
becomes visible and can be accessed but will be empty.
As of MySQL 8.0.14, the thread pool
INFORMATION_SCHEMA tables are also available
as Performance Schema tables. (See
Section 26.12.15, “Performance Schema Thread Pool Tables”.) The
INFORMATION_SCHEMA tables are deprecated and
will be removed in a future MySQL version. Applications should
transition away from the old tables to the new tables. For
example, if an application uses this query:
SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_GROUP_STATE;
The application should use this query instead:
SELECT * FROM performance_schema.tp_thread_group_state;
The TP_THREAD_GROUP_STATE table has one row per
thread group in the thread pool. Each row provides information
about the current state of a group.
For descriptions of the columns in the
INFORMATION_SCHEMA
TP_THREAD_GROUP_STATE table, see
Section 26.12.15.1, “The tp_thread_group_state Table”. The Performance
Schema tp_thread_group_state table
has equivalent columns.
As of MySQL 8.0.14, the thread pool
INFORMATION_SCHEMA tables are also available
as Performance Schema tables. (See
Section 26.12.15, “Performance Schema Thread Pool Tables”.) The
INFORMATION_SCHEMA tables are deprecated and
will be removed in a future MySQL version. Applications should
transition away from the old tables to the new tables. For
example, if an application uses this query:
SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_GROUP_STATS;
The application should use this query instead:
SELECT * FROM performance_schema.tp_thread_group_stats;
The TP_THREAD_GROUP_STATS table reports
statistics per thread group. There is one row per group.
For descriptions of the columns in the
INFORMATION_SCHEMA
TP_THREAD_GROUP_STATS table, see
Section 26.12.15.2, “The tp_thread_group_stats Table”. The Performance
Schema tp_thread_group_stats table
has equivalent columns.
As of MySQL 8.0.14, the thread pool
INFORMATION_SCHEMA tables are also available
as Performance Schema tables. (See
Section 26.12.15, “Performance Schema Thread Pool Tables”.) The
INFORMATION_SCHEMA tables are deprecated and
will be removed in a future MySQL version. Applications should
transition away from the old tables to the new tables. For
example, if an application uses this query:
SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_STATE;
The application should use this query instead:
SELECT * FROM performance_schema.tp_thread_state;
The TP_THREAD_STATE table has one row per
thread created by the thread pool to handle connections.
For descriptions of the columns in the
INFORMATION_SCHEMA
TP_THREAD_STATE table, see
Section 26.12.15.3, “The tp_thread_state Table”. The Performance Schema
tp_thread_state table has equivalent
columns.
The following sections describe the
INFORMATION_SCHEMA tables associated with the
CONNECTION_CONTROL plugin.
This table provides information about the current number of consecutive failed connection attempts per client user/host combination.
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
has these columns:
USERHOST
The user/host combination of a client that has failed
connection attempts, in
'
format.
user_name'@'host_name'
FAILED_ATTEMPTS
The current number of consecutive failed connection attempts
for the USERHOST value. This counts all
failed attempts, regardless of whether they were delayed. The
number of attempts for which the server added a delay to its
response is the difference between the
FAILED_ATTEMPTS value and the
connection_control_failed_connections_threshold
system variable value.
The
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
plugin must be activated for this table to be available, and
the CONNECTION_CONTROL plugin must be
activated or the table contents will always be empty. See
Section 6.4.2, “The Connection-Control Plugins”.
The table contains rows only for clients that have had one or more consecutive failed connection attempts without a subsequent successful attempt. When a client connects successfully, its failed-connection count is reset to zero and the server removes any row corresponding to the client.
Assigning a value to the
connection_control_failed_connections_threshold
system variable at runtime resets all accumulated
failed-connection counters to zero, which causes the table to
become empty.
Some extensions to SHOW statements
accompany the implementation of
INFORMATION_SCHEMA:
INFORMATION_SCHEMA is an information database,
so its name is included in the output from
SHOW DATABASES. Similarly,
SHOW TABLES can be used with
INFORMATION_SCHEMA to obtain a list of its
tables:
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_INFORMATION_SCHEMA |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
SHOW COLUMNS and
DESCRIBE can display information
about the columns in individual
INFORMATION_SCHEMA tables.
SHOW statements that accept a
LIKE clause to limit the rows
displayed also permit a WHERE clause that
specifies more general conditions that selected rows must satisfy:
SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW DATABASES SHOW FUNCTION STATUS SHOW INDEX SHOW OPEN TABLES SHOW PROCEDURE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW TRIGGERS SHOW VARIABLES
The WHERE clause, if present, is evaluated
against the column names displayed by the
SHOW statement. For example, the
SHOW CHARACTER SET statement
produces these output columns:
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
...
To use a WHERE clause with
SHOW CHARACTER SET, you would refer
to those column names. As an example, the following statement
displays information about character sets for which the default
collation contains the string 'japanese':
mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
This statement displays the multibyte character sets:
mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+