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
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
mysql> SELECT table_name, table_type, engine FROM information_schema.tables 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
db5, 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
INFORMATION_SCHEMA string columns can be affected by file system case sensitivity. For more information, see Section 10.8.7, “Using Collation in 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 to
It conforms to Codd's rules, because all access is done on tables.
You can use the familiar syntax of the
SELECTstatement, 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_SCHEMAqueries 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 of
INFORMATION_SCHEMA, there are enhancements to
SHOW as described in Section 24.35, “Extensions to SHOW Statements”.
INFORMATION_SCHEMA tables, each MySQL user has the right to access them, 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
ROUTINES table), users who have insufficient privileges see
NULL. Some tables have different privilege requirements; for these, the requirements are mentioned in the applicable table descriptions. For example,
InnoDB tables (tables with names that begin with
INNODB_) require the
The same privileges apply to selecting information from
INFORMATION_SCHEMA and viewing the same information through
SHOW 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
INFORMATION_SCHEMA queries, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
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 added column is the
ENGINE column in the
Although other DBMSs use a variety of names, like
system, 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 changed
TABLE_COLLATION in the
TABLES table.) See the list of reserved words near the end of this article: https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5 .
The following sections describe each of the tables and columns 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.
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
These sections discuss additional
INFORMATION_SCHEMAtables specific to the thread pool plugin: Section 24.33, “INFORMATION_SCHEMA Thread Pool Tables”
INFORMATION_SCHEMAtables specific to the
CONNECTION_CONTROLplugin: Section 24.34, “INFORMATION_SCHEMA Connection-Control Tables”
Answers to questions that are often asked concerning the
INFORMATION_SCHEMAdatabase: Section A.7, “MySQL 5.7 FAQ: INFORMATION_SCHEMA”
INFORMATION_SCHEMAqueries and the optimizer: Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”
The effect of collation on
INFORMATION_SCHEMAcomparisons: Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”