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
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.
Here is an example of a statement that retrieves information from
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
db5, showing just three pieces of
information: the name of the table, its type, and its storage
The definition for character columns (for example,
TABLES.TABLE_NAME) is generally
N) CHARACTER SET
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.
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
Using Collation in INFORMATION_SCHEMA Searches.
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 TABLES, and so forth). Using
SELECT has these advantages, compared
It conforms to Codd's rules, because all access is done on tables.
You can use the familiar syntax of the
SELECTstatement, 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_SCHEMAqueries 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.
SHOW is familiar and widely
SHOW statements remain as
an alternative. In fact, along with the implementation of
INFORMATION_SCHEMA, there are enhancements to
SHOW as described in
Chapter 27, 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,
ROUTINE_DEFINITION column in the
users who have insufficient privileges see
These restrictions do not apply for
InnoDB tables; you can see them with
The same privileges apply to selecting information from
INFORMATION_SCHEMA and viewing the same
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
Optimizing INFORMATION_SCHEMA Queries.
The implementation for the
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
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
column in the
Although other DBMSs use a variety of names, like
system, the standard
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
TABLES table.) See the list of
reserved words near the end of this article:
The following sections describe each of the tables and columns in
INFORMATION_SCHEMA. For each column, there are
three pieces of information:
INFORMATION_SCHEMAName” indicates the name for the column in the
INFORMATION_SCHEMAtable. This corresponds to the standard SQL name unless the “Remarks” field says “MySQL extension.”
“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
statement is equivalent to a
that retrieves information from
SHOW statements that display
information for the default database if you omit a
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
These sections discuss additional
INFORMATION_SCHEMAtables specific to the thread pool plugin: Thread Pool INFORMATION_SCHEMA Tables
Answers to questions that are often asked concerning the
INFORMATION_SCHEMAdatabase: Chapter 28, MySQL 5.7 FAQ: INFORMATION_SCHEMA
INFORMATION_SCHEMAqueries and the optimizer: Optimizing INFORMATION_SCHEMA Queries
The effect of collation on
INFORMATION_SCHEMAcomparisons: Using Collation in INFORMATION_SCHEMA Searches