Movatterモバイル変換

• INFORMATION_SCHEMA Usage Notes

• Character Set Considerations

• INFORMATION_SCHEMA as Alternative to SHOW Statements

• INFORMATION_SCHEMA and Privileges

• Performance Considerations

• Standards Considerations

• Conventions in the INFORMATION_SCHEMA Reference Sections

• Related Information

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. TheINFORMATION_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.

Although you can selectINFORMATION_SCHEMA as the default database with aUSE statement, you can only read the contents of tables, not performINSERT,UPDATE, orDELETE operations on them.

Here is an example of a statement that retrieves information fromINFORMATION_SCHEMA:

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 databasedb5, showing just three pieces of information: the name of the table, its type, and its storage engine.

Information about generated invisible primary keys is visible by default in allINFORMATION_SCHEMA tables describing table columns, keys, or both, such as theCOLUMNS andSTATISTICS tables. If you wish to make such information hidden from queries that select from these tables, you can do so by setting the value of theshow_gipk_in_create_table_and_information_schema server system variable toOFF. For more information, seeSection 15.1.24.11, “Generated Invisible Primary Keys”.

The definition for character columns (for example,TABLES.TABLE_NAME) is generallyVARCHAR(N) CHARACTER SET utf8mb3 whereN is at least 64. MySQL uses the default collation for this character set (utf8mb3_general_ci) for all searches, sorts, comparisons, and other string operations on such columns.

Because some MySQL objects are represented as files, searches inINFORMATION_SCHEMA string columns can be affected by file system case sensitivity. For more information, seeSection 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.

TheSELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the variousSHOW statements that MySQL supports (SHOW DATABASES,SHOW TABLES, and so forth). UsingSELECT has these advantages, compared toSHOW:

BecauseSHOW is familiar and widely used, theSHOW statements remain as an alternative. In fact, along with the implementation ofINFORMATION_SCHEMA, there are enhancements toSHOW as described inSection 28.8, “Extensions to SHOW Statements”.

For mostINFORMATION_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, theROUTINE_DEFINITION column in theINFORMATION_SCHEMAROUTINES table), users who have insufficient privileges seeNULL. 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 withINNODB_) require thePROCESS privilege.

The same privileges apply to selecting information fromINFORMATION_SCHEMA and viewing the same information throughSHOW 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 useEXPLAIN. For information about usingEXPLAIN output to tuneINFORMATION_SCHEMA queries, seeSection 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

The implementation for theINFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11Schemata. Our intent is approximate compliance with SQL:2003 core feature F021Basic 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 theENGINE column in theINFORMATION_SCHEMATABLES table.

Although other DBMSs use a variety of names, likesyscat orsystem, the standard name isINFORMATION_SCHEMA.

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 changedCOLLATION toTABLE_COLLATION in theTABLES 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 inINFORMATION_SCHEMA. For each column, there are three pieces of information:

Many sections indicate whatSHOW statement is equivalent to aSELECT that retrieves information fromINFORMATION_SCHEMA. ForSHOW statements that display information for the default database if you omit aFROMdb_name clause, you can often select information for the default database by adding anAND TABLE_SCHEMA = SCHEMA() condition to theWHERE clause of a query that retrieves information from anINFORMATION_SCHEMA table.

These sections discuss additionalINFORMATION_SCHEMA-related topics: