INFORMATION_SCHEMA 使用注意事项

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.

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.

Character Set Considerations

The definition for character columns (for example,
TABLES.TABLE_NAME) is generally
VARCHAR(N) CHARACTER SET
utf8 where N 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.

INFORMATION_SCHEMA as Alternative to SHOW Statements

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.

Privileges

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.

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.

Conventions in INFORMATION_SCHEMA Reference Sections

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
db_name clause, you can often
select information for the default database by adding an
AND TABLE_SCHEMA = SCHEMA() condition to the
WHERE clause of a query that retrieves
information from an INFORMATION_SCHEMA table.

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

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

CHARACTER_OCTET_LENGTH should be the same
as CHARACTER_MAXIMUM_LENGTH, except for
multibyte character sets.

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

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.

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

EVENT_NAME: The name of the event.

DEFINER: The account of the user who
created the event, in
'user_name'@'host_name'
format.

TIME_ZONE: The event time zone, which is
the time zone used for scheduling the event and that is in
effect within the event as it executes. The default value is
SYSTEM.

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

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

EVENT_DEFINITION: The text of the SQL
statement making up the event's
DO clause; in other words, the
statement executed by this event.

EVENT_TYPE: The event repetition type,
either ONE TIME (transient) or
RECURRING (repeating).

EXECUTE_AT: For a one-time event, this is
the DATETIME value specified in
the AT clause of the
CREATE EVENT statement used to
create the event, or of the last ALTER
EVENT statement that modified the event. The value
shown in this column reflects the addition or subtraction of
any INTERVAL value included in the event's
AT clause. For example, if an event is
created using ON SCHEDULE AT CURRENT_TIMESTAMP +
'1:6' DAY_HOUR, and the event was created at
2006-02-09 14:05:30, the value shown in this column would be
'2006-02-10 20:05:30'.

If the event's timing is determined by an
EVERY clause instead of an
AT clause (that is, if the event is
recurring), the value of this column is
NULL.

INTERVAL_VALUE: For recurring events, this
column contains the numeric portion of the event's
EVERY clause.

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

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

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

SQL_MODE: The SQL mode in effect when the
event was created or altered, and under which the event
executes. For the permitted values, see
Section 5.1.8, “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 18.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
ENABLE;

(Note that comments can span multiple lines.)

This user can then run the following
SELECT statement, and obtain the
output shown:

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

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

FILE_TYPE is the tablespace file type.
There are three possible file types for
InnoDB files. TABLESPACE
is the file type for any system, general, or file-per-table
tablespace file that holds tables, indexes, or other forms of
user data. TEMPORARY is the file type for
temporary tablespaces. UNDO LOG is the file
type for undo tablespaces, which hold undo records. Separate
undo tablespaces are configured 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 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.

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.

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.

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

TABLE_CATALOG: This column is always
def.

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

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

PARTITION_NAME: The name of the partition.

SUBPARTITION_NAME: If the
PARTITIONS table record
represents a subpartition, then this column contains the name
of subpartition; otherwise it is NULL.

PARTITION_ORDINAL_POSITION: All partitions
are indexed in the same order as they are defined, with
1 being the number assigned to the first
partition. The indexing can change as partitions are added,
dropped, and reorganized; the number shown is this column
reflects the current order, taking into account any indexing
changes.

SUBPARTITION_ORDINAL_POSITION:
Subpartitions within a given partition are also indexed and
reindexed in the same manner as partitions are indexed within
a table.

PARTITION_METHOD: One of the values
RANGE, LIST,
HASH, LINEAR HASH,
KEY, or LINEAR KEY; that
is, one of the available partitioning types as discussed in
Section 22.2, “Partitioning Types”.

SUBPARTITION_METHOD: One of the values
HASH, LINEAR HASH,
KEY, or LINEAR KEY; that
is, one of the available subpartitioning types as discussed in
Section 22.2.6, “Subpartitioning”.

PARTITION_EXPRESSION: This is the
expression for the partitioning function used in the
CREATE TABLE or
ALTER TABLE statement that
created the table's current partitioning scheme.

For example, consider a partitioned table created in the
test database using this statement:

SUBPARTITION_EXPRESSION: This works in the
same fashion for the subpartitioning expression that defines
the subpartitioning for a table as
PARTITION_EXPRESSION does for the
partitioning expression used to define a table's partitioning.

If the table has no subpartitions, then this column is
NULL.

PARTITION_DESCRIPTION: This column is used
for RANGE and LIST partitions. For a RANGE
partition, it contains the value set in the partition's
VALUES LESS THAN clause, which can be
either an integer or MAXVALUE. For a
LIST partition, this column contains the
values defined in the partition's VALUES IN
clause, which is a comma-separated list of integer values.

