Table of Contents [+/-]
- 20.1. The
- 20.2. The
- 20.3. The
- 20.4. The
- 20.5. The
- 20.6. The
- 20.7. The
- 20.8. The
- 20.9. The
- 20.10. The
- 20.11. The
- 20.12. The
- 20.13. The
- 20.14. The
- 20.15. The
- 20.16. The
- 20.17. The
- 20.18. The
- 20.19. The
- 20.20. The
- 20.21. The
- 20.22. The
- 20.23. The
- 20.24. The
- 20.25. The
- 20.26. The
- 20.27. Other
- 20.28. Extensions to
INFORMATION_SCHEMAprovides 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_SCHEMAis the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside
INFORMATION_SCHEMAthere 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_SCHEMAas the default database with a
USEstatement, 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
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 database
db5, in reverse alphabetical 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 utf8
Nis 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. However, searches in
INFORMATION_SCHEMAstring columns are also affected by file system case sensitivity. For more information, see Section 188.8.131.52, “Collation and
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_DEFINITIONcolumn in the
INFORMATION_SCHEMA.ROUTINEStable), users who have insufficient privileges will see
SELECT ... FROM INFORMATION_SCHEMAstatement is intended as a more consistent way to provide access to the information provided by the various
SHOWstatements that MySQL supports (
SHOW TABLES, and so forth). Using
SELECThas these advantages, compared to
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
SELECTworks, 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.
SHOWis 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 implementation of
INFORMATION_SCHEMA, there are enhancements to
SHOWas well. These are described in Section 20.28, “Extensions to
There is no difference between the privileges required for
SHOWstatements 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.
INFORMATION_SCHEMAqueries 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
EXPLAINoutput that is specific to interpreting the cost of
INFORMATION_SCHEMAqueries, see Section 7.2.20, “
The implementation for the
INFORMATION_SCHEMAtable 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
ENGINEcolumn in the
Although other DBMSs use a variety of names, like
system, the standard name is
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_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.”
“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
TABLEStable.) See the list of reserved words near the end of this article: http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5.
Each section indicates what
SHOWstatement is equivalent to a
SELECTthat retrieves information from
INFORMATION_SCHEMA, if there is such a statement. For
SHOWstatements that display information for the default database if you omit a
FROMclause, you can often select information for the default database by adding an
AND TABLE_SCHEMA = CURRENT_SCHEMA()condition to the
WHEREclause of a query that retrieves information from an
At present, there are some missing columns and some columns out of order. We are working on this and updating the documentation as changes are made.
For answers to questions that are often asked concerning the
INFORMATION_SCHEMAdatabase, see Section A.7, “MySQL 5.1 FAQ:
- 20.1. The