Chapter 23. The INFORMATION_SCHEMA Database
INFORMATION_SCHEMA provides access to database
metadata.
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.
In effect, we have a database named
INFORMATION_SCHEMA, although the server does not
create a database directory with that name. It is possible to select
INFORMATION_SCHEMA as the default database with a
USE statement, but it is possible only to read
the contents of tables. You cannot insert into them, update them, or
delete from them.
Here is an example of a statement that retrieves information from
INFORMATION_SCHEMA:
mysql> SELECT table_name, table_type, engine
-> FROM information_schema.tables
-> 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
database db5, in reverse alphabetical order,
showing just three pieces of information: the name of the table, its
type, and its storage engine.
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.
The 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 DATABASES,
SHOW TABLES, and so forth). Using
SELECT has these advantages, compared to
SHOW:
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 SELECT works, 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.
However, because SHOW is popular with MySQL
employees and users, 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 implementation of
INFORMATION_SCHEMA, there are enhancements to
SHOW as well. These are described in
Section 23.24, “Extensions to SHOW Statements”.
There is no difference between the privileges required for
SHOW statements and those required to select
information from INFORMATION_SCHEMA. In either
case, you have to have some privilege on an object in order to see
information about it.
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 INFORMATION_SCHEMA.TABLES table.
Although other DBMSs use a variety of names, like
syscat or system, the standard
name is INFORMATION_SCHEMA.
The following sections describe each of the tables and columns that
are in INFORMATION_SCHEMA. For each column, there
are three pieces of information:
“INFORMATION_SCHEMA Name”
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.”
“SHOW Name” indicates the
equivalent field name in the closest SHOW
statement, 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.
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
COLLATION to TABLE_COLLATION
in the TABLES table.) See the list of reserved
words near the end of this article:
https://www.dbazine.com/gulutzan5.shtml.
The definition for character columns (for example,
TABLES.TABLE_NAME), is generally
VARCHAR(N) CHARACTER SET
utf8 where N is at least 64.
Each section indicates what SHOW statement is
equivalent to a SELECT that retrieves information
from INFORMATION_SCHEMA, if there is such a
statement.
Note: At present, there are some
missing columns and some columns out of order. We are working on
this and update the documentation as changes are made.