For partitions whose PARTITION_METHOD is
other than RANGE or
LIST, this column is always
NULL.

TABLE_ROWS: The number of table rows in the
partition.

For partitioned InnoDB tables,
the row count given in the TABLE_ROWS
column is only an estimated value used in SQL optimization,
and may not always be exact.

AVG_ROW_LENGTH: The average length of the
rows stored in this partition or subpartition, in bytes.

This is the same as DATA_LENGTH divided by
TABLE_ROWS.

DATA_LENGTH: The total length of all rows
stored in this partition or subpartition, in bytes—that
is, the total number of bytes stored in the partition or
subpartition.

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

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

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

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

UPDATE_TIME: The time that the partition or
subpartition was last modified.

CHECK_TIME: The last time that the table to
which this partition or subpartition belongs was checked.

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

In MySQL 8.0, the maximum length for a partition
comment is defined as 1024 characters, and the display width
of the PARTITION_COMMENT column is also
1024, characters to match this limit (Bug #11748924, Bug
#37728).

The default value for this column is an empty string.

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

TABLESPACE_NAME: This column contains the
name of the tablespace to which the partition belongs. The
value of this column is always DEFAULT.

A nonpartitioned table has one record in
INFORMATION_SCHEMA.PARTITIONS;
however, the values of the PARTITION_NAME,
SUBPARTITION_NAME,
PARTITION_ORDINAL_POSITION,
SUBPARTITION_ORDINAL_POSITION,
PARTITION_METHOD,
SUBPARTITION_METHOD,
PARTITION_EXPRESSION,
SUBPARTITION_EXPRESSION, and
PARTITION_DESCRIPTION columns are all
NULL. (The
PARTITION_COMMENT column in this case is
blank.)

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 library file. This is the name used to refer to the
plugin file in statements such as INSTALL
PLUGIN and UNINSTALL
PLUGIN. This file is located in the directory named
by the plugin_dir system
variable. If the library name is NULL, the
plugin is compiled in and cannot be uninstalled with
UNINSTALL PLUGIN.

PLUGIN_LIBRARY_VERSION indicates the plugin
API interface version.

PLUGIN_AUTHOR names the plugin author.

PLUGIN_DESCRIPTION provides a short
description of the plugin.

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

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.

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

24.19 INFORMATION_SCHEMA SCHEMATA Table

A schema is a database, so the
SCHEMATA table provides information
about databases.

If the innodb_read_only system
variable is enabled, ANALYZE
TABLE may fail because it cannot update statistics
tables in the data dictionary, which use
InnoDB. For ANALYZE
TABLE operations that update the key distribution,
failure may occur even if the operation updates the table itself
(for example, if it is a MyISAM table). To
obtain the updated distribution statistics, set
information_schema_stats=LATEST.

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
FROM tbl_name
FROM db_name

24.22 INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table

The ST_GEOMETRY_COLUMNS table
provides information about table columns that store spatial data.
This table is based on the SQL/MM (ISO/IEC 13249-3) standard, with
extensions as noted. MySQL implements
ST_GEOMETRY_COLUMNS as a view on the
INFORMATION_SCHEMACOLUMNS table.

24.23 INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table

The ST_SPATIAL_REFERENCE_SYSTEMS
table provides information about available spatial reference
systems for spatial data. This table is based on the SQL/MM
(ISO/IEC 13249-3) standard. MySQL implements
ST_SPATIAL_REFERENCE_SYSTEMS as a
view on the st_spatial_reference_systems data
dictionary table.

SRS_ID values represent the same kind of
values passed as the SRID argument to spatial functions.

SRID 0 is special. It is always a legal spatial reference
system ID and can be used in any computations on spatial data
that depend on SRID values. However, SRID 0 has no entry in
ST_SPATIAL_REFERENCE_SYSTEMS
because it cannot be described by the SRS definition format.
It represents an infinite flat Cartesian plane with no units
assigned to its axes. Unlike projected SRSs, it is not
georeferenced and it does not necessarily represent Earth. It
is an abstract plane that can be used for anything.

ORGANIZATION

The name of the organization that defined the coordinate
system on which the spatial reference system is based.

ORGANIZATION_COORDSYS_ID

The numeric ID given to the spatial reference system by the
organization that defined it.

DEFINITION

The spatial reference system definition, as a WKT value.

DESCRIPTION

The spatial reference system description.

Notes:

The SRS_NAME,
ORGANIZATION,
ORGANIZATION_COORDSYS_ID, and
DESCRIPTION columns contain information
that may be of interest to users, but they are not used by
MySQL.

If the innodb_read_only system
variable is enabled, ANALYZE
TABLE may fail because it cannot update statistics
tables in the data dictionary, which use
InnoDB. For ANALYZE
TABLE operations that update the key distribution,
failure may occur even if the operation updates the table itself
(for example, if it is a MyISAM table). To
obtain the updated distribution statistics, set
information_schema_stats=LATEST.

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

TABLE_TYPE should be BASE
TABLE or VIEW. The
TABLES table does not list
TEMPORARY tables.

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

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

For InnoDB tables, the row count
is only a rough estimate used in SQL optimization. (This is
also true if the InnoDB table is
partitioned.)

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

UPDATE_TIME displays a timestamp value for
the last UPDATE,
INSERT, or
DELETE performed on
InnoDB tables that are not partitioned. For
MVCC, the timestamp value reflects the
COMMIT time, which is
considered the last update time. Timestamps are not persisted
when the server is restarted or when the table is evicted from
the InnoDB data dictionary cache.

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

EVENT_MANIPULATION: The trigger event. This
is the type of operation on the associated table for which the
trigger activates. The value is 'INSERT' (a
row was inserted), 'DELETE' (a row was
deleted), or 'UPDATE' (a row was modified).

EVENT_OBJECT_SCHEMA and
EVENT_OBJECT_TABLE: As noted in
Section 23.3, “Using Triggers”, every trigger is associated with
exactly one table. These columns indicate the database in
which this table occurs, and the table name, respectively.

ACTION_ORDER: The ordinal position of the
trigger's action within the list of triggers on the same table
with the same EVENT_MANIPULATION and
ACTION_TIMING values.

ACTION_STATEMENT: The trigger body; that
is, the statement executed when the trigger activates. This
text uses UTF-8 encoding.

ACTION_ORIENTATION: Always contains the
value 'ROW'.

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

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

CREATED: The date and time when the trigger
was created. This is a TIMESTAMP(2) value
(with a fractional part in hundredths of seconds) for
triggers.

SQL_MODE: The SQL mode in effect when the
trigger was created, and under which the trigger executes. For
the permitted values, see Section 5.1.8, “Server SQL Modes”.

DEFINER: The account of the user who
created the trigger, in
'user_name'@'host_name'
format.

CHARACTER_SET_CLIENT: The session value of
the character_set_client
system variable when the trigger was created.

COLLATION_CONNECTION: The session value of
the collation_connection
system variable when the trigger was created.

DATABASE_COLLATION: The collation of the
database with which the trigger is associated.

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

The VIEW_DEFINITION column has most of what
you see in the Create Table field that
SHOW CREATE VIEW produces. Skip
the words before SELECT and
skip the words WITH CHECK OPTION. Suppose
that the original statement was:

CREATE VIEW v AS
SELECT s2,s1 FROM t
WHERE s1 > 5
ORDER BY s1
WITH CHECK OPTION;

Then the view definition looks like this:

SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1

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

MySQL sets a flag, called the view updatability flag, at
CREATE VIEW time. The flag is
set to YES (true) if
UPDATE and
DELETE (and similar operations)
are legal for the view. Otherwise, the flag is set to
NO (false). The
IS_UPDATABLE column in the
VIEWS table displays the status
of this flag. It means that the server always knows whether a
view is updatable.

DEFINER: The account of the user who
created the view, in
'user_name'@'host_name'
format. SECURITY_TYPE has a value of
DEFINER or INVOKER.

CHARACTER_SET_CLIENT: The session value of
the character_set_client
system variable when the view was created.

COLLATION_CONNECTION: The session value of
the collation_connection
system variable when the view was created.

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:

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.

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

24.31.1 INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

The INNODB_BUFFER_PAGE table holds information
about each page in the
InnoDBbuffer
pool.

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.

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.

When tables, table rows, partitions, or indexes are deleted,
associated pages remain in the buffer pool until space is
required for other data. The
INNODB_BUFFER_PAGE table reports
information about these pages until they are evicted from the
buffer pool. For more information about how the
InnoDB manages buffer pool data, see
Section 15.6.3.1, “The InnoDB Buffer Pool”.

24.31.2 INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

The INNODB_BUFFER_PAGE_LRU table holds
information about the pages in the InnoDBbuffer 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.

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.

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.

Notes

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 15.6.3.1, “The InnoDB Buffer Pool”.

24.31.3 INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table

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.

This query returns the number of index pages cached in the
InnoDB buffer pool for each index, and uses the
INNODB_SYS_INDEXES and
INNODB_SYS_TABLES to resolve the
table name and index name for each INDEX_ID
value.

SELECT
tables.name AS table_name,
indexes.name AS index_name,
cached.n_cached_pages AS n_cached_pages
FROM
INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS indexes,
INFORMATION_SCHEMA.INNODB_SYS_TABLES AS tables
WHERE
cached.index_id = indexes.index_id
AND indexes.table_id = tables.table_id;

Notes:

Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.

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.

The INNODB_CMPMEM and
INNODB_CMPMEM_RESET tables contain status
information on compressed pages
within the InnoDBbuffer pool.

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

The INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET tables contain
status information on operations related to
compressedInnoDB 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.

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.

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.

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.

24.31.10 INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table

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

24.31.11 INFORMATION_SCHEMA INNODB_FT_DELETED Table

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

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.

24.31.12 INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table

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.

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.

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

The tables differ in the privileges required: The
INNODB_LOCK_WAITS table requires
the global PROCESS privilege. The
data_lock_waits table requires the
usual Performance Schema privilege of
SELECT on the table to be
selected from.

The following table shows the mapping from
INNODB_LOCK_WAITS columns to
data_lock_waits columns. Use this
information to migrate applications from one table to the other.

Table 24.15 Mapping from INNODB_LOCK_WAITS to data_lock_waits Columns

INNODB_LOCK_WAITS Column

data_lock_waits Column

REQUESTING_TRX_ID

REQUESTING_ENGINE_TRANSACTION_ID

REQUESTED_LOCK_ID

REQUESTING_ENGINE_LOCK_ID

BLOCKING_TRX_ID

BLOCKING_ENGINE_TRANSACTION_ID

BLOCKING_LOCK_ID

BLOCKING_ENGINE_LOCK_ID

24.31.16 INFORMATION_SCHEMA INNODB_METRICS 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.

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.

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 24.31.26, “INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table”.

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(N);
that is, it might be
2*N,
3*N, and so
on depending on the character encoding.

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 tablespaces, and undo tablespaces (if
present).

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

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.

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

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
InnoDBsystem
tablespace. Any other number represents either a
file-per-table
tablespace or a general tablespace. This identifier stays
the same after a TRUNCATE
TABLE statement. For file-per-table tablespaces,
this identifier is unique for tables across all databases
in the instance.

ROW_FORMAT

The table's row format (Compact, Redundant, Dynamic, or Compressed).

ZIP_PAGE_SIZE

The zip page size. 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
TABLETABLESPACE=innodb_system clause have a
GeneralSPACE_TYPE.

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.

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.

0 - REDUNDANT

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

0 - Not Compressed

2 - 1024 Byte Compressed Page Size

4 - 2048 Byte Compressed Page Size

6 - 4096 Byte Compressed Page Size

8 - 8192 Byte Compressed Page Size

10 - 16384 Byte Compressed Page Size

5

This bit is set if the row format is DYNAMIC or
COMPRESSED.

0 - REDUNDANT or
COMPACT

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

0 - Not a remote file-per-table tablespace

64 - A remote file-per-table tablespace

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 TABLETABLESPACE=tablespace_name
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.

The INFORMATION_SCHEMA.FILES table
provides metadata about all InnoDB tablespace
types including file-per-table tablespaces, general tablespaces,
the system tablespace, temporary tablespaces, and undo
tablespaces (if present).

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

This bit is set if the row format of tables in the tablespace is
DYNAMIC or
COMPRESSED. If the bit is not set, the
row format of tables in the tablespace may be either
REDUNDANT or
COMPACT. If it is a file-per-table
tablespace, you can query
INNODB_SYS_TABLES to determine if the
row format is REDUNDANT or
COMPACT.

0 - REDUNDANT or
COMPACT

1 - DYNAMIC or
COMPRESSED

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.

0 - Not Compressed

2 - 1024 Byte Compressed Page Size

4 - 2048 Byte Compressed Page Size

6 - 4096 Byte Compressed Page Size

8 - 8192 Byte Compressed Page Size

10 - 16384 Byte Compressed Page Size

12 - 32768 Byte Compressed Page Size

14 - 65536 Byte Compressed Page Size

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 that the
tablespace holds REDUNDANT or
COMPACT tables. But actually, general
tablespaces may contain any combination of
REDUNDANT, COMPACT
and DYNAMIC tables. For more
information about general tablespaces, see
CREATE TABLESPACE.

0 - REDUNDANT or
COMPACT

32 - DYNAMIC or
COMPRESSED

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.

192 - 4096 Byte Logical/Uncompressed Page Size

256 - 8192 Byte Logical/Uncompressed Page Size

0 - 16384 Byte Logical/Uncompressed Page size

384 - 32768 Byte Logical/Uncompressed Page Size

448 - 65536 Byte Logical/Uncompressed Page Size

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

0 - Not a remote file-per-table tablespace

1024 - A remote file-per-table tablespace

11

This bit is set if the tablespace is a shared general tablespace created
using CREATE TABLESPACE.

This bit is set if the tablespace is dedicated to temporary tables. Only
the predefined ibtmp1 tablespace uses
this flag.

0 - The tablespace does not contain temporary
tables, so it is not recreated upon startup.

4096 - The tablespace contains temporary tables and
is recreated on startup.

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.

24.31.25 INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View

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.

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.

24.31.26 INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table

The INNODB_SYS_VIRTUAL table provides metadata
about InnoDBvirtual generated
columns and columns upon which virtual generated columns
are based.

A row appears in the INNODB_SYS_VIRTUAL table
for each column upon which a virtual generated column is based.

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

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.

24.31.27 INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

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.

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. All
InnoDB temporary tables reside in a
shared temporary 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.

24.31.28 INFORMATION_SCHEMA INNODB_TRX Table

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.

The weight of a transaction, reflecting (but not necessarily the exact
count of) the number of rows altered and the number of
rows locked by the transaction. To resolve a deadlock,
InnoDB selects the transaction with the
smallest weight as the “victim” to roll back.
Transactions that have changed non-transactional tables
are considered heavier than others, regardless of the
number of altered and locked rows.

ID of the lock the transaction is currently waiting for, if
TRX_STATE is LOCK
WAIT; otherwise NULL. To
obtain details about the lock, join this column with the
ENGINE_LOCK_ID column of the
Performance Schema data_locks
table.

TRX_WAIT_STARTED

Time when the transaction started waiting on the lock, if
TRX_STATE is LOCK
WAIT; otherwise NULL.

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

TRX_ISOLATION_LEVEL

The isolation level of the current transaction.

TRX_UNIQUE_CHECKS

Whether unique checks are turned on or off for the current transaction.
For example, they might be turned off during a bulk data
load.

TRX_FOREIGN_KEY_CHECKS

Whether foreign key checks are turned on or off for the current
transaction. For example, they might be turned off during
a bulk data load.

TRX_LAST_FOREIGN_KEY_ERROR

Detailed error message for the last foreign key error, if any; otherwise
NULL.

TRX_ADAPTIVE_HASH_LATCHED

Whether the adaptive hash index is locked by the current transaction.
When the adaptive hash index search system is partitioned,
a single transaction does not lock the entire adaptive
hash index. Adaptive hash index partitioning is controlled
by
innodb_adaptive_hash_index_parts,
which is set to 8 by default.

TRX_ADAPTIVE_HASH_TIMEOUT

Whether to relinquish the search latch immediately for the adaptive hash
index, or reserve it across calls from MySQL. When there
is no adaptive hash index contention, this value remains
zero and statements reserve the latch until they finish.
During times of contention, it counts down to zero, and
statements release the latch immediately after each row
lookup. When the adaptive hash index search system is
partitioned (controlled by
innodb_adaptive_hash_index_parts),
the value remains 0.

TRX_IS_READ_ONLY

A value of 1 indicates the transaction is read only.

TRX_AUTOCOMMIT_NON_LOCKING

A value of 1 indicates the transaction is a
SELECT statement that does
not use the FOR UPDATE or LOCK
IN SHARED MODE clauses, and is executing with
autocommit enabled so
that the transaction will only contain this one statement.
When this column and TRX_IS_READ_ONLY
are both 1, InnoDB optimizes the
transaction to reduce the overhead associated with
transactions that change table data.

The user/host combination of a client that has failed
connection attempts, in
'user_name'@'host_name'
format.

FAILED_ATTEMPTS

The current number of consecutive failed connection attempts
for the USERHOST value. This counts all
failed attempts, regardless of whether they were delayed. The
number of attempts for which the server added a delay to its
response is the difference between the
FAILED_ATTEMPTS value and the
connection_control_failed_connections_threshold
system variable value.

Notes:

The
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
plugin must be activated for this table to be available, and
the CONNECTION_CONTROL plugin must be
activated or the table contents will always be empty. See
Section 6.5.2, “The Connection-Control Plugins”.

The table contains rows only for clients that have had one or
more consecutive failed connection attempts without a
subsequent successful attempt. When a client connects
successfully, its failed-connection count is reset to zero and
the server removes any row corresponding to the client.

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:

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