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
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
SELECT table_name, table_type, engine->
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
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
N) CHARACTER SET utf8
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
string columns can be affected by file system case sensitivity. For more information, see Section
10.1.7.9, "Collation and
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 to
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.
SHOW is familiar and widely used, the
SHOW statements remain as an alternative. In fact, along with the implementation
INFORMATION_SCHEMA, there are enhancements to
SHOW as described in Section
19.31, "Extensions to
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
NULL. These restrictions do not apply for
InnoDB tables; you can see them with only the
The same privileges apply to selecting information from
viewing the same information through
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
queries, see Section 8.2.4, "Optimizing
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
Although other DBMSs use a variety of names, like
the 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
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 in
INFORMATION_SCHEMA. For each column, there are three pieces of information:
INFORMATION_SCHEMA Name" indicates the name for the column in
INFORMATION_SCHEMA table. 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
SHOW statement is equivalent to a
SELECT that retrieves information from
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
AND TABLE_SCHEMA = SCHEMA()
condition to the
WHERE clause of a query that retrieves information from an
For information about
INFORMATION_SCHEMA tables specific to the
InnoDB storage engine, see Section
INFORMATION_SCHEMA Tables for
For answers to questions that are often asked concerning the
database, see Section B.7, "MySQL 5.7 FAQ: