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
is a database within each
MySQL instance, the place that stores information about all the
other databases that the MySQL server maintains. The
INFORMATION_SCHEMA
database contains several
read-only tables. They are actually views, not base tables, so there
are no files associated with them, and you cannot set triggers on
them. Also, there is no database directory with that name.
Although you can select INFORMATION_SCHEMA
as the
default database with a USE
statement, you can only read the contents of tables, not perform
INSERT
,
UPDATE
, or
DELETE
operations on them.
Here is an example of a statement that retrieves information from
INFORMATION_SCHEMA
:
mysql>SELECT table_name, table_type, engine
->FROM information_schema.tables
->WHERE table_schema = 'db5'
->ORDER BY table_name;
+------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | fk | BASE TABLE | InnoDB | | fk2 | BASE TABLE | InnoDB | | goto | BASE TABLE | MyISAM | | into | BASE TABLE | MyISAM | | k | BASE TABLE | MyISAM | | kurs | BASE TABLE | MyISAM | | loop | BASE TABLE | MyISAM | | pk | BASE TABLE | InnoDB | | t | BASE TABLE | MyISAM | | t2 | BASE TABLE | MyISAM | | t3 | BASE TABLE | MyISAM | | t7 | BASE TABLE | MyISAM | | tables | BASE TABLE | MyISAM | | v | VIEW | NULL | | v2 | VIEW | NULL | | v3 | VIEW | NULL | | v56 | VIEW | NULL | +------------+------------+--------+ 17 rows in set (0.01 sec)
Explanation: The statement requests a list of all the tables in
database db5
, showing just three pieces of
information: the name of the table, its type, and its storage
engine.
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.1.7.9, “Collation and 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 21.32, “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.4, “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 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_name
AND TABLE_SCHEMA = SCHEMA()
condition to the
WHERE
clause of a query that retrieves
information from an INFORMATION_SCHEMA
table.
For information about INFORMATION_SCHEMA
tables
specific to the InnoDB
storage engine,
see Section 21.31, “INFORMATION_SCHEMA Tables for InnoDB”.
For answers to questions that are often asked concerning the
INFORMATION_SCHEMA
database, see
Section A.7, “MySQL 5.7 FAQ: INFORMATION_SCHEMA”.
The CHARACTER_SETS
table provides
information about available character sets.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
CHARACTER_SET_NAME | Charset | |
DEFAULT_COLLATE_NAME | Default collation | |
DESCRIPTION | Description | MySQL extension |
MAXLEN | Maxlen | MySQL extension |
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS [WHERE CHARACTER_SET_NAME LIKE 'wild
'] SHOW CHARACTER SET [LIKE 'wild
']
The COLLATIONS
table provides
information about collations for each character set.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset | MySQL extension |
ID | Id | MySQL extension |
IS_DEFAULT | Default | MySQL extension |
IS_COMPILED | Compiled | MySQL extension |
SORTLEN | Sortlen | MySQL extension |
COLLATION_NAME
is the collation name.
CHARACTER_SET_NAME
is the name of the
character set with which the collation is associated.
ID
is the collation ID.
IS_DEFAULT
indicates whether the collation
is the default for its character set.
IS_COMPILED
indicates whether the character
set is compiled into the server.
SORTLEN
is related to the amount of memory
required to sort strings expressed in the character set.
Collation information is also available from the
SHOW COLLATION
statement. 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 columns are equivalent to the first two display
fields that we get from SHOW
COLLATION
.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset |
The COLUMNS
table provides
information about columns in tables.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | Field | |
ORDINAL_POSITION | see notes | |
COLUMN_DEFAULT | Default | |
IS_NULLABLE | Null | |
DATA_TYPE | Type | |
CHARACTER_MAXIMUM_LENGTH | Type | |
CHARACTER_OCTET_LENGTH | ||
NUMERIC_PRECISION | Type | |
NUMERIC_SCALE | Type | |
DATETIME_PRECISION | Type | |
CHARACTER_SET_NAME | ||
COLLATION_NAME | Collation | |
COLUMN_TYPE | Type | MySQL extension |
COLUMN_KEY | Key | MySQL extension |
EXTRA | Extra | MySQL extension |
PRIVILEGES | Privileges | MySQL extension |
COLUMN_COMMENT | Comment | MySQL extension |
GENERATION_EXPRESSION | MySQL extension |
Notes:
In SHOW
, the
Type
display includes values from several
different COLUMNS
columns.
ORDINAL_POSITION
is necessary because you
might want to say ORDER BY
ORDINAL_POSITION
. Unlike
SHOW
,
SELECT
does not have automatic
ordering.
CHARACTER_OCTET_LENGTH
should be the same
as CHARACTER_MAXIMUM_LENGTH
, except for
multibyte character sets.
CHARACTER_SET_NAME
can be derived from
Collation
. For example, if you say
SHOW FULL COLUMNS FROM t
, and you see in
the Collation
column a value of
latin1_swedish_ci
, the character set is
what is before the first underscore:
latin1
.
GENERATION_EXPRESSION
is nonempty for
generated columns and displays the expression used to compute
column values. For information about generated columns, see
CREATE TABLE and Generated Columns. This column
was added in MySQL 5.7.6.
As of MySQL 5.7.6, the EXTRA
column
contains VIRTUAL GENERATED
or
VIRTUAL STORED
for generated columns.
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. This information comes from
the mysql.columns_priv
grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
GRANTEE | '
value | |
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
In the output from
SHOW FULL
COLUMNS
, the privileges are all in one field and in
lowercase, for example,
select,insert,update,references
. In
COLUMN_PRIVILEGES
, there is one
privilege per row, in uppercase.
PRIVILEGE_TYPE
can contain one (and only
one) of these values: SELECT
,
INSERT
,
UPDATE
,
REFERENCES
.
If the user has GRANT OPTION
privilege, IS_GRANTABLE
should be
YES
. Otherwise,
IS_GRANTABLE
should be
NO
. The output does not list
GRANT OPTION
as a separate
privilege.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES SHOW GRANTS ...
The ENGINES
table provides
information about storage engines.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
ENGINE | Engine | MySQL extension |
SUPPORT | Support | MySQL extension |
COMMENT | Comment | MySQL extension |
TRANSACTIONS | Transactions | MySQL extension |
XA | XA | MySQL extension |
SAVEPOINTS | Savepoints | MySQL extension |
Notes:
The ENGINES
table is a
nonstandard table. Its contents correspond to the columns of
the SHOW ENGINES
statement. For
descriptions of its columns, see
Section 13.7.5.16, “SHOW ENGINES Syntax”.
See also Section 13.7.5.16, “SHOW ENGINES Syntax”.
The EVENTS
table provides information
about scheduled events, which are discussed in
Section 20.4, “Using the Event Scheduler”. The SHOW
Name
values correspond to column names of the
SHOW EVENTS
statement.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
EVENT_CATALOG | def , MySQL extension | |
EVENT_SCHEMA | Db | MySQL extension |
EVENT_NAME | Name | MySQL extension |
DEFINER | Definer | MySQL extension |
TIME_ZONE | Time zone | MySQL extension |
EVENT_BODY | MySQL extension | |
EVENT_DEFINITION | MySQL extension | |
EVENT_TYPE | Type | MySQL extension |
EXECUTE_AT | Execute at | MySQL extension |
INTERVAL_VALUE | Interval value | MySQL extension |
INTERVAL_FIELD | Interval field | MySQL extension |
SQL_MODE | MySQL extension | |
STARTS | Starts | MySQL extension |
ENDS | Ends | MySQL extension |
STATUS | Status | MySQL extension |
ON_COMPLETION | MySQL extension | |
CREATED | MySQL extension | |
LAST_ALTERED | MySQL extension | |
LAST_EXECUTED | MySQL extension | |
EVENT_COMMENT | MySQL extension | |
ORIGINATOR | Originator | MySQL extension |
CHARACTER_SET_CLIENT | character_set_client | MySQL extension |
COLLATION_CONNECTION | collation_connection | MySQL extension |
DATABASE_COLLATION | Database Collation | MySQL extension |
Notes:
The EVENTS
table is a nonstandard
table.
EVENT_CATALOG
: The value of this column is
always def
.
EVENT_SCHEMA
: The name of the schema
(database) to which this event belongs.
EVENT_NAME
: The name of the event.
DEFINER
: The account of the user who
created the event, in
'
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; in MySQL 5.7, this is always
SQL
.
This column is not to be confused with the column of the same
name (now named EVENT_DEFINITION
) that
existed in earlier MySQL versions.
EVENT_DEFINITION
: The text of the SQL
statement making up the event's
DO
clause; in other words, the
statement executed by this event.
EVENT_TYPE
: The event repetition type,
either ONE TIME
(transient) or
RECURRING
(repeating).
EXECUTE_AT
: For a one-time event, this is
the DATETIME
value specified in
the AT
clause of the
CREATE EVENT
statement used to
create the event, or of the last ALTER
EVENT
statement that modified the event. The value
shown in this column reflects the addition or subtraction of
any INTERVAL
value included in the event's
AT
clause. For example, if an event is
created using ON SCHEDULE AT CURRENT_TIMESTAMP +
'1:6' DAY_HOUR
, and the event was created at
2006-02-09 14:05:30, the value shown in this column would be
'2006-02-10 20:05:30'
.
If the event's timing is determined by an
EVERY
clause instead of an
AT
clause (that is, if the event is
recurring), the value of this column is
NULL
.
INTERVAL_VALUE
: For recurring events, this
column contains the numeric portion of the event's
EVERY
clause.
For a one-time event (that is, an event whose timing is
determined by an AT
clause), this column is
NULL
.
INTERVAL_FIELD
: For recurring events, this
column contains the units portion of the
EVERY
clause governing the timing of the
event. Thus, this column contains a value such as
'YEAR
',
'QUARTER
', 'DAY
', and so
on.
For a one-time event (that is, an event whose timing is
determined by an AT
clause), this column is
NULL
.
SQL_MODE
: The SQL mode in effect when the
event was created or altered, and under which the event
executes. For the permitted values, see
Section 5.1.7, “Server SQL Modes”.
STARTS
: For a recurring event whose
definition includes a STARTS
clause, this
column contains the corresponding
DATETIME
value. As with the
EXECUTE_AT
column, this value resolves any
expressions used.
If there is no STARTS
clause affecting the
timing of the event, this column is NULL
ENDS
: For a recurring event whose
definition includes a ENDS
clause, this
column contains the corresponding
DATETIME
value. As with the
EXECUTE_AT
column, this value resolves any
expressions used.
If there is no ENDS
clause affecting the
timing of the event, this column is NULL
.
STATUS
: One of the three values
ENABLED
, DISABLED
, or
SLAVESIDE_DISABLED
.
SLAVESIDE_DISABLED
indicates that the
creation of the event occurred on another MySQL server acting
as a replication master and was replicated to the current
MySQL server which is acting as a slave, but the event is not
presently being executed on the slave. See
Section 17.4.1.12, “Replication of Invoked Features”, for more
information.
ON_COMPLETION
: One of the two values
PRESERVE
or NOT
PRESERVE
.
CREATED
: The date and time when the event
was created. This is a
TIMESTAMP
value.
LAST_ALTERED
: The date and time when the
event was last modified. This is a
TIMESTAMP
value. If the event
has not been modified since its creation, this column holds
the same value as the CREATED
column.
LAST_EXECUTED
: The date and time when the
event last executed. A DATETIME
value. If the event has never executed, this column is
NULL
.
LAST_EXECUTED
indicates when the event
started. As a result, the ENDS
column is
never less than LAST_EXECUTED
.
EVENT_COMMENT
: The text of a comment, if
the event has one. If not, the value of this column is an
empty string.
ORIGINATOR
: The server ID of the MySQL
server on which the event was created; used in replication.
The default value is 0.
CHARACTER_SET_CLIENT
: The session value of
the character_set_client
system variable when the event was created.
COLLATION_CONNECTION
: The session value of
the collation_connection
system variable when the event was created.
DATABASE_COLLATION
: The collation of the
database with which the event is associated.
Example: Suppose that the user
jon@ghidora
creates an event named
e_daily
, and then modifies it a few minutes
later using an ALTER EVENT
statement, as shown here:
DELIMITER | CREATE EVENT e_daily ON SCHEDULE EVERY 1 DAY COMMENT 'Saves total number of sessions then clears the table each day' DO BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END | DELIMITER ; ALTER EVENT e_daily ENABLED;
(Note that comments can span multiple lines.)
This user can then run the following
SELECT
statement, and obtain the
output shown:
mysql>SELECT * FROM INFORMATION_SCHEMA.EVENTS
>WHERE EVENT_NAME = 'e_daily'
>AND EVENT_SCHEMA = 'myschema'\G
*************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: test EVENT_NAME: e_daily DEFINER: me@localhost 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: STARTS: 2008-09-03 12:13:39 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2008-09-03 12:13:39 LAST_ALTERED: 2008-09-03 12:13:39 LAST_EXECUTED: NULL EVENT_COMMENT: Saves total number of sessions then clears the table each day ORIGINATOR: 1 CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci
Times in the EVENTS
table are
displayed using the event time zone or the current session time
zone, as described in Section 20.4.4, “Event Metadata”.
See also Section 13.7.5.18, “SHOW EVENTS Syntax”.
The FILES
table provides information
about the files in which MySQL tablespace data is stored.
INFORMATION_SCHEMA.FILES
provides
information about InnoDB
data files (MySQL
5.7.8 and later). In MySQL Cluster this table also provides
information about the files in which MySQL Cluster Disk Data
tables are stored. For information specific to
InnoDB
, see
InnoDB Notes, later in this section;
for information specific to MySQL Cluster, see
NDB Notes.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
FILE_ID | MySQL extension | |
FILE_NAME | MySQL extension | |
FILE_TYPE | MySQL extension | |
TABLESPACE_NAME | MySQL extension | |
TABLE_CATALOG | MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
TABLE_NAME | MySQL extension | |
LOGFILE_GROUP_NAME | MySQL extension | |
LOGFILE_GROUP_NUMBER | MySQL extension | |
ENGINE | MySQL extension | |
FULLTEXT_KEYS | MySQL extension | |
DELETED_ROWS | MySQL extension | |
UPDATE_COUNT | MySQL extension | |
FREE_EXTENTS | MySQL extension | |
TOTAL_EXTENTS | MySQL extension | |
EXTENT_SIZE | MySQL extension | |
INITIAL_SIZE | MySQL extension | |
MAXIMUM_SIZE | MySQL extension | |
AUTOEXTEND_SIZE | MySQL extension | |
CREATION_TIME | MySQL extension | |
LAST_UPDATE_TIME | MySQL extension | |
LAST_ACCESS_TIME | MySQL extension | |
RECOVER_TIME | MySQL extension | |
TRANSACTION_COUNTER | MySQL extension | |
VERSION | MySQL extension | |
ROW_FORMAT | MySQL extension | |
TABLE_ROWS | MySQL extension | |
AVG_ROW_LENGTH | MySQL extension | |
DATA_LENGTH | MySQL extension | |
MAX_DATA_LENGTH | MySQL extension | |
INDEX_LENGTH | MySQL extension | |
DATA_FREE | MySQL extension | |
CREATE_TIME | MySQL extension | |
UPDATE_TIME | MySQL extension | |
CHECK_TIME | MySQL extension | |
CHECKSUM | MySQL extension | |
STATUS | MySQL extension | |
EXTRA | MySQL extension |
The following notes apply to InnoDB
data files.
INFORMATION_SCHEMA.FILES
fields that
are not described below are not applicable to
InnoDB
and report a NULL
value.
Data reported by
INFORMATION_SCHEMA.FILES
is
reported from the InnoDB
in-memory cache
for open files. By comparison,
INFORMATION_SCHEMA.INNODB_SYS_DATAFILES
reports data from the InnoDB
SYS_DATAFILES
internal data dictionary
table.
The data reported by
INFORMATION_SCHEMA.FILES
includes
temporary table tablespace data. This data is not available in
the internal SYS_DATAFILES
data dictionary
table, and is therefore not reported by
INNODB_SYS_DATAFILES
.
Undo tablespace data is reported by
INFORMATION_SCHEMA.FILES
when
separate undo tablespaces are configured using the
innodb_undo_tablespaces
configuration option.
FILE_ID
is the tablespace ID, also referred
to as the space_id
or
fil_space_t::id
.
FILE_NAME
is the name of the data file.
File-per-table and general tablespaces have a
.ibd
file name extension. Undo
tablespaces are prefixed by undo
. The
system tablespace is prefixed by ibdata
.
Temporary table tablespaces are prefixed by
ibtmp
. The file name includes the file
path, which may be relative to the MySQL data directory
(datadir
).
FILE_TYPE
is the tablespace file type.
There are three possible file types for
InnoDB
files. TABLESPACE
is the file type for any system, general, or file-per-table
tablespace file that holds tables, indexes, or other forms of
user data. TEMPORARY
is the file type for
temporary table tablespaces. UNDO LOG
is
the file type for undo log tablespaces, which hold undo
records. By default, undo records are stored in the system
tablespace. Separate undo log tablespaces can be added using
the innodb_undo_tablespaces
option.
TABLESPACE_NAME
is the SQL name for the
tablespace. A general tablespace name is the
SYS_TABLESPACES.NAME
value. For other
tablespace files, names start with innodb_
,
such as innodb_system
,
innodb_undo
, and
innodb_file_per_table
. The file-per-table
tablespace name format is
innodb_file_per_table_
,
where ##
##
is the tablespace ID.
ENGINE
is the storage engine. For
InnoDB
files, the value is always
InnoDB
.
FREE_EXTENTS
is the number of fully free
extents in the current data file.
TOTAL_EXTENTS
is the number of full extents
used in the current data file. Any partial extent at the end
of the file is not counted.
EXTENT_SIZE
is 1048576 (1MB) for files with
a 4k, 8k, or 16k page size. Extent size is 2097152 bytes (2MB)
for files with a 32k page size, and 4194304 (4MB) for files
with a 64k page size.
INFORMATION_SCHEMA.FILES
does not
report InnoDB
page size. Page size is
defined by the
innodb_page_size
option.
Extent size information can also be retrieved from
INNODB_SYS_TABLESPACES
where
FILES.FILE_ID =
INNODB_SYS_TABLESPACES.SPACE_ID
.
INITIAL_SIZE
is the initial size of the
file, in bytes.
MAXIMUM_SIZE
is the maximum number of bytes
allowed in the file. The value is NULL
for
all data files except for predefined system tablespace data
files. Maximum system tablespace file size is defined by
innodb_data_file_path
.
Maximum temporary table tablespace file size is defined by
innodb_temp_data_file_path
. A
NULL
value for a predefined system
tablespace data file indicates that a file size limit was not
defined explicitly.
AUTOEXTEND_SIZE
is the auto-extend size
defined by
innodb_data_file_path
for the
system tablespace, or defined by
innodb_temp_data_file_path
for temporary table tablespaces.
DATA_FREE
is the total amount of free space
(in bytes) for the entire tablespace. Predefined system
tablespaces, which include the system tablespace and temporary
table tablespaces, may have one or more data files.
STATUS
is NORMAL
by
default. InnoDB
file-per-table tablespaces
may report IMPORTING
, which indicates that
the tablespace is not yet available.
The following query returns all data pertinent to
InnoDB
tablespaces.
mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE FROM INFORMATION_SCHEMA.FILES \G
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 18.4.10, “ndb_desc — Describe NDB Tables”.
FILE_ID
column values are auto-generated.
FILE_NAME
is 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
is one of the values
UNDOFILE
, DATAFILE
, or
TABLESPACE
.
TABLESPACE_NAME
is the name of the
tablespace with which the file is associated.
Currently, the value of the
TABLESPACE_CATALOG
column is always
NULL
.
TABLE_NAME
is the name of the Disk Data
table with which the file is associated, if any.
The LOGFILE_GROUP_NAME
column gives the
name of the log file group to which the log file or data file
belongs.
For an UNDO
log file, the
LOGFILE_GROUP_NUMBER
contains the
auto-generated ID number of the log file group to which the
log file belongs.
For a MySQL Cluster Disk Data log file or data file, the value
of the ENGINE
column is always
NDB
or
NDBCLUSTER
.
For a MySQL Cluster Disk Data log file or data file, the value
of the FULLTEXT_KEYS
column is always
empty.
The FREE EXTENTS
column displays the number
of extents which have not yet been used by the file. The
TOTAL EXTENTS
column show the total number
of extents allocated to the file.
The difference between these two 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';
You can approximate the amount of disk space in use by the
file by multiplying this 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. See
Section 13.1.19, “CREATE TABLESPACE Syntax”, for more information.
The INITIAL_SIZE
column shows the size in
bytes of the file. 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.
For MySQL Cluster Disk Data files, the value of the
MAXIMUM_SIZE
column is always the same as
INITIAL_SIZE
, and the
AUTOEXTEND_SIZE
column is always empty.
The CREATION_TIME
column shows the date and
time when the file was created. The
LAST_UPDATE_TIME
column displays the date
and time when the file was last modified. The
LAST_ACCESSED
column provides the date and
time when the file was last accessed by the server.
Currently, the values of these columns 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
.
For MySQL Cluster Disk Data files, the value of the
RECOVER_TIME
and
TRANSACTION_COUNTER
columns is always
0
.
For MySQL Cluster Disk Data files, the following columns are
always NULL
:
VERSION
ROW_FORMAT
TABLE_ROWS
AVG_ROW_LENGTH
DATA_LENGTH
MAX_DATA_LENGTH
INDEX_LENGTH
DATA_FREE
CREATE_TIME
UPDATE_TIME
CHECK_TIME
CHECKSUM
For MySQL Cluster Disk Data files, the value of the
STATUS
column is always
NORMAL
.
For MySQL Cluster Disk Data files, the
EXTRA
column shows which data node the file
belongs to, as each data node has its own copy of the file.
Suppose that you use this statement on a MySQL Cluster with
four data nodes:
CREATE LOGFILE GROUP mygroup ADD UNDOFILE 'new_undo.dat' INITIAL_SIZE 2G ENGINE NDB;
After running the CREATE LOGFILE
GROUP
statement successfully, you should see a
result similar to the one shown here for this query against
the FILES
table:
mysql>SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
->FROM INFORMATION_SCHEMA.FILES
->WHERE FILE_NAME = 'new_undo.dat';
+--------------------+-------------+----------------+ | LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA | +--------------------+-------------+----------------+ | mygroup | UNDO FILE | CLUSTER_NODE=3 | | mygroup | UNDO FILE | CLUSTER_NODE=4 | | mygroup | UNDO FILE | CLUSTER_NODE=5 | | mygroup | UNDO FILE | CLUSTER_NODE=6 | +--------------------+-------------+----------------+ 4 rows in set (0.01 sec)
The FILES
table is a nonstandard
table.
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
. Currently, 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 MySQL Cluster, and you create one using the following statement:
mysql>CREATE LOGFILE GROUP lg1
->ADD UNDOFILE 'undofile.dat'
->INITIAL_SIZE = 16M
->UNDO_BUFFER_SIZE = 1M
->ENGINE = NDB;
Query OK, 0 rows affected (3.81 sec)
You can now see this NULL
row when you
query the FILES
table:
mysql>SELECT DISTINCT
->FILE_NAME AS File,
->FREE_EXTENTS AS Free,
->TOTAL_EXTENTS AS Total,
->EXTENT_SIZE AS Size,
->INITIAL_SIZE AS Initial
->FROM INFORMATION_SCHEMA.FILES;
+--------------+---------+---------+------+----------+ | File | Free | Total | Size | Initial | +--------------+---------+---------+------+----------+ | undofile.dat | NULL | 4194304 | 4 | 16777216 | | NULL | 4184068 | NULL | 4 | NULL | +--------------+---------+---------+------+----------+ 2 rows in set (0.01 sec)
The total number of free extents available for undo logging is
always somewhat less than the sum of the
TOTAL_EXTENTS
column values for all undo
files in the log file group due to overhead required for
maintaining the undo files. This can be seen by adding a
second undo file to the log file group, then repeating the
previous query against the FILES
table:
mysql>ALTER LOGFILE GROUP lg1
->ADD UNDOFILE 'undofile02.dat'
->INITIAL_SIZE = 4M
->ENGINE = NDB;
Query OK, 0 rows affected (1.02 sec) mysql>SELECT DISTINCT
->FILE_NAME AS File,
->FREE_EXTENTS AS Free,
->TOTAL_EXTENTS AS Total,
->EXTENT_SIZE AS Size,
->INITIAL_SIZE AS Initial
->FROM INFORMATION_SCHEMA.FILES;
+----------------+---------+---------+------+----------+ | File | Free | Total | Size | Initial | +----------------+---------+---------+------+----------+ | undofile.dat | NULL | 4194304 | 4 | 16777216 | | undofile02.dat | NULL | 1048576 | 4 | 4194304 | | NULL | 5223944 | NULL | 4 | NULL | +----------------+---------+---------+------+----------+ 3 rows in set (0.01 sec)
The amount of free space in bytes which is available for undo logging by Disk Data tables using this log file group can be approximated by multiplying the number of free extents by the initial size:
mysql>SELECT
->FREE_EXTENTS AS 'Free Extents',
->FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
->FROM INFORMATION_SCHEMA.FILES
->WHERE LOGFILE_GROUP_NAME = 'lg1'
->AND FILE_NAME IS NULL;
+--------------+------------+ | Free Extents | Free Bytes | +--------------+------------+ | 5223944 | 20895776 | +--------------+------------+ 1 row in set (0.02 sec)
If you create a MySQL Cluster Disk Data table and then insert some rows into it, you can see approximately how much space remains for undo logging afterward, for example:
mysql>CREATE TABLESPACE ts1
->ADD DATAFILE 'data1.dat'
->USE LOGFILE GROUP lg1
->INITIAL_SIZE 512M
->ENGINE = NDB;
Query OK, 0 rows affected (8.71 sec) mysql>CREATE TABLE dd (
->c1 INT NOT NULL PRIMARY KEY,
->c2 INT,
->c3 DATE
->)
->TABLESPACE ts1 STORAGE DISK
->ENGINE = NDB;
Query OK, 0 rows affected (2.11 sec) 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');
Query OK, 4 rows affected (0.01 sec) mysql>SELECT
->FREE_EXTENTS AS 'Free Extents',
->FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
->FROM INFORMATION_SCHEMA.FILES
->WHERE LOGFILE_GROUP_NAME = 'lg1'
->AND FILE_NAME IS NULL;
+--------------+------------+ | Free Extents | Free Bytes | +--------------+------------+ | 5207565 | 20830260 | +--------------+------------+ 1 row in set (0.01 sec)
An additional row is present in the
FILES
table for any MySQL 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
. Currently, the value of the
ENGINE
column is always
NDBCLUSTER
.
There are no SHOW
statements
associated with the FILES
table.
For additional information, and examples of creating and dropping MySQL Cluster Disk Data objects, see Section 18.5.13, “MySQL Cluster Disk Data Tables”.
As of MySQL 5.7.6, the value of the
show_compatibility_56
system
variable affects the information available from the tables
described here. For details, see the description of that
variable in Section 5.1.4, “Server System Variables”.
As of MySQL 5.7.6, information available from the tables
described here is also available from the Performance Schema.
The INFORMATION_SCHEMA
tables are deprecated
in preference to the Performance Schema tables and will be
removed in a future MySQL release. For advice on migrating away
from the INFORMATION_SCHEMA
tables to the
Performance Schema tables, see
Section 22.17, “Migrating to Performance Schema System and Status Variable Tables”.
The GLOBAL_STATUS
and SESSION_STATUS
tables provide information about server status variables. Their
contents correspond to the information produced by the
SHOW GLOBAL
STATUS
and
SHOW SESSION
STATUS
statements (see Section 13.7.5.35, “SHOW STATUS Syntax”).
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
VARIABLE_NAME | Variable_name | |
VARIABLE_VALUE | Value |
Notes:
The VARIABLE_VALUE
column for each of these
tables is defined as VARCHAR(1024)
.
As of MySQL 5.7.6, the value of the
show_compatibility_56
system
variable affects the information available from the tables
described here. For details, see the description of that
variable in Section 5.1.4, “Server System Variables”.
As of MySQL 5.7.6, information available from the tables
described here is also available from the Performance Schema.
The INFORMATION_SCHEMA
tables are deprecated
in preference to the Performance Schema tables and will be
removed in a future MySQL release. For advice on migrating away
from the INFORMATION_SCHEMA
tables to the
Performance Schema tables, see
Section 22.17, “Migrating to Performance Schema System and Status Variable Tables”.
The
GLOBAL_VARIABLES
and
SESSION_VARIABLES
tables provide information about server status variables. Their
contents correspond to the information produced by the
SHOW GLOBAL
VARIABLES
and
SHOW SESSION
VARIABLES
statements (see
Section 13.7.5.39, “SHOW VARIABLES Syntax”).
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
VARIABLE_NAME | Variable_name | |
VARIABLE_VALUE | Value |
Notes:
The VARIABLE_VALUE
column for each of these
tables is defined as VARCHAR(1024)
. For
variables with very long values that are not completely
displayed, use SELECT
as a
workaround. For example:
SELECT @@GLOBAL.innodb_data_file_path;
The KEY_COLUMN_USAGE
table describes
which key columns have constraints.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
CONSTRAINT_CATALOG | def | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
ORDINAL_POSITION | ||
POSITION_IN_UNIQUE_CONSTRAINT | ||
REFERENCED_TABLE_SCHEMA | ||
REFERENCED_TABLE_NAME | ||
REFERENCED_COLUMN_NAME |
Notes:
If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.
The value of ORDINAL_POSITION
is the
column's position within the constraint, not the column's
position within the table. Column positions are numbered
beginning with 1.
The value of POSITION_IN_UNIQUE_CONSTRAINT
is NULL
for unique and primary-key
constraints. For foreign-key constraints, it is the ordinal
position in key of the table that is being referenced.
Suppose that there are two tables name t1
and t3
that have the following definitions:
CREATE TABLE t1 ( s1 INT, s2 INT, s3 INT, PRIMARY KEY(s3) ) ENGINE=InnoDB; CREATE TABLE t3 ( s1 INT, s2 INT, s3 INT, KEY(s1), CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3) ) ENGINE=InnoDB;
For those two tables, the
KEY_COLUMN_USAGE
table has two
rows:
One row with CONSTRAINT_NAME
=
'PRIMARY'
,
TABLE_NAME
= 't1'
,
COLUMN_NAME
= 's3'
,
ORDINAL_POSITION
=
1
,
POSITION_IN_UNIQUE_CONSTRAINT
=
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 a MySQL Cluster as API nodes. This information is used
when populating the
server_operations
and
server_transactions
tables of
the ndbinfo
MySQL 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 MySQL 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 |
| mysql_old_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
that wass supplied with the MySQL Cluster distribution, or one
that was built from the MySQL Cluster sources with
NDB
storage engine support enabled.
It is not available in the standard MySQL 5.6 Server.
The OPTIMIZER_TRACE
table provides
information produced by the optimizer tracing capability. To
enable tracking, use the
optimizer_trace
system variable.
For details, see
MySQL
Internals: Tracing the Optimizer.
The PARAMETERS
table provides
information about stored procedure and function parameters, and
about return values for stored functions. Parameter information is
similar to the contents of the param_list
column in the mysql.proc
table.
INFORMATION_SCHEMA Name | mysql.proc Name | Remarks |
---|---|---|
SPECIFIC_CATALOG | def | |
SPECIFIC_SCHEMA | db | routine database |
SPECIFIC_NAME | name | routine name |
ORDINAL_POSITION | 1, 2, 3, ... for parameters, 0 for function RETURNS
clause | |
PARAMETER_MODE | IN , OUT , INOUT
(NULL for RETURNS ) | |
PARAMETER_NAME | parameter name (NULL for RETURNS ) | |
DATA_TYPE | same as for COLUMNS table | |
CHARACTER_MAXIMUM_LENGTH | same as for COLUMNS table | |
CHARACTER_OCTET_LENGTH | same as for COLUMNS table | |
NUMERIC_PRECISION | same as for COLUMNS table | |
NUMERIC_SCALE | same as for COLUMNS table | |
DATETIME_PRECISION | same as for COLUMNS table | |
CHARACTER_SET_NAME | same as for COLUMNS table | |
COLLATION_NAME | same as for COLUMNS table | |
DTD_IDENTIFIER | same as for COLUMNS table | |
ROUTINE_TYPE | type | same as for ROUTINES table |
Notes:
For successive parameters of a stored procedure or function,
the ORDINAL_POSITION
values are 1, 2, 3,
and so forth. For a stored function, there is also a row that
describes the data type for the RETURNS
clause. The return value is not a true parameter, so the row
that describes it has these unique characteristics:
The 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.
The PARTITIONS
table provides
information about table partitions. See
Chapter 19, Partitioning, for more information about
partitioning tables.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TABLE_CATALOG | MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
TABLE_NAME | MySQL extension | |
PARTITION_NAME | MySQL extension | |
SUBPARTITION_NAME | MySQL extension | |
PARTITION_ORDINAL_POSITION | MySQL extension | |
SUBPARTITION_ORDINAL_POSITION | MySQL extension | |
PARTITION_METHOD | MySQL extension | |
SUBPARTITION_METHOD | MySQL extension | |
PARTITION_EXPRESSION | MySQL extension | |
SUBPARTITION_EXPRESSION | MySQL extension | |
PARTITION_DESCRIPTION | MySQL extension | |
TABLE_ROWS | MySQL extension | |
AVG_ROW_LENGTH | MySQL extension | |
DATA_LENGTH | MySQL extension | |
MAX_DATA_LENGTH | MySQL extension | |
INDEX_LENGTH | MySQL extension | |
DATA_FREE | MySQL extension | |
CREATE_TIME | MySQL extension | |
UPDATE_TIME | MySQL extension | |
CHECK_TIME | MySQL extension | |
CHECKSUM | MySQL extension | |
PARTITION_COMMENT | MySQL extension | |
NODEGROUP | MySQL extension | |
TABLESPACE_NAME | MySQL extension |
Notes:
The PARTITIONS
table is a
nonstandard table.
Each record in this table corresponds to an individual partition or subpartition of a partitioned table.
TABLE_CATALOG
: This column is always
def
.
TABLE_SCHEMA
: This column contains the name
of the database to which the table belongs.
TABLE_NAME
: This column contains the name
of the table containing the partition.
PARTITION_NAME
: The name of the partition.
SUBPARTITION_NAME
: If the
PARTITIONS
table record
represents a subpartition, then this column contains the name
of subpartition; otherwise it is NULL
.
PARTITION_ORDINAL_POSITION
: All partitions
are indexed in the same order as they are defined, with
1
being the number assigned to the first
partition. The indexing can change as partitions are added,
dropped, and reorganized; the number shown is this column
reflects the current order, taking into account any indexing
changes.
SUBPARTITION_ORDINAL_POSITION
:
Subpartitions within a given partition are also indexed and
reindexed in the same manner as partitions are indexed within
a table.
PARTITION_METHOD
: One of the values
RANGE
, LIST
,
HASH
, LINEAR HASH
,
KEY
, or LINEAR KEY
; that
is, one of the available partitioning types as discussed in
Section 19.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 19.2.6, “Subpartitioning”.
PARTITION_EXPRESSION
: This is the
expression for the partitioning function used in the
CREATE TABLE
or
ALTER TABLE
statement that
created the table's current partitioning scheme.
For example, consider a partitioned table created in the
test
database using this statement:
CREATE TABLE tp ( c1 INT, c2 INT, c3 VARCHAR(25) ) PARTITION BY HASH(c1 + c2) PARTITIONS 4;
The PARTITION_EXPRESSION
column in a
PARTITIONS table record for a partition from this table
displays c1 + c2
, as shown here:
mysql>SELECT DISTINCT PARTITION_EXPRESSION
>FROM INFORMATION_SCHEMA.PARTITIONS
>WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';
+----------------------+ | PARTITION_EXPRESSION | +----------------------+ | c1 + c2 | +----------------------+ 1 row in set (0.09 sec)
SUBPARTITION_EXPRESSION
: This works in the
same fashion for the subpartitioning expression that defines
the subpartitioning for a table as
PARTITION_EXPRESSION
does for the
partitioning expression used to define a table's partitioning.
If the table has no subpartitions, then this column is
NULL
.
PARTITION_DESCRIPTION
: This column is used
for RANGE and LIST partitions. For a RANGE
partition, it contains the value set in the partition's
VALUES LESS THAN
clause, which can be
either an integer or MAXVALUE
. For a
LIST
partition, this column contains the
values defined in the partition's VALUES IN
clause, which is a comma-separated list of integer values.
For partitions whose PARTITION_METHOD
is
other than RANGE
or
LIST
, this column is always
NULL
.
TABLE_ROWS
: The number of table rows in the
partition.
For partitioned InnoDB
tables,
the row count given in the TABLE_ROWS
column is only an estimated value used in SQL optimization,
and may not always be exact.
AVG_ROW_LENGTH
: The average length of the
rows stored in this partition or subpartition, in bytes.
This is the same as DATA_LENGTH
divided by
TABLE_ROWS
.
DATA_LENGTH
: The total length of all rows
stored in this partition or subpartition, in bytes—that
is, the total number of bytes stored in the partition or
subpartition.
MAX_DATA_LENGTH
: The maximum number of
bytes that can be stored in this partition or subpartition.
INDEX_LENGTH
: The length of the index file
for this partition or subpartition, in bytes.
DATA_FREE
: The number of bytes allocated to
the partition or subpartition but not used.
CREATE_TIME
: The time of the partition's or
subpartition's creation.
Prior to MySQL 5.7.8, for partitioned
InnoDB
tables, this column was
always NULL
. The correct creation time is
shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)
UPDATE_TIME
: The time that the partition or
subpartition was last modified.
Prior to MySQL 5.7.8, for partitioned
InnoDB
tables, this column was
always NULL
. The correct modification time
is shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)
CHECK_TIME
: The last time that the table to
which this partition or subpartition belongs was checked.
For partitioned InnoDB
tables,
this column is always NULL
.
CHECKSUM
: The checksum value, if any;
otherwise, this column is NULL
.
PARTITION_COMMENT
: This column contains the
text of any comment made for the partition.
In MySQL 5.7, the maximum length for a partition
comment is defined as 1024 characters, and the display width
of the PARTITION_COMMENT
column is also
1024, characters to match this limit (Bug #11748924, Bug
#37728).
The default value for this column is an empty string.
NODEGROUP
: This is the nodegroup to which
the partition belongs. This is relevant only to MySQL Cluster
tables; otherwise the value of this column is always
0
.
TABLESPACE_NAME
: This column contains the
name of the tablespace to which the partition belongs. The
value of this column is always DEFAULT
.
A nonpartitioned table has one record in
INFORMATION_SCHEMA.PARTITIONS
;
however, the values of the PARTITION_NAME
,
SUBPARTITION_NAME
,
PARTITION_ORDINAL_POSITION
,
SUBPARTITION_ORDINAL_POSITION
,
PARTITION_METHOD
,
SUBPARTITION_METHOD
,
PARTITION_EXPRESSION
,
SUBPARTITION_EXPRESSION
, and
PARTITION_DESCRIPTION
columns are all
NULL
. (The
PARTITION_COMMENT
column in this case is
blank.)
The PLUGINS
table provides
information about server plugins.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
PLUGIN_NAME | Name | MySQL extension |
PLUGIN_VERSION | MySQL extension | |
PLUGIN_STATUS | Status | MySQL extension |
PLUGIN_TYPE | Type | MySQL extension |
PLUGIN_TYPE_VERSION | MySQL extension | |
PLUGIN_LIBRARY | Library | MySQL extension |
PLUGIN_LIBRARY_VERSION | MySQL extension | |
PLUGIN_AUTHOR | MySQL extension | |
PLUGIN_DESCRIPTION | MySQL extension | |
PLUGIN_LICENSE | License | MySQL extension |
LOAD_OPTION | MySQL extension |
Notes:
The PLUGINS
table is a
nonstandard table.
PLUGIN_NAME
is the name used to refer to
the plugin in statements such as INSTALL
PLUGIN
and UNINSTALL
PLUGIN
.
PLUGIN_VERSION
is the version from the
plugin's general type descriptor.
PLUGIN_STATUS
indicates the plugin status,
one of ACTIVE
, INACTIVE
,
DISABLED
, or DELETED
.
PLUGIN_TYPE
indicates the type of plugin,
such as STORAGE ENGINE
,
INFORMATION_SCHEMA
, or
AUTHENTICATION
.
PLUGIN_TYPE_VERSION
is the version from the
plugin's type-specific descriptor.
PLUGIN_LIBRARY
is the name of the plugin
shared object file. This is the name used to refer to the
plugin file in statements such as INSTALL
PLUGIN
and UNINSTALL
PLUGIN
. This file is located in the directory named
by the plugin_dir
system
variable. If the library name is NULL
, the
plugin is compiled in and cannot be uninstalled with
UNINSTALL PLUGIN
.
PLUGIN_LIBRARY_VERSION
indicates the plugin
API interface version.
PLUGIN_AUTHOR
names the plugin author.
PLUGIN_DESCRIPTION
provides a short
description of the plugin.
PLUGIN_LICENSE
indicates how the plugin is
licensed; for example, GPL
.
LOAD_OPTION
indicates how the plugin was
loaded. The value is OFF
,
ON
, FORCE
, or
FORCE_PLUS_PERMANENT
. See
Section 5.1.8.1, “Installing and Uninstalling Plugins”.
For plugins installed with INSTALL
PLUGIN
, the PLUGIN_NAME
and
PLUGIN_LIBRARY
values are also registered in
the mysql.plugin
table.
These statements are equivalent:
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_LICENSE FROM INFORMATION_SCHEMA.PLUGINS; SHOW PLUGINS;
For information about plugin data structures that form the basis
of the information in the PLUGINS
table, see Section 25.2, “The MySQL Plugin API”.
Plugin information is also available using the
SHOW PLUGINS
statement. See
Section 13.7.5.25, “SHOW PLUGINS Syntax”.
The PROCESSLIST
table provides
information about which threads are running.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
ID | Id | MySQL extension |
USER | User | MySQL extension |
HOST | Host | MySQL extension |
DB | db | MySQL extension |
COMMAND | Command | MySQL extension |
TIME | Time | MySQL extension |
STATE | State | MySQL extension |
INFO | Info | MySQL extension |
For an extensive description of the table columns, see Section 13.7.5.29, “SHOW PROCESSLIST Syntax”.
Notes:
The PROCESSLIST
table is a
nonstandard table.
Like the output from the corresponding
SHOW
statement, the
PROCESSLIST
table will only show
information about your own threads, unless you have the
PROCESS
privilege, in which
case you will see information about other threads, too. As an
anonymous user, you cannot see any rows at all.
If an SQL statement refers to
INFORMATION_SCHEMA.PROCESSLIST
,
MySQL populates the entire table once, when statement
execution begins, so there is read consistency during the
statement. There is no read consistency for a multi-statement
transaction, though.
Process information is also available from the
performance_schema.threads
table.
However, access to threads
does
not require a mutex and has minimal impact on server
performance.
INFORMATION_SCHEMA.PROCESSLIST
and SHOW PROCESSLIST
have
negative performance consequences because they require a
mutex. threads
also shows
information about background threads, which
INFORMATION_SCHEMA.PROCESSLIST
and SHOW PROCESSLIST
do not.
This means that threads
can be
used to monitor activity the other thread information sources
cannot.
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
PROFILES
and SHOW PROFILE
statements (see Section 13.7.5.31, “SHOW PROFILES Syntax”). The table is
empty unless the profiling
session variable is set to 1.
This table is deprecated as of MySQL 5.7.2 and will be removed in a future MySQL release. Use the Performance Schema instead; see Chapter 22, MySQL Performance Schema.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
QUERY_ID | Query_ID | |
SEQ |
| |
STATE | Status | |
DURATION | Duration | |
CPU_USER | CPU_user | |
CPU_SYSTEM | CPU_system | |
CONTEXT_VOLUNTARY | Context_voluntary | |
CONTEXT_INVOLUNTARY | Context_involuntary | |
BLOCK_OPS_IN | Block_ops_in | |
BLOCK_OPS_OUT | Block_ops_out | |
MESSAGES_SENT | Messages_sent | |
MESSAGES_RECEIVED | Messages_received | |
PAGE_FAULTS_MAJOR | Page_faults_major | |
PAGE_FAULTS_MINOR | Page_faults_minor | |
SWAPS | Swaps | |
SOURCE_FUNCTION | Source_function | |
SOURCE_FILE | Source_file | |
SOURCE_LINE | Source_line |
Notes:
QUERY_ID
is a numeric statement identifier.
SEQ
is a sequence number indicating the
display order for rows with the same
QUERY_ID
value.
STATE
is the profiling state to which the
row measurements apply.
DURATION
indicates how long statement
execution remained in the given state, in seconds.
CPU_USER
and CPU_SYSTEM
indicate user and system CPU use, in seconds.
CONTEXT_VOLUNTARY
and
CONTEXT_INVOLUNTARY
indicate how many
voluntary and involuntary context switches occurred.
BLOCK_OPS_IN
and
BLOCK_OPS_OUT
indicate the number of block
input and output operations.
MESSAGES_SENT
and
MESSAGES_RECEIVED
indicate the number of
communication messages sent and received.
PAGE_FAULTS_MAJOR
and
PAGE_FAULTS_MINOR
indicate the number of
major and minor page faults.
SWAPS
indicates how many swaps occurred.
SOURCE_FUNCTION
,
SOURCE_FILE
, and
SOURCE_LINE
provide information indicating
where in the source code the profiled state executes.
The REFERENTIAL_CONSTRAINTS
table
provides information about foreign keys.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
CONSTRAINT_CATALOG | def | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
UNIQUE_CONSTRAINT_CATALOG | def | |
UNIQUE_CONSTRAINT_SCHEMA | ||
UNIQUE_CONSTRAINT_NAME | ||
MATCH_OPTION | ||
UPDATE_RULE | ||
DELETE_RULE | ||
TABLE_NAME | ||
REFERENCED_TABLE_NAME |
Notes:
TABLE_NAME
has the same value as
TABLE_NAME
in
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
.
CONSTRAINT_SCHEMA
and
CONSTRAINT_NAME
identify the foreign key.
UNIQUE_CONSTRAINT_SCHEMA
,
UNIQUE_CONSTRAINT_NAME
, and
REFERENCED_TABLE_NAME
identify the
referenced key.
The only valid value at this time for
MATCH_OPTION
is NONE
.
The possible values for UPDATE_RULE
or
DELETE_RULE
are CASCADE
,
SET NULL
, SET DEFAULT
,
RESTRICT
, NO ACTION
.
The ROUTINES
table provides
information about stored routines (both procedures and functions).
The ROUTINES
table does not include
user-defined functions (UDFs).
The column named “mysql.proc
name”
indicates the mysql.proc
table column that
corresponds to the
INFORMATION_SCHEMA.ROUTINES
table
column, if any.
INFORMATION_SCHEMA Name | mysql.proc Name | Remarks |
---|---|---|
SPECIFIC_NAME | specific_name | |
ROUTINE_CATALOG | def | |
ROUTINE_SCHEMA | db | |
ROUTINE_NAME | name | |
ROUTINE_TYPE | type | {PROCEDURE|FUNCTION} |
DATA_TYPE | same as for COLUMNS table | |
CHARACTER_MAXIMUM_LENGTH | same as for COLUMNS table | |
CHARACTER_OCTET_LENGTH | same as for COLUMNS table | |
NUMERIC_PRECISION | same as for COLUMNS table | |
NUMERIC_SCALE | same as for COLUMNS table | |
DATETIME_PRECISION | same as for COLUMNS table | |
CHARACTER_SET_NAME | same as for COLUMNS table | |
COLLATION_NAME | same as for COLUMNS table | |
DTD_IDENTIFIER | data type descriptor | |
ROUTINE_BODY | SQL | |
ROUTINE_DEFINITION | body_utf8 | |
EXTERNAL_NAME | NULL | |
EXTERNAL_LANGUAGE | language | NULL |
PARAMETER_STYLE | SQL | |
IS_DETERMINISTIC | is_deterministic | |
SQL_DATA_ACCESS | sql_data_access | |
SQL_PATH | NULL | |
SECURITY_TYPE | security_type | |
CREATED | created | |
LAST_ALTERED | modified | |
SQL_MODE | sql_mode | MySQL extension |
ROUTINE_COMMENT | comment | MySQL extension |
DEFINER | definer | MySQL extension |
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension | |
DATABASE_COLLATION | MySQL extension |
Notes:
MySQL calculates EXTERNAL_LANGUAGE
thus:
If mysql.proc.language='SQL'
,
EXTERNAL_LANGUAGE
is
NULL
Otherwise, EXTERNAL_LANGUAGE
is what is
in mysql.proc.language
. However, we do
not have external languages yet, so it is always
NULL
.
CREATED
: The date and time when the routine
was created. This is a
TIMESTAMP
value.
LAST_ALTERED
: The date and time when the
routine was last modified. This is a
TIMESTAMP
value. If the routine
has not been modified since its creation, this column holds
the same value as the CREATED
column.
SQL_MODE
: The SQL mode in effect when the
routine was created or altered, and under which the routine
executes. For the permitted values, see
Section 5.1.7, “Server SQL Modes”.
CHARACTER_SET_CLIENT
: The session value of
the character_set_client
system variable when the routine was created.
COLLATION_CONNECTION
: The session value of
the collation_connection
system variable when the routine was created.
DATABASE_COLLATION
: The collation of the
database with which the routine is associated.
The DATA_TYPE
,
CHARACTER_MAXIMUM_LENGTH
,
CHARACTER_OCTET_LENGTH
,
NUMERIC_PRECISION
,
NUMERIC_SCALE
,
DATETIME_PRECISION
,
CHARACTER_SET_NAME
, and
COLLATION_NAME
columns provide information
about the data type for the RETURNS
clause
of stored functions. If a stored routine is a stored
procedure, these columns all are NULL
.
Information about stored function RETURNS
data types is also available in the
PARAMETERS
table. The return
value data type row for a function can be identified as the
row that has an ORDINAL_POSITION
value of
0.
A schema is a database, so the
SCHEMATA
table provides information
about databases.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
CATALOG_NAME | def | |
SCHEMA_NAME | Database | |
DEFAULT_CHARACTER_SET_NAME | ||
DEFAULT_COLLATION_NAME | ||
SQL_PATH | NULL |
The following statements are equivalent:
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild
'] SHOW DATABASES [LIKE 'wild
']
The SCHEMA_PRIVILEGES
table provides
information about schema (database) privileges. This information
comes from the mysql.db
grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | def , MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a nonstandard table. It takes its values from the
mysql.db
table.
The STATISTICS
table provides
information about table indexes.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | = Database | |
TABLE_NAME | Table | |
NON_UNIQUE | Non_unique | |
INDEX_SCHEMA | = Database | |
INDEX_NAME | Key_name | |
SEQ_IN_INDEX | Seq_in_index | |
COLUMN_NAME | Column_name | |
COLLATION | Collation | |
CARDINALITY | Cardinality | |
SUB_PART | Sub_part | MySQL extension |
PACKED | Packed | MySQL extension |
NULLABLE | Null | MySQL extension |
INDEX_TYPE | Index_type | MySQL extension |
COMMENT | Comment | MySQL extension |
Notes:
There is no standard table for indexes. The preceding list is
similar to what SQL Server 2000 returns for
sp_statistics
, except that we replaced the
name QUALIFIER
with
CATALOG
and we replaced the name
OWNER
with SCHEMA
.
Clearly, the preceding table and the output from
SHOW INDEX
are derived from the
same parent. So the correlation is already close.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name
' AND table_schema = 'db_name
' SHOW INDEX FROMtbl_name
FROMdb_name
The TABLES
table provides information
about tables in databases.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | Table_ ... | |
TABLE_NAME | Table_ ... | |
TABLE_TYPE | ||
ENGINE | Engine | MySQL extension |
VERSION | Version | The version number of the table's .frm file, MySQL
extension |
ROW_FORMAT | Row_format | MySQL extension |
TABLE_ROWS | Rows | MySQL extension |
AVG_ROW_LENGTH | Avg_row_length | MySQL extension |
DATA_LENGTH | Data_length | MySQL extension |
MAX_DATA_LENGTH | Max_data_length | MySQL extension |
INDEX_LENGTH | Index_length | MySQL extension |
DATA_FREE | Data_free | MySQL extension |
AUTO_INCREMENT | Auto_increment | MySQL extension |
CREATE_TIME | Create_time | MySQL extension |
UPDATE_TIME | Update_time | MySQL extension |
CHECK_TIME | Check_time | MySQL extension |
TABLE_COLLATION | Collation | MySQL extension |
CHECKSUM | Checksum | MySQL extension |
CREATE_OPTIONS | Create_options | MySQL extension |
TABLE_COMMENT | Comment | MySQL extension |
Notes:
Refer to
SHOW TABLE
STATUS
for field descriptions.
TABLE_SCHEMA
and
TABLE_NAME
are a single field in a
SHOW
display, for example
Table_in_db1
.
TABLE_TYPE
should be BASE
TABLE
or VIEW
. The
TABLES
table does not list
TEMPORARY
tables.
For partitioned tables, the ENGINE
column
shows the name of the storage engine used by all partitions.
(Previously, this column showed PARTITION
for such tables.)
The TABLE_ROWS
column is
NULL
if the table is in the
INFORMATION_SCHEMA
database.
For InnoDB
tables, the row count
is only a rough estimate used in SQL optimization. (This is
also true if the InnoDB
table is
partitioned.)
The DATA_FREE
column shows the free space
in bytes for InnoDB
tables.
Prior to MySQL 5.7.8, for partitioned
InnoDB
tables, the
CREATE_TIME
column always showed
NULL
. This column shows the correct table
creation time for such tables in MySQL 5.7.8 and later. (Bug
#17299181, Bug #69990)
Beginning with MySQL 5.7.2, UPDATE_TIME
displays a timestamp value for the last
UPDATE
,
INSERT
, or
DELETE
performed on
InnoDB
tables that are not partitioned.
Previously, UPDATE_TIME
displayed a NULL
value for InnoDB
tables. 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.
The UPDATE_TIME
column also shows this
information for partitioned InnoDB
tables
in MySQL 5.7.8 and later. Previously this column was always
NULL
for such tables. (Bug #17299181, Bug
#69990)
For partitioned InnoDB
tables,
the CHECK_TIME
column is always
NULL
.
We have nothing for the table's default character set.
TABLE_COLLATION
is close, because collation
names begin with a character set name.
The CREATE_OPTIONS
column shows
partitioned
if the table is partitioned.
The following statements are equivalent:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name
' [AND table_name LIKE 'wild
'] SHOW TABLES FROMdb_name
[LIKE 'wild
']
The TABLESPACES
table provides
information about active tablespaces.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TABLESPACE_NAME | MySQL extension | |
ENGINE | MySQL extension | |
TABLESPACE_TYPE | MySQL extension | |
LOGFILE_GROUP_NAME | MySQL extension | |
EXTENT_SIZE | MySQL extension | |
AUTOEXTEND_SIZE | MySQL extension | |
MAXIMUM_SIZE | MySQL extension | |
NODEGROUP_ID | MySQL extension | |
TABLESPACE_COMMENT | MySQL extension |
Notes:
The INFORMATION_SCHEMA.TABLESPACES
table does
not provide information about InnoDB
tablespaces. For InnoDB
tablespace metadata,
see INNODB_SYS_TABLESPACES
and
INNODB_SYS_DATAFILES
. As of MySQL
5.7.8, the INFORMATION_SCHEMA.FILES
table also provides metadata for InnoDB
tablespaces.
The TABLE_CONSTRAINTS
table describes
which tables have constraints.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
CONSTRAINT_CATALOG | def | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_SCHEMA | ||
TABLE_NAME | ||
CONSTRAINT_TYPE |
Notes:
The CONSTRAINT_TYPE
value can be
UNIQUE
, PRIMARY KEY
, or
FOREIGN KEY
.
The UNIQUE
and PRIMARY
KEY
information is about the same as what you get
from the Key_name
field in the output from
SHOW INDEX
when the
Non_unique
field is 0
.
The CONSTRAINT_TYPE
column can contain one
of these values: UNIQUE
, PRIMARY
KEY
, FOREIGN KEY
,
CHECK
. This is a
CHAR
(not
ENUM
) column. The
CHECK
value is not available until we
support CHECK
.
The TABLE_PRIVILEGES
table provides
information about table privileges. This information comes from
the mysql.tables_priv
grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
GRANTEE | '
value | |
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
PRIVILEGE_TYPE
can contain one (and only
one) of these values: SELECT
,
INSERT
,
UPDATE
,
REFERENCES
,
ALTER
,
INDEX
,
DROP
,
CREATE VIEW
.
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.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TRIGGER_CATALOG | def | |
TRIGGER_SCHEMA | ||
TRIGGER_NAME | Trigger | |
EVENT_MANIPULATION | Event | |
EVENT_OBJECT_CATALOG | def | |
EVENT_OBJECT_SCHEMA | ||
EVENT_OBJECT_TABLE | Table | |
ACTION_ORDER | ||
ACTION_CONDITION | NULL | |
ACTION_STATEMENT | Statement | |
ACTION_ORIENTATION | ROW | |
ACTION_TIMING | Timing | |
ACTION_REFERENCE_OLD_TABLE | NULL | |
ACTION_REFERENCE_NEW_TABLE | NULL | |
ACTION_REFERENCE_OLD_ROW | OLD | |
ACTION_REFERENCE_NEW_ROW | NEW | |
CREATED | Created | |
SQL_MODE | sql_mode | MySQL extension |
DEFINER | Definer | MySQL extension |
CHARACTER_SET_CLIENT | character_set_client | MySQL extension |
COLLATION_CONNECTION | collation_connection | MySQL extension |
DATABASE_COLLATION | Database Collation | MySQL extension |
Notes:
The names in the “SHOW
Name” column refer to the SHOW
TRIGGERS
statement, not SHOW
CREATE TRIGGER
. See Section 13.7.5.38, “SHOW TRIGGERS Syntax”.
TRIGGER_SCHEMA
and
TRIGGER_NAME
: The name of the database in
which the trigger occurs and the trigger name, respectively.
EVENT_MANIPULATION
: The trigger event. This
is the type of operation on the associated table for which the
trigger activates. The value is 'INSERT'
(a
row was inserted), 'DELETE'
(a row was
deleted), or 'UPDATE'
(a row was modified).
EVENT_OBJECT_SCHEMA
and
EVENT_OBJECT_TABLE
: As noted in
Section 20.3, “Using Triggers”, every trigger is associated with
exactly one table. These columns indicate the database in
which this table occurs, and the table name, respectively.
ACTION_ORDER
: The ordinal position of the
trigger's action within the list of triggers on the same table
with the same EVENT_MANIPULATION
and
ACTION_TIMING
values. Before MySQL 5.7.2,
this value is always 0
because it is not
possible for a table to have more than one trigger with the
same EVENT_MANIPULATION
and
ACTION_TIMING
values.
ACTION_STATEMENT
: The trigger body; that
is, the statement executed when the trigger activates. This
text uses UTF-8 encoding.
ACTION_ORIENTATION
: Always contains the
value 'ROW'
.
ACTION_TIMING
: Whether the trigger
activates before or after the triggering event. The value is
'BEFORE'
or 'AFTER'
.
ACTION_REFERENCE_OLD_ROW
and
ACTION_REFERENCE_NEW_ROW
: The old and new
column identifiers, respectively. This means that
ACTION_REFERENCE_OLD_ROW
always contains
the value 'OLD'
and
ACTION_REFERENCE_NEW_ROW
always contains
the value 'NEW'
.
CREATED
: The date and time when the trigger
was created. This is a TIMESTAMP(2)
value
(with a fractional part in hundredths of seconds) for triggers
created in MySQL 5.7.2 or later, NULL
for
triggers created prior to 5.7.2.
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.7, “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 columns currently always contain
NULL
: ACTION_CONDITION
,
ACTION_REFERENCE_OLD_TABLE
, and
ACTION_REFERENCE_NEW_TABLE
.
Example, using the ins_sum
trigger defined in
Section 20.3, “Using Triggers”:
mysql>SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
->WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ins_sum'\G
*************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: ins_sum EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: account ACTION_ORDER: 1 ACTION_CONDITION: NULL ACTION_STATEMENT: SET @sum = @sum + NEW.amount ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2013-07-05 07:41:21.26 SQL_MODE: NO_ENGINE_SUBSTITUTION DEFINER: me@localhost CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci
The USER_PRIVILEGES
table provides
information about global privileges. This information comes from
the mysql.user
grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | def , MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a nonstandard table. It takes its values from the
mysql.user
table.
The VIEWS
table provides information
about views in databases. You must have the
SHOW VIEW
privilege to access this
table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
VIEW_DEFINITION | ||
CHECK_OPTION | ||
IS_UPDATABLE | ||
DEFINER | ||
SECURITY_TYPE | ||
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension |
Notes:
The VIEW_DEFINITION
column has most of what
you see in the Create Table
field that
SHOW CREATE VIEW
produces. Skip
the words before SELECT
and
skip the words WITH CHECK OPTION
. Suppose
that the original statement was:
CREATE VIEW v AS SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1 WITH CHECK OPTION;
Then the view definition looks like this:
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
The CHECK_OPTION
column has a value of
NONE
, CASCADE
, or
LOCAL
.
MySQL sets a flag, called the view updatability flag, at
CREATE VIEW
time. The flag is
set to YES
(true) if
UPDATE
and
DELETE
(and similar operations)
are legal for the view. Otherwise, the flag is set to
NO
(false). The
IS_UPDATABLE
column in the
VIEWS
table displays the status
of this flag.
If a view is not updatable, statements such
UPDATE
,
DELETE
, and
INSERT
are illegal and will be
rejected. (Note that even if a view is updatable, it might not
be possible to insert into it; for details, refer to
Section 20.5.3, “Updatable and Insertable Views”.)
The IS_UPDATABLE
flag may be unreliable if
a view depends on one or more other views, and one of these
underlying views is updated. Regardless of the
IS_UPDATABLE
value, the server keeps track
of the updatability of a view and correctly rejects data
change operations to views that are not updatable. If the
IS_UPDATABLE
value for a view has become
inaccurate to due to changes to underlying views, the value
can be updated by deleting and recreating the view.
DEFINER
: The account of the user who
created the view, in
'
format. user_name
'@'host_name
'SECURITY_TYPE
has a value of
DEFINER
or INVOKER
.
CHARACTER_SET_CLIENT
: The session value of
the character_set_client
system variable when the view was created.
COLLATION_CONNECTION
: The session value of
the collation_connection
system variable when the view was created.
MySQL lets you use different
sql_mode
settings to tell the
server the type of SQL syntax to support. For example, you might
use the ANSI
SQL mode to ensure
MySQL correctly interprets the standard SQL concatenation
operator, the double bar (||
), in your queries.
If you then create a view that concatenates items, you might worry
that changing the sql_mode
setting to a value different from
ANSI
could cause the view to
become invalid. But this is not the case. No matter how you write
out a view definition, MySQL always stores it the same way, in a
canonical form. Here is an example that shows how the server
changes a double bar concatenation operator to a
CONCAT()
function:
mysql>SET sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec) mysql>CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;
Query OK, 0 rows affected (0.00 sec) mysql>SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
->WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
+----------------------------------+ | VIEW_DEFINITION | +----------------------------------+ | select concat('a','b') AS `col1` | +----------------------------------+ 1 row in set (0.00 sec)
The advantage of storing a view definition in canonical form is
that changes made later to the value of
sql_mode
will 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.
This section provides table definitions for
InnoDB
INFORMATION_SCHEMA
tables. For related information and examples, see
Section 14.13, “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_CMP
and
INNODB_CMP_RESET
tables contain status
information on operations related to
compressed
InnoDB
tables.
Table 21.1 Columns of INNODB_CMP and INNODB_CMP_RESET
Column name | Description |
---|---|
PAGE_SIZE | Compressed page size in bytes. |
COMPRESS_OPS | Number of times a B-tree page of the 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 | Number of times a B-tree page of the size PAGE_SIZE
has been successfully compressed. This count should never
exceed COMPRESS_OPS . |
COMPRESS_TIME | Total time in seconds spent in attempts to compress B-tree pages of the
size PAGE_SIZE . |
UNCOMPRESS_OPS | Number of times a B-tree page of the 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 | Total time in seconds spent in uncompressing B-tree pages of the size
PAGE_SIZE . |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP \G *************************** 1. row *************************** page_size: 1024 compress_ops: 0 compress_ops_ok: 0 compress_time: 0 uncompress_ops: 0 uncompress_time: 0 *************************** 2. row *************************** page_size: 2048 compress_ops: 0 compress_ops_ok: 0 compress_time: 0 uncompress_ops: 0 uncompress_time: 0 *************************** 3. row *************************** page_size: 4096 compress_ops: 0 compress_ops_ok: 0 compress_time: 0 uncompress_ops: 0 uncompress_time: 0 *************************** 4. row *************************** page_size: 8192 compress_ops: 86955 compress_ops_ok: 81182 compress_time: 27 uncompress_ops: 26828 uncompress_time: 5 *************************** 5. row *************************** page_size: 16384 compress_ops: 0 compress_ops_ok: 0 compress_time: 0 uncompress_ops: 0 uncompress_time: 0
Notes:
Use these tables to measure the effectiveness of
InnoDB
table
compression in your
database.
Use DESCRIBE
or
SHOW COLUMNS
to view additional
information about the columns of these tables including data
types and default values.
You must have the PROCESS
privilege to
query this table.
For usage information, see
Section 14.7.1.4, “Monitoring Compression at Runtime” and
Section 14.13.1.3, “Using the Compression Information Schema Tables”.
For general information about InnoDB
table
compression, see Section 14.7, “InnoDB Table and Page Compression”.
The INNODB_CMP_PER_INDEX
and
INNODB_CMP_PER_INDEX_RESET
tables contain
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.
Table 21.2 Columns of INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET
Column name | Description |
---|---|
DATABASE_NAME | Database containing the applicable table. |
TABLE_NAME | Table to monitor for compression statistics. |
INDEX_NAME | Index to monitor for compression statistics. |
COMPRESS_OPS | 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 | 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 | Total amount of CPU time, in seconds, used for compressing data in this index. |
UNCOMPRESS_OPS | 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 | Total amount of CPU time, in seconds, used for uncompressing data in this index. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX \G *************************** 1. row *************************** database_name: employees table_name: salaries index_name: PRIMARY compress_ops: 0 compress_ops_ok: 0 compress_time: 0 uncompress_ops: 23451 uncompress_time: 4 *************************** 2. row *************************** database_name: employees table_name: salaries index_name: emp_no compress_ops: 0 compress_ops_ok: 0 compress_time: 0 uncompress_ops: 1597 uncompress_time: 0
Notes:
Use these tables to measure the effectiveness of
InnoDB
table
compression for
specific tables, indexes, or both.
Use DESCRIBE
or
SHOW COLUMNS
to view additional
information about the columns of these tables including data
types and default values.
You must have the PROCESS
privilege to
query these tables.
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
configuration option before performing the operations on
compressed tables that you want to monitor.
For usage information, see
Section 14.7.1.4, “Monitoring Compression at Runtime” and
Section 14.13.1.3, “Using the Compression Information Schema Tables”.
For general information about InnoDB
table
compression, see Section 14.7, “InnoDB Table and Page Compression”.
The INNODB_CMPMEM
and
INNODB_CMPMEM_RESET
tables contain status
information on compressed pages
within the InnoDB
buffer pool.
Table 21.3 Columns of INNODB_CMPMEM and INNODB_CMPMEM_RESET
Column name | Description |
---|---|
PAGE_SIZE | 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 | Number of blocks of the size PAGE_SIZE that are
currently in use. |
PAGES_FREE | Number of blocks of the 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 | Number of times a block of the 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 table INNODB_CMPMEM_RESET resets
this count. |
RELOCATION_TIME | Total time in microseconds spent in relocating blocks of the size
PAGE_SIZE . Reading from the table
INNODB_CMPMEM_RESET resets this count. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM \G *************************** 1. row *************************** page_size: 1024 buffer_pool_instance: 0 pages_used: 0 pages_free: 0 relocation_ops: 0 relocation_time: 0 *************************** 2. row *************************** page_size: 2048 buffer_pool_instance: 0 pages_used: 0 pages_free: 0 relocation_ops: 0 relocation_time: 0 *************************** 3. row *************************** page_size: 4096 buffer_pool_instance: 0 pages_used: 0 pages_free: 0 relocation_ops: 0 relocation_time: 0 *************************** 4. row *************************** page_size: 8192 buffer_pool_instance: 0 pages_used: 7673 pages_free: 15 relocation_ops: 4638 relocation_time: 0 *************************** 5. row *************************** page_size: 16384 buffer_pool_instance: 0 pages_used: 0 pages_free: 0 relocation_ops: 0 relocation_time: 0
Notes:
Use these tables to measure the effectiveness of
InnoDB
table
compression in your
database.
Use DESCRIBE
or
SHOW COLUMNS
to view additional
information about the columns of these tables including data
types and default values.
You must have the PROCESS
privilege to
query this table.
For usage information, see
Section 14.7.1.4, “Monitoring Compression at Runtime” and
Section 14.13.1.3, “Using the Compression Information Schema Tables”.
For general information about InnoDB
table
compression, see Section 14.7, “InnoDB Table and Page Compression”.
The INNODB_TRX
table contains 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.
Table 21.4 INNODB_TRX Columns
Column name | Description |
---|---|
TRX_ID | Unique transaction ID number, internal to InnoDB .
(Starting in MySQL 5.6, these IDs are not created for
transactions that are read-only and non-locking. See
Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for details.) |
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 rollback.
Transactions that have changed non-transactional tables
are considered heavier than others, regardless of the
number of altered and locked rows. |
TRX_STATE | Transaction execution state. One of RUNNING ,
LOCK WAIT , ROLLING
BACK or COMMITTING . |
TRX_STARTED | Transaction start time. |
TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if
TRX_STATE is LOCK
WAIT , otherwise NULL ).
Details about the lock can be found by joining with
INNODB_LOCKS on
LOCK_ID . |
TRX_WAIT_STARTED | Time when the transaction started waiting on the lock (if
TRX_STATE is LOCK
WAIT , otherwise NULL ). |
TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with
PROCESSLIST on ID .
See
Section 14.13.2.3.1, “Potential Inconsistency with PROCESSLIST Data”. |
TRX_QUERY | The SQL query that is being executed by the transaction. |
TRX_OPERATION_STATE | The transaction's current operation, or NULL . |
TRX_TABLES_IN_USE | The number of InnoDB tables used while processing the
current SQL statement of this transaction. |
TRX_TABLES_LOCKED | 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 | Total size taken up by the lock structures of this transaction in memory. |
TRX_ROWS_LOCKED | 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 option. |
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. (They might be turned off during a bulk data load, for example.) |
TRX_FOREIGN_KEY_CHECKS | Whether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_LAST_FOREIGN_KEY_ERROR | Detailed error message for last FK error, or NULL . |
TRX_ADAPTIVE_HASH_LATCHED | Whether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.) |
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 AHI 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. |
TRX_IS_READ_ONLY | A value of 1 indicates the transaction is read-only. (5.6.4 and up.) |
TRX_AUTOCOMMIT_NON_LOCKING |
A value of 1 indicates the transaction is a
|
Example:
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 1 row in set (0.00 sec)
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 14.13.2.3, “Data Persistence and Consistency for InnoDB Transaction and Locking Tables”.
Use DESCRIBE
or
SHOW COLUMNS
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 usage information, see Section 14.13.2.1, “Usage Examples for InnoDB Transaction and Locking Tables”.
The INNODB_LOCKS
table contains 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.
Table 21.5 INNODB_LOCKS Columns
Column name | Description |
---|---|
LOCK_ID | Unique lock ID number, internal to InnoDB . Treat it
as an opaque string. Although LOCK_ID
currently contains TRX_ID , the format
of the data in LOCK_ID is not
guaranteed to remain the same in future releases. Do not
write programs that parse the LOCK_ID
value. |
LOCK_TRX_ID | ID of the transaction holding this lock. Details about the transaction
can be found by joining with INNODB_TRX
on TRX_ID . |
LOCK_MODE | Mode of the lock. One of S[,GAP] ,
X[,GAP] , IS[,GAP] ,
IX[,GAP] , AUTO_INC ,
or UNKNOWN . Lock modes other than
AUTO_INC and UNKNOWN
will indicate GAP locks, if present. Refer to
Section 14.3.1, “InnoDB Lock Modes” for information about
S , X ,
IS , and IX lock
modes. Refer to
Section 14.3.2, “InnoDB Record, Gap, and Next-Key Locks” for
information about GAP locks. |
LOCK_TYPE | Type of the lock. One of RECORD or
TABLE for record (row) level or table
level locks, respectively. |
LOCK_TABLE | Name of the table that has been locked or contains locked records. |
LOCK_INDEX | Name of the index if LOCK_TYPE='RECORD' , otherwise
NULL . |
LOCK_SPACE | Tablespace ID of the locked record if
LOCK_TYPE='RECORD' , otherwise
NULL . |
LOCK_PAGE | Page number of the locked record if
LOCK_TYPE='RECORD' , otherwise
NULL . |
LOCK_REC | Heap number of the locked record within the page if
LOCK_TYPE='RECORD' , otherwise
NULL . |
LOCK_DATA | Primary key value(s) of the locked record if
LOCK_TYPE='RECORD' , otherwise
NULL . This column contains the value(s)
of the primary key column(s) in the locked row, formatted
as a valid SQL string (ready to be copied to SQL
commands). If there is no primary key then the
InnoDB internal unique row ID number is
used. If a gap lock is taken for key values or ranges
above the largest value in the index,
LOCK_DATA reports “supremum
pseudo-record”. When the page containing the locked
record is not in the buffer pool (in the case that it was
paged out to disk while the lock was held),
InnoDB does not fetch the page from
disk, to avoid unnecessary disk operations. Instead,
LOCK_DATA is set to
NULL . |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS \G *************************** 1. row *************************** lock_id: 3723:72:3:2 lock_trx_id: 3723 lock_mode: X lock_type: RECORD lock_table: `mysql`.`t` lock_index: PRIMARY lock_space: 72 lock_page: 3 lock_rec: 2 lock_data: 1, 9 *************************** 2. row *************************** lock_id: 3722:72:3:2 lock_trx_id: 3722 lock_mode: S lock_type: RECORD lock_table: `mysql`.`t` lock_index: PRIMARY lock_space: 72 lock_page: 3 lock_rec: 2 lock_data: 1, 9
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 14.13.2.3, “Data Persistence and Consistency for InnoDB Transaction and Locking Tables”.
Use DESCRIBE
or
SHOW COLUMNS
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 usage information, see Section 14.13.2.1, “Usage Examples for InnoDB Transaction and Locking Tables”.
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.
Table 21.6 INNODB_LOCK_WAITS Columns
Column name | Description |
---|---|
REQUESTING_TRX_ID | ID of the requesting transaction. |
REQUESTED_LOCK_ID | ID of the lock for which a transaction is waiting. Details about the
lock can be found by joining with
INNODB_LOCKS on
LOCK_ID . |
BLOCKING_TRX_ID | ID of the blocking transaction. |
BLOCKING_LOCK_ID | ID of a lock held by a transaction blocking another transaction from
proceeding. Details about the lock can be found by joining
with INNODB_LOCKS on
LOCK_ID . |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS \G *************************** 1. row *************************** requesting_trx_id: 3396 requested_lock_id: 3396:91:3:2 blocking_trx_id: 3395 blocking_lock_id: 3395:91:3:2 1 row in set (0.00 sec)
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 14.13.2.3, “Data Persistence and Consistency for InnoDB Transaction and Locking Tables”.
Use DESCRIBE
or
SHOW COLUMNS
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 usage information, see Section 14.13.2.1, “Usage Examples for InnoDB Transaction and Locking Tables”.
The INNODB_SYS_TABLES
table provides metadata
about InnoDB
tables, equivalent to the
information from the SYS_TABLES
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
Table 21.7 INNODB_SYS_TABLES Columns
Column name | Description |
---|---|
TABLE_ID | An identifier for each InnoDB table that is unique
across all databases in the instance. |
NAME | The name of the table. Preceded by the database name where appropriate,
for example test/t1 .
InnoDB system table names are in all
uppercase. 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 | This value provides bit level information about table format and storage
characteristics including row format, compressed page size
(if applicable), and whether or not the DATA
DIRECTORY clause was used with
CREATE TABLE or
ALTER TABLE . |
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. |
FILE_FORMAT | The table's file format (Antelope or Barracuda). |
ROW_FORMAT | The table's row format (Compact, Redundant, Dynamic, or Compressed). |
ZIP_PAGE_SIZE | The zip page size. Only applies to tables that use the Compressed row format. |
SPACE_TYPE | The type of tablespace to which the table belongs. Possible values
include System (for the
InnoDB system tablespace),
General (for InnoDB
general tablespaces created using
CREATE TABLESPACE , and
Single (for InnoDB
file-per-table tablespaces). Tables assigned to the system
tablespace using the CREATE
TABLE or ALTER
TABLE
TABLESPACE=innodb_system clause have a
General SPACE_TYPE .
The SPACE_TYPE column was added in
MySQL 5.7.6 with the introduction of
InnoDB general tablespaces. For more
information, see CREATE
TABLESPACE . |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE TABLE_ID = 214 \G *************************** 1. row *************************** TABLE_ID: 214 NAME: test/t1 FLAG: 129 N_COLS: 4 SPACE: 233 FILE_FORMAT: Antelope ROW_FORMAT: Compact ZIP_PAGE_SIZE: 0 SPACE_TYPE: General 1 row in set (0.00 sec)
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
Interpreting the INNODB_SYS_TABLES.FLAG Column Value:
The INNODB_SYS_TABLES.FLAG
column provides
bit-level information about the table's format and storage
characteristics. You can interpret the FLAG
column value by adding together the applicable decimal numeric
values that are provided in the following table.
Table 21.8 Bit Position Values for Interpreting INNODB_SYS_TABLES FLAG Column Data
Bit Position | Description | Decimal Numeric Value |
---|---|---|
0 | This bit is set if the row format is not REDUNDANT .
In other words, it is set if the row format is
COMPACT , DYNAMIC or
COMPRESSED . |
|
1-4 | These four bits contain a small number that represents the compressed
page size of the table. The
INNODB_SYS_TABLES.ZIP_PAGE_SIZE field
also reports the compressed page size, if applicable. |
|
5 | This bit is set if the row format is DYNAMIC or
COMPRESSED . |
|
6 | This bit is set if the DATA DIRECTORY option is used
with CREATE TABLE or
ALTER TABLE . This bit is
set for file-per-table tablespaces that are located in
directories other than the default data directory
(datadir ). For these
tables, a
file is present in the same location as the
file. The
file stores the actual directory path to the
file-per-table tablespace file. |
|
7 | This bit is set if the table is assigned to a shared tablespace (either
a general tablespace or a system tablespace) using the
CREATE TABLE or
ALTER TABLE
TABLESPACE=
option. |
|
In the following, table t1
uses
ROW_FORMAT=DYNAMIC
and has a
FLAG
value of 33. Based on the information in
the preceding table, we can see that bit position 0 would be set
to 1, and bit position 5 would be set to 32 for a table with a
DYNAMIC
row format. These values add up to a
FLAG
value of 33.
mysql> use test; Database changed mysql> SET GLOBAL innodb_file_format=Barracuda; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 (c1 int) ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G *************************** 1. row *************************** TABLE_ID: 89 NAME: test/t1 FLAG: 33 N_COLS: 4 SPACE: 75 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic ZIP_PAGE_SIZE: 0 1 row in set (0.01 sec)
The INNODB_SYS_INDEXES
table provides metadata
about InnoDB
indexes, equivalent to the
information in the internal SYS_INDEXES
table
in the InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
Table 21.9 INNODB_SYS_INDEXES Columns
Column name | Description |
---|---|
INDEX_ID | An identifier for each index that is 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. For example,
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 from INNODB_SYS_TABLES.TABLE_ID . |
TYPE | A numeric identifier signifying the kind of index. 0 = Secondary Index, 1 = Clustered Index, 2 = Unique Index, 3 = Primary Index, 32 = Full-text Index, 64 = Spatial Index, 128 = A secondary index that includes a virtual generated column. |
N_FIELDS | The number of columns in the index key. For the
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 field 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 in
file-per-table
mode with a separate .ibd file. 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%. The
MERGE_THRESHOLD column was added to
INNODB_SYS_INDEXES in MySQL 5.7.6. For
more information, see
Section 14.4.12, “Configuring the Merge Threshold for Index Pages”. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_SYS_COLUMNS
table provides metadata
about InnoDB
table columns, equivalent to the
information from the SYS_COLUMNS
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
Table 21.10 INNODB_SYS_COLUMNS Columns
Column name | Description |
---|---|
TABLE_ID | An identifier representing the table associated with the column; the
same value from
INNODB_SYS_TABLES.TABLE_ID . |
NAME | The name of each column in each table. 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 21.31.16, “The INFORMATION_SCHEMA INNODB_SYS_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
2* ,
3* , and so
on depending on the character encoding. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where TABLE_ID = 71 \G *************************** 1. row *************************** TABLE_ID: 71 NAME: col1 POS: 0 MTYPE: 6 PRTYPE: 1027 LEN: 4 *************************** 2. row *************************** TABLE_ID: 71 NAME: col2 POS: 1 MTYPE: 2 PRTYPE: 524542 LEN: 10 *************************** 3. row *************************** TABLE_ID: 71 NAME: col3 POS: 2 MTYPE: 1 PRTYPE: 524303 LEN: 10
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_SYS_FIELDS
table provides metadata
about the key columns (fields) of InnoDB
indexes, equivalent to the information from the
SYS_FIELDS
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
Table 21.11 INNODB_SYS_FIELDS Columns
Column name | Description |
---|---|
INDEX_ID | An identifier for the index associated with this key field, using the
same value as in
INNODB_SYS_INDEXES.INDEX_ID . |
NAME | The name of the original column from the table, using the same value as
in INNODB_SYS_COLUMNS.NAME . |
POS | The ordinal position of the key field within the index, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS where INDEX_ID = 117 \G *************************** 1. row *************************** INDEX_ID: 117 NAME: col1 POS: 0 1 row in set (0.00 sec)
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_SYS_FOREIGN
table provides metadata
about InnoDB
foreign keys, equivalent
to the information from the SYS_FOREIGN
table
in the InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
Table 21.12 INNODB_SYS_FOREIGN Columns
Column name | Description |
---|---|
ID | The name (not a numeric value) of the foreign key index. Preceded by the
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. 1 = ON DELETE CASCADE , 2
= ON UPDATE SET NULL, 4 = ON
UPDATE CASCADE , 8 = ON UPDATE
SET NULL, 16 = ON DELETE NO
ACTION, 32 = ON UPDATE NO ACTION. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G *************************** 1. row *************************** ID: test/fk1 FOR_NAME: test/child REF_NAME: test/parent N_COLS: 1 TYPE: 1 1 row in set (0.00 sec)
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_SYS_FOREIGN_COLS
table provides
status information about the columns of InnoDB
foreign keys, equivalent to the information from the
SYS_FOREIGN_COLS
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
Table 21.13 INNODB_SYS_FOREIGN_COLS Columns
Column name | Description |
---|---|
ID | The foreign key index associated with this index key field, using the
same value as INNODB_SYS_FOREIGN.ID . |
FOR_COL_NAME | The name of the associated column in the child table. |
REF_COL_NAME | The name of the associated column in the parent table. |
POS | The ordinal position of this key field within the foreign key index, starting from 0. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS WHERE ID = 'test/fk1' \G *************************** 1. row *************************** ID: test/fk1 FOR_COL_NAME: parent_id REF_COL_NAME: id POS: 0 1 row in set (0.00 sec)
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_SYS_TABLESTATS
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 corresponding to 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 not aged
out of the table cache. Tables for which persistent stats are
available are always represented in this view.
Table statistics are only updated for
DELETE
or
UPDATE
operations that modify
indexed columns. Statistics are not updated by operations that
only modify non-indexed columns.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
Table 21.14 INNODB_SYS_TABLESTATS Columns
Column name | Description |
---|---|
TABLE_ID | An identifier representing the table for which statistics are available,
using the same value as
INNODB_SYS_TABLES.TABLE_ID . |
NAME | The name of the table, using the same value as
INNODB_SYS_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. Could be imprecise if uncommitted transactions are inserting into or deleting from the table. |
CLUST_INDEX_SIZE | 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 | 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. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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 1 row in set (0.00 sec)
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_SYS_DATAFILES
table provides data
file path information for InnoDB
file-per-table
and general tablespaces, equivalent to the information in the
SYS_DATAFILES
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
As of MySQL 5.7.8, the
INFORMATION_SCHEMA.FILES
table
provides data file path information and other metadata about all
InnoDB
tablespace types including
file-per-table tablespaces, general tablespaces, the system
tablespace, temporary table tablespaces, and undo tablespaces
(if present).
Table 21.15 INNODB_SYS_DATAFILES Columns
Column name | Description |
---|---|
SPACE | The tablespace Space ID. |
PATH | The tablespace data file path (for example,
“.\world\innodb\city.ibd ”).
If a
file-per-table
tablespace is created in a location outside the MySQL data
directory using the DATA DIRECTORY
clause of the CREATE TABLE
statement, the tablespace PATH field
shows the fully qualified directory path. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE SPACE = 57 \G *************************** 1. row *************************** SPACE: 57 PATH: ./test/t1.ibd 1 row in set (0.01 sec)
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_SYS_TABLESPACES
table provides
metadata about InnoDB
file-per-table and
general tablespaces, equivalent to the information in the
SYS_TABLESPACES
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.13.3, “InnoDB INFORMATION_SCHEMA System Tables”.
As of MySQL 5.7.8, the
INFORMATION_SCHEMA.FILES
table
provides metadata about all InnoDB
tablespace
types including file-per-table tablespaces, general tablespaces,
the system tablespace, temporary table tablespaces, and undo
tablespaces (if present).
Table 21.16 INNODB_SYS_TABLESPACES Columns
Column name | Description |
---|---|
SPACE | Tablespace Space ID. |
NAME | The database and table name (for example,
world_innodb\city ) |
FLAG | This value provides bit level information about tablespace format and storage characteristics. |
FILE_FORMAT | The tablespace file format. For example,
Antelope,
Barracuda, or
Any
(general
tablespaces support any row format). The data in
this field is interpreted from the tablespace flags
information that resides in the
.ibd file. For more
information about InnoDB file formats,
see Section 14.8, “InnoDB File-Format Management”. |
ROW_FORMAT | The tablespace row format (Compact or Redundant, Dynamic, or Compressed). The data in this field is interpreted from the tablespace flags information that resides in the .ibd file. |
PAGE_SIZE | The tablespace page size. The data in this field 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 field is interpreted from the tablespace flags information that resides in the .ibd file. |
SPACE_TYPE | The type of tablespace. Possible values include
General (for InnoDB
general tablespaces created using
CREATE TABLESPACE and
Single (for InnoDB
file-per-table tablespaces). The
SPACE_TYPE column was added in MySQL
5.7.6 with the introduction of InnoDB
general tablespaces. For more information, see
CREATE TABLESPACE . |
FS_BLOCK_SIZE | The file system block size, which is the unit size used for hole
punching. This column was added in MySQL 5.7.8 with the
introduction of 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 was added in MySQL 5.7.8
with the introduction of 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 was added in MySQL 5.7.8 with the
introduction of the InnoDB
transparent page
compression feature. |
COMPRESSION | The current tablespace setting for page compression
(Zlib , Lz4 , or
None ). A table may contain a mix of
pages with different compression settings. This column was
added in MySQL 5.7.8 with the introduction of the
InnoDB
transparent page
compression feature. This column displays incorrect
data after a server restart (Bug #78197) and is removed in
5.7.10. Use SHOW CREATE
TABLE to view the current page compression
setting. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 26 \G *************************** 1. row *************************** SPACE: 26 NAME: test/t1 FLAG: 0 FILE_FORMAT: Antelope 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 COMPRESSION: LZ4 1 row in set (0.00 sec)
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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.
Because tablespace flags are always zero for all Antelope file
formats (unlike table flags), there is no way to determine
from this flag integer if the tablespace row format is
Redundant or Compact. As a result, the possible values for the
ROW_FORMAT
field are “Compact or
Redundant”, “Compressed”, or
“Dynamic.”
With the introduction of general tablespaces in MySQl 5.7.6,
InnoDB
system tablespace data (for SPACE 0)
is exposed in INNODB_SYS_TABLESPACES
.
Interpreting the INNODB_SYS_TABLESPACES.FLAG Column Value:
The INNODB_SYS_TABLESPACES.FLAG
column provides
bit-level information about tablespace format and storage
characteristics.
Until MySQL 5.6, table and tablespace flags were the same except
for the bit position 0 settings. In MySQL 5.6, support was added
for 4K and 8K pages, which required an additional 4 bits to hold
the logical page size. Also in MySQL 5.6, support was added for
the CREATE TABLE
and
ALTER TABLE
DATA
DIRECTORY
clause, which allows file-per-table
tablespaces to be stored in a location outside of the MySQL data
directory. This feature required an additional bit for both table
and tablespace flags, but not at the same position.
You can interpret the tablespace FLAG
column
value by adding together the applicable decimal numeric values
that are provided in the following table.
Table 21.17 Bit Position Values for Interpreting INNODB_SYS_TABLESPACES FLAG Column Data
Bit Position | Description | Decimal Numeric Value |
---|---|---|
0 | This bit is set if the row format of tables in the tablespace is
DYNAMIC or
COMPRESSED . This information can help
you distinguish between Antelope and Barracuda file
formats but not between REDUNDANT and
COMPACT file formats
(DYNAMIC and
COMPRESSED row formats require the
Barracuda file format). If it is a file-per-table
tablespace, you must query
INNODB_SYS_TABLES to determine which of
the two Antelope row formats is used
(REDUNDANT or
COMPACT ). |
|
1-4 | These four bits contain a small number that represents the compressed
page size (the KEY_BLOCK_SIZE or
“physical block size”) of the tablespace. |
|
5 | This bit is set for file-per-table tablespaces if the row format of the
table is DYNAMIC or
COMPRESSED . General tablespaces that do
not contain compressed tables will have the first 6 bits
set to zero, including this bit, making it appear to be
the Antelope file format. But actually, general
tablespaces may contain any combination of
REDUNDANT , COMPACT
and DYNAMIC tables. For more
information about general tablespaces, see
CREATE TABLESPACE . |
|
6-9 | These four bits contain a small number that represents the uncompressed
page size (logical page size) of the tablespace. The
setting is zero if the logical page size is the original
InnoDB default page size of 16K. |
|
10 | This bit is set if the DATA DIRECTORY option is used
with CREATE TABLE or
ALTER TABLE . This bit is
set for file-per-table tablespaces that are located in
directories other than the default data directory
(datadir ). For these
tables, a
file is present in the same location as the
file. The
file stores the actual directory path to the
file-per-table tablespace file. |
|
11 | This bit is set if the tablespace is a shared general tablespace created
using CREATE TABLESPACE . |
|
12 | This bit is set if the tablespace is dedicated to temporary tables. In
MySQL 5.7, only the predefined ibtmp1
tablespace uses this flag. |
|
In the following example, table t1
is created
with innodb_file_per_table=ON
,
which creates table t1
in its own tablespace.
When querying INNODB_SYS_TABLESPACES
,
we see that the tablespace has a FLAG
value of
33. To determine how this value is arrived at, review the bit
values described in the preceding table. Bit 0 has a value of 1
because table t1
uses the
DYNAMIC
row format. Bit 5 has a value of 32
because the tablespace is a file-per-table tablespace that uses a
DYNAMIC
row format. Bit position 6-9 is 0
because innodb_page_size
is set
to the default 16K value. The other bit values are not applicable
and are therefore set to 0. The values for bit position 0 and bit
position 5 add up to a FLAG
value of 33.
mysql> use test; Database changed mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_page_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec) mysql> SET GLOBAL innodb_file_format=Barracuda; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 (c1 int) ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME LIKE 'test/t1' \G *************************** 1. row *************************** SPACE: 75 NAME: test/t1 FLAG: 33 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 1 row in set (0.00 sec)
The INNODB_SYS_VIRTUAL
table provides metadata
about InnoDB
virtual generated columns and
columns upon which virtual generated columns are based, equivalent
to information in the SYS_VIRTUAL
table in the
InnoDB
data dictionary.
A row appears in the INNODB_SYS_VIRTUAL
table
for each column upon which a virtual generated column is based.
Table 21.18 INNODB_SYS_VIRTUAL Columns
Column name | Description |
---|---|
TABLE_ID | An identifier representing the table associated with the virtual column;
the same value as
INNODB_SYS_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. The formula is ((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. |
Example:
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=latin1; mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL -> WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "test/t1"); +----------+-------+----------+ | TABLE_ID | POS | BASE_POS | +----------+-------+----------+ | 45 | 65538 | 0 | | 45 | 65538 | 1 | +----------+-------+----------+
Notes:
If a constant value is assigned to a virtual generated column,
as in the following example, an entry for the column does not
appear in the INNODB_SYS_VIRTUAL
table. For
an entry to appear, a virtual generated column must have a
base column.
mysql> 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=latin1;
However, metadata for such a column appears in the
INNODB_SYS_COLUMNS
table.
Use DESCRIBE
or
SHOW COLUMNS
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.
The INNODB_BUFFER_PAGE
table holds information
about each page in the
InnoDB
buffer
pool.
For related usage information and examples, see Section 14.13.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Querying the INNODB_BUFFER_PAGE
table can
introduce significant performance overhead. Do not query this
table on a production system unless you are aware of the
performance impact that your query may have and have determined
it to be acceptable. To avoid impacting performance, reproduce
the issue you want to investigate on a test instance and query
the INNODB_BUFFER_PAGE
table on the test
instance.
Table 21.19 INNODB_BUFFER_PAGE Columns
Column name | Description |
---|---|
POOL_ID | Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances. |
BLOCK_ID | Buffer Pool Block ID. |
SPACE | Tablespace ID. Uses the same value as in
INNODB_SYS_TABLES.SPACE . |
PAGE_NUMBER | Page number. |
PAGE_TYPE | Page type. One of ALLOCATED (Freshly allocated page),
INDEX (B-tree node),
UNDO_LOG (Undo log page),
INODE (Index node),
IBUF_FREE_LIST (Insert buffer free
list), IBUF_BITMAP (Insert buffer
bitmap), SYSTEM (System page),
TRX_SYSTEM (Transaction system data),
FILE_SPACE_HEADER (File space header),
EXTENT_DESCRIPTOR (Extent descriptor
page), BLOB (Uncompressed BLOB page),
COMPRESSED_BLOB (First compressed BLOB
page), COMPRESSED_BLOB2 (Subsequent
comp BLOB page), IBUF_INDEX (Insert
buffer index), RTREE_INDEX (spatial index),
UNKNOWN (unknown). |
FLUSH_TYPE | Flush type. |
FIX_COUNT | Number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted. |
IS_HASHED | Whether hash index has been built on this page. |
NEWEST_MODIFICATION | Log Sequence Number of the youngest modification. |
OLDEST_MODIFICATION | Log Sequence Number of the oldest modification. |
ACCESS_TIME | An abstract number used to judge the first access time of the page. |
TABLE_NAME | Name of the table the page belongs to. This column is only applicable to
pages of type INDEX . |
INDEX_NAME | Name of the index the page belongs to. It can be the name of a clustered
index or a secondary index. This column is only applicable
to pages of type INDEX . |
NUMBER_RECORDS | Number of records within the page. |
DATA_SIZE | Sum of the sizes of the records. This column is only applicable to pages
of type INDEX . |
COMPRESSED_SIZE | Compressed page size. Null for pages that are not compressed. |
PAGE_STATE | Page state. A page with valid data has one of the following states:
FILE_PAGE (buffers a page of data from
a file), MEMORY (buffers a page from an
in-memory object), COMPRESSED . Other
possible states (managed by InnoDB )
are: NULL ,
READY_FOR_USE ,
NOT_USED ,
REMOVE_HASH . |
IO_FIX | Specifies 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 | Specifies whether or not the block is in the sublist of old blocks in the LRU list. |
FREE_PAGE_CLOCK | The value of the freed_page_clock counter when the
block was the last placed at the head of the LRU list. The
freed_page_clock counter tracks the
number of blocks removed from the end of the LRU list. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G *************************** 1. row *************************** POOL_ID: 0 BLOCK_ID: 0 SPACE: 97 PAGE_NUMBER: 2473 PAGE_TYPE: INDEX FLUSH_TYPE: 1 FIX_COUNT: 0 IS_HASHED: YES NEWEST_MODIFICATION: 733855581 OLDEST_MODIFICATION: 0 ACCESS_TIME: 3378385672 TABLE_NAME: `employees`.`salaries` INDEX_NAME: PRIMARY NUMBER_RECORDS: 468 DATA_SIZE: 14976 COMPRESSED_SIZE: 0 PAGE_STATE: FILE_PAGE IO_FIX: IO_NONE IS_OLD: YES FREE_PAGE_CLOCK: 66 1 row in set (0.03 sec)
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Use DESCRIBE
or
SHOW COLUMNS
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.
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 8.10.1, “The InnoDB Buffer Pool”.
The INNODB_BUFFER_PAGE_LRU
table holds
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
an LRU_POSITION
column instead of a
BLOCK_ID
column.
For related usage information and examples, see Section 14.13.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Querying the INNODB_BUFFER_PAGE_LRU
table can
introduce significant performance overhead. Do not query this
table on a production system unless you are aware of the
performance impact that your query may have, and have determined
it to be acceptable. To avoid impacting performance, reproduce
the issue you want to investigate on a test instance and query
the INNODB_BUFFER_PAGE_LRU
table on the test
instance.
Table 21.20 INNODB_BUFFER_PAGE_LRU Columns
Column name | Description |
---|---|
POOL_ID | Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances. |
LRU_POSITION | The position of the page in the LRU list. |
SPACE | Tablespace ID. Uses the same value as in
INNODB_SYS_TABLES.SPACE . |
PAGE_NUMBER | Page number. |
PAGE_TYPE | Page type. One of ALLOCATED (Freshly allocated page),
INDEX (B-tree node),
UNDO_LOG (Undo log page),
INODE (Index node),
IBUF_FREE_LIST (Insert buffer free
list), IBUF_BITMAP (Insert buffer
bitmap), SYSTEM (System page),
TRX_SYSTEM (Transaction system data),
FILE_SPACE_HEADER (File space header),
EXTENT_DESCRIPTOR (Extent descriptor
page), BLOB (Uncompressed BLOB page),
COMPRESSED_BLOB (First compressed BLOB
page), COMPRESSED_BLOB2 (Subsequent
comp BLOB page), IBUF_INDEX (Insert
buffer index), RTREE_INDEX (spatial index),
UNKNOWN (unknown). |
FLUSH_TYPE | Flush type. |
FIX_COUNT | Number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted. |
IS_HASHED | Whether hash index has been built on this page. |
NEWEST_MODIFICATION | Log Sequence Number of the youngest modification. |
OLDEST_MODIFICATION | Log Sequence Number of the oldest modification. |
ACCESS_TIME | An abstract number used to judge the first access time of the page. |
TABLE_NAME | Name of the table the page belongs to. This column is only applicable to
pages of type INDEX . |
INDEX_NAME | Name of the index the page belongs to. It can be the name of a clustered
index or a secondary index. This column is only applicable
to pages of type INDEX . |
NUMBER_RECORDS | Number of records within the page. |
DATA_SIZE | Sum of the sizes of the records. This column is only applicable to pages
of type INDEX . |
COMPRESSED_SIZE | Compressed page size. Null for pages that are not compressed. |
PAGE_STATE | Page state. A page with valid data has one of the following states:
FILE_PAGE (buffers a page of data from
a file), MEMORY (buffers a page from an
in-memory object), COMPRESSED . Other
possible states (managed by InnoDB )
are: NULL ,
READY_FOR_USE ,
NOT_USED ,
REMOVE_HASH . |
IO_FIX | Specifies 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 | Specifies whether or not 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 1 row in set (0.02 sec)
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the PROCESS
privilege to
query this table.
Use DESCRIBE
or
SHOW COLUMNS
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 time 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 8.10.1, “The InnoDB 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 14.13.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Table 21.21 INNODB_BUFFER_POOL_STATS Columns
Column name | Description |
---|---|
POOL_ID | Buffer Pool ID. A unique 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. The 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 | LRU IO total |
LRU_IO_CURRENT | LRU IO for the current interval |
UNCOMPRESS_TOTAL | Total number of pages decompressed |
UNCOMPRESS_CURRENT | The number of pages decompressed in the current interval |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS\G *************************** 1. row *************************** POOL_ID: 0 POOL_SIZE: 8192 FREE_BUFFERS: 1 DATABASE_PAGES: 8085 OLD_DATABASE_PAGES: 2964 MODIFIED_DATABASE_PAGES: 0 PENDING_DECOMPRESS: 0 PENDING_READS: 0 PENDING_FLUSH_LRU: 0 PENDING_FLUSH_LIST: 0 PAGES_MADE_YOUNG: 22821 PAGES_NOT_MADE_YOUNG: 3544303 PAGES_MADE_YOUNG_RATE: 357.62602199870594 PAGES_MADE_NOT_YOUNG_RATE: 0 NUMBER_PAGES_READ: 2389 NUMBER_PAGES_CREATED: 12385 NUMBER_PAGES_WRITTEN: 13111 PAGES_READ_RATE: 0 PAGES_CREATE_RATE: 0 PAGES_WRITTEN_RATE: 0 NUMBER_PAGES_GET: 33322210 HIT_RATE: 1000 YOUNG_MAKE_PER_THOUSAND_GETS: 18 NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0 NUMBER_PAGES_READ_AHEAD: 2024 NUMBER_READ_AHEAD_EVICTED: 0 READ_AHEAD_RATE: 0 READ_AHEAD_EVICTED_RATE: 0 LRU_IO_TOTAL: 0 LRU_IO_CURRENT: 0 UNCOMPRESS_TOTAL: 0 UNCOMPRESS_CURRENT: 0 1 row in set (0.00 sec)
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Use DESCRIBE
or
SHOW COLUMNS
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.
This INFORMATION_SCHEMA
table presents 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, you set one of the configuration options
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 14.13.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.
Table 21.22 INNODB_METRICS Columns
Column name | Description |
---|---|
NAME | Unique name for the counter. |
SUBSYSTEM | The aspect of InnoDB that the metric applies to. See
the list following the table for the corresponding module
names to use with the SET GLOBAL
syntax. |
COUNT | Value since the counter is enabled. |
MAX_COUNT | Maximum value since the counter is enabled. |
MIN_COUNT | Minimum value since the counter is enabled. |
AVG_COUNT | Average value since the counter is enabled. |
COUNT_RESET | Counter value since it was last reset. (The _RESET
fields 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 the
COUNT , MAX_COUNT ,
and so on fields.) |
MAX_COUNT_RESET | Maximum counter value since it was last reset. |
MIN_COUNT_RESET | Minimum counter value since it was last reset. |
AVG_COUNT_RESET | Average counter value since it was last reset. |
TIME_ENABLED | Timestamp of last start. |
TIME_DISABLED | Timestamp of last stop. |
TIME_ELAPSED | Elapsed time in seconds since the counter started. |
TIME_RESET | Timestamp of last stop. |
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 | Counter description. |
Example:
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 1 row in set (0.00 sec)
Notes:
You must have the PROCESS
privilege to
query this table.
Use DESCRIBE
or
SHOW COLUMNS
to view additional
information about the columns of this table including data
types and default values.
The INNODB_FT_CONFIG
table displays metadata
about the FULLTEXT
index and associated
processing for an InnoDB
table.
Before you query this table, set the configuration variable
innodb_ft_aux_table
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 14.13.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
Table 21.23 INNODB_FT_CONFIG Columns
Column name | Description |
---|---|
KEY | The name designating an item of metadata for an
InnoDB table containing a
FULLTEXT index. |
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. |
Example:
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 | +---------------------------+-------------------+
Notes:
This table is only intended for internal configuration. It is not intended for statistical information purposes.
Use DESCRIBE
or
SHOW COLUMNS
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.
The values for the KEY
column might evolve
depending on the needs for performance tuning and debugging
for InnoDB
full-text processing. The key
values include:
optimize_checkpoint_limit
: The number
of seconds after which an OPTIMIZE
TABLE
run will stop.
synced_doc_id
: The next
DOC_ID
to be issued.
stopword_table_name
: The
database/table
name for a user
defined stopword table. This field appears empty if there
is no user-defined stopword table.
use_stopword
: Indicates whether or not
a stopword table is used, which is defined when the
FULLTEXT
index is created.
For more information about InnoDB
FULLTEXT
search, see
Section 14.2.6.3, “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 an InnoDB
table. 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 14.13.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
Table 21.24 INNODB_FT_DEFAULT_STOPWORD Columns
Column name | Description |
---|---|
value | A word that is used by default as a stopword for
FULLTEXT indexes on
InnoDB tables. Not used if you override
the default stopword processing with either the
innodb_ft_server_stopword_table
or the
innodb_ft_user_stopword_table
option. |
Example:
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)
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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 14.2.6.3, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_INDEX_TABLE
table displays
information about the inverted index used to process text searches
against the FULLTEXT
index of an
InnoDB
table.
For related usage information and examples, see Section 14.13.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
Before you query this table, set the configuration variable
innodb_ft_aux_table
to the name
(including the database name) of the table that contains the
FULLTEXT
index, for example
test/articles
.
Table 21.25 INNODB_FT_INDEX_TABLE Columns
Column name | Description |
---|---|
WORD | A word extracted from the text of the columns that are part of a
FULLTEXT . |
FIRST_DOC_ID | The first document ID that this word appears in the
FULLTEXT index. |
LAST_DOC_ID | The last document ID that this word appears in the
FULLTEXT index. |
DOC_COUNT | The number of rows 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 does not
contain a suitable column. |
POSITION | The position of this particular instance of the word within the relevant
document identified by the DOC_ID
value. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table
. The
following example demonstrates how to use the
innodb_ft_aux_table
option 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
innodb_optimize_fulltext_only=ON
.
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; Query OK, 0 rows affected (0.00 sec) mysql> OPTIMIZE TABLE articles; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.articles | optimize | status | OK | +---------------+----------+----------+----------+ 1 row in set (0.00 sec) mysql> SET GLOBAL innodb_ft_aux_table = 'test/articles'; Query OK, 0 rows affected (0.00 sec) mysql> USE INFORMATION_SCHEMA; mysql> SELECT word, doc_count, doc_id, position FROM 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 | +------------+-----------+--------+----------+
Use DESCRIBE
or
SHOW COLUMNS
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 14.2.6.3, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
INNODB_FT_INDEX_CACHE
: Contains 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
innodb_ft_cache_size
or
innodb_ft_total_cache_size
.
Before you query this table, set the configuration variable
innodb_ft_aux_table
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 14.13.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
Table 21.26 INNODB_FT_INDEX_CACHE Columns
Column name | Description |
---|---|
WORD | A word extracted from the text of a newly inserted row. |
FIRST_DOC_ID | The first document ID that this word appears in the
FULLTEXT index. |
LAST_DOC_ID | The last document ID that this word appears in the
FULLTEXT index. |
DOC_COUNT | The number of rows 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 does not contain
a 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. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table
. The
following example demonstrates how to use the
innodb_ft_aux_table
option 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'; Query OK, 0 rows affected (0.00 sec) mysql> USE INFORMATION_SCHEMA; mysql> SELECT word, doc_count, doc_id, position FROM 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 | +------------+-----------+--------+----------+
Use DESCRIBE
or
SHOW COLUMNS
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 14.2.6.3, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_DELETED
table records 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 the
OPTIMIZE TABLE
statement for the
InnoDB
table. See
Optimizing InnoDB Full-Text Indexes for more information.
This table initially appears empty, until you set the value of the
configuration variable
innodb_ft_aux_table
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 14.13.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
Table 21.27 INNODB_FT_DELETED Columns
Column name | Description |
---|---|
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 does not contain
a suitable column. This value is used to skip rows in the
innodb_ft_index_table table, when you
do text searches before data for deleted rows is
physically removed from the FULLTEXT
index by an OPTIMIZE TABLE
statement. See Optimizing InnoDB Full-Text Indexes for
more information. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +--------+ | DOC_ID | +--------+ | 6 | | 7 | | 8 | +--------+
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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 14.2.6.3, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_BEING_DELETED
table is a snapshot
of the INNODB_FT_DELETED
table that
is only used during an OPTIMIZE
TABLE
maintenance operation. When
OPTIMIZE TABLE
is run, the
INNODB_FT_BEING_DELETED
table is emptied, and
DOC_IDs 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 initially appears empty, until you set the value of the
configuration variable
innodb_ft_aux_table
. The output
appears similar to the example provided for the
INNODB_FT_DELETED
table.
For related usage information and examples, see Section 14.13.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
Table 21.28 INNODB_FT_BEING_DELETED Columns
Column name | Description |
---|---|
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 does not contain a suitable column. This value is
used to skip rows in the
innodb_ft_index_table table, when you
do text searches before data for deleted rows is
physically removed from the FULLTEXT
index by an OPTIMIZE TABLE
statement. See Optimizing InnoDB Full-Text Indexes for
more information. |
Notes:
Use DESCRIBE
or
SHOW COLUMNS
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 14.2.6.3, “InnoDB FULLTEXT Indexes”, and
Section 12.9, “Full-Text Search Functions”.
INNODB_TEMP_TABLE_INFO
contains metadata about
active InnoDB
temporary tables. With the
exception of optimized internal temporary tables used by
InnoDB
,
INNODB_TEMP_TABLE_INFO
reports on all user and
system-created temporary tables that are active within a given
InnoDB
instance. The table is maintained in
memory and not persisted to disk.
Prior to the introduction of the
INNODB_TEMP_TABLE_INFO
table in MySQL 5.7.1,
InnoDB
temporary table metadata was stored in
InnoDB
system tables.
For usage information and examples, see Section 14.13.7, “InnoDB INFORMATION_SCHEMA Temporary Table Information Table”.
Table 21.29 INNODB_TEMP_TABLE_INFO Columns
Column name | Description |
---|---|
TABLE_ID | The table ID of the active temporary table. |
NAME | The name of the active temporary table. |
N_COLS | The number of columns in the temporary table. The number always includes
three hidden columns created by InnoDB
(DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR). |
SPACE | The tablespace identifier (a numerical value) for the tablespace in
which the temporary table resides. As of MySQL 5.7.1, all
non-compressed InnoDB temporary tables
reside in a shared temporary table tablespace, as defined
by
innodb_temp_data_file_path .
By default the shared temporary tablespace is named
ibtmp1 and located in the
data directory. Compressed temporary
tables reside in separate per-table tablespaces located in
the temporary file directory, as defined by
tmpdir . The
SPACE ID is always a non-zero value and
is dynamically generated on server restart. |
PER_TABLE_SPACE | A value of TRUE indicates that the temporary table
resides in a separate per-table tablespace. A value of
FALSE indicates that the temporary
table resides in the shared temporary tablespace. |
IS_COMPRESSED | A value of TRUE indicates that the temporary table is
compressed. |
Example:
mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G *************************** 1. row *************************** TABLE_ID: 32 NAME: #sqlaf56_2_0 N_COLS: 4 SPACE: 19 PER_TABLE_TABLESPACE: FALSE IS_COMPRESSED: FALSE 1 row in set (0.00 sec)
Notes:
This table is primarily useful for expert level monitoring.
Use DESCRIBE
or
SHOW COLUMNS
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.
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 |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
27 rows in set (0.00 sec)
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 |
+---------+---------------------------+---------------------+--------+