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
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
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
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
Section 10.1.7.9, “Collation and 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
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
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
SHOW statements remain as
an alternative. In fact, along with the implementation of
INFORMATION_SCHEMA, there are enhancements to
SHOW as described in
Section 20.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
Section 8.3.3, “INFORMATION_SCHEMA Optimization”.
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
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:
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.”
“Remarks” provides additional information where
applicable. If this field is
NULL, it means
that the value of the column is always
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
For information about
specific to the
NDB storage engine
(MySQL Cluster), see Section 20.26, “INFORMATION_SCHEMA Tables for MySQL Cluster”.
For answers to questions that are often asked concerning the
INFORMATION_SCHEMA database, see
Section A.7, “MySQL 5.1 FAQ: INFORMATION_SCHEMA”.