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
Although you can select
the default database with a
statement, you can only read the contents of tables, not perform
DELETE operations on them.
Here is an example of a statement that retrieves information
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
SHOW TABLES, and so forth). Using
SELECT has these advantages,
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 used, the
remain as an alternative. In fact, along with the implementation
INFORMATION_SCHEMA, there are enhancements
SHOW as described in
Chapter 9, Extensions to SHOW Statements.
INFORMATION_SCHEMA tables, each
MySQL user has the right to access them, 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
ROUTINES table), users who have
insufficient privileges see
NULL. Some tables
have different privilege requirements; for these, the
requirements are mentioned in the applicable table descriptions.
InnoDB tables (tables
with names that begin with
The same privileges apply to selecting information from
INFORMATION_SCHEMA and viewing the same
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
EXPLAIN. For information
EXPLAIN output to
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 information schema.
Users of SQL Server 2000 (which also follows the standard) may
notice a strong similarity. However, MySQL has omitted many
columns that are not relevant for our implementation, and added
columns that are MySQL-specific. One such added column is the
ENGINE column in the
Although other DBMSs use a variety of names, like
standard name is
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
TABLE_COLLATION in the
TABLES table.) See the list of
reserved words near the end of this article:
The following sections describe each of the tables and columns
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.”
SHOWName” indicates the equivalent field name in the closest
SHOWstatement, 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
statement is equivalent to a
SELECT that retrieves information
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
InnoDBstorage engine: Chapter 5, INFORMATION_SCHEMA InnoDB Tables
INFORMATION_SCHEMAtables specific to the thread pool plugin: Chapter 6, INFORMATION_SCHEMA Thread Pool Tables
INFORMATION_SCHEMAtables specific to the
CONNECTION_CONTROLplugin: Chapter 7, INFORMATION_SCHEMA Connection-Control Tables
Answers to questions that are often asked concerning the
INFORMATION_SCHEMAdatabase: Chapter 10, 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