Table of Contents [+/-]
INFORMATION_SCHEMA provides access to database
Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.
INFORMATION_SCHEMA is the information database,
the place that stores information about all the other databases that
the MySQL server maintains. Inside
INFORMATION_SCHEMA there are 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 DESC;+------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | v56 | VIEW | NULL | | v3 | VIEW | NULL | | v2 | VIEW | NULL | | v | VIEW | NULL | | tables | BASE TABLE | MyISAM | | t7 | BASE TABLE | MyISAM | | t3 | BASE TABLE | MyISAM | | t2 | BASE TABLE | MyISAM | | t | BASE TABLE | MyISAM | | pk | BASE TABLE | InnoDB | | loop | BASE TABLE | MyISAM | | kurs | BASE TABLE | MyISAM | | k | BASE TABLE | MyISAM | | into | BASE TABLE | MyISAM | | goto | BASE TABLE | MyISAM | | fk2 | BASE TABLE | InnoDB | | fk | BASE TABLE | InnoDB | +------------+------------+--------+ 17 rows in set (0.01 sec)
Explanation: The statement requests a list of all the tables in
db5, in reverse alphabetic order,
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
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. Values
such as table names in
are treated as strings, not identifiers, and are not compared using
the identifier rules described in
Section 9.2.2, “Identifier Case Sensitivity”. If the result of a
string operation on an
differs from expectations, a workaround is to use an explicit
COLLATE clause to force a suitable collation
(Section 10.1.7.2, “Using COLLATE in SQL Statements”). You can also use the
LOWER() function. For example, in a
WHERE clause, you might use:
WHERE TABLE_NAME COLLATE utf8_bin = 'City' WHERE TABLE_NAME COLLATE utf8_general_ci = 'city' WHERE UPPER(TABLE_NAME) = 'CITY' WHERE LOWER(TABLE_NAME) = 'city'
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 will see
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. That is, all access is done on tables.
Nobody needs to learn a new statement syntax. Because they
already know how
they only need to learn the object names.
The implementor need not worry about adding keywords.
There are millions of possible output variations, instead of just one. This provides more flexibility for applications that have varying requirements about what metadata they need.
Migration is easier because every other DBMS does it this way.
SHOW is popular and
because it might be confusing were it to disappear, the advantages
of conventional syntax are not a sufficient reason to eliminate
SHOW. In fact, along with the
INFORMATION_SCHEMA, there are
SHOW as well. These
are described in Section 19.18, “Extensions to SHOW Statements”.
There is no difference between the privileges required for
SHOW statements and those required to
select information from
either case, you have to have some privilege on an object in order
to see information about it.
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
The following sections describe each of the tables and columns that
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.
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:
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 = DATABASE() condition to the
WHERE clause of a query that retrieves
information from an
For answers to questions that are often asked concerning the
INFORMATION_SCHEMA database, see
Section A.7, “MySQL 5.0 FAQ: INFORMATION_SCHEMA”.