DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) information-schema

Info Catalog (mysql.info) views (mysql.info) Top (mysql.info) precision-math
 
 20 The `INFORMATION_SCHEMA' Database
 ************************************
 

Menu

 
* schemata-table               The `INFORMATION_SCHEMA SCHEMATA' Table
* tables-table                 The `INFORMATION_SCHEMA TABLES' Table
* columns-table                The `INFORMATION_SCHEMA COLUMNS' Table
* statistics-table             The `INFORMATION_SCHEMA STATISTICS' Table
* user-privileges-table        The `INFORMATION_SCHEMA USER_PRIVILEGES' Table
* schema-privileges-table      The `INFORMATION_SCHEMA SCHEMA_PRIVILEGES' Table
* table-privileges-table       The `INFORMATION_SCHEMA TABLE_PRIVILEGES' Table
* column-privileges-table      The `INFORMATION_SCHEMA COLUMN_PRIVILEGES' Table
* character-sets-table         The `INFORMATION_SCHEMA CHARACTER_SETS' Table
* collations-table             The `INFORMATION_SCHEMA COLLATIONS' Table
* collation-character-set-applicability-table  The `INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY' Table
* table-constraints-table      The `INFORMATION_SCHEMA TABLE_CONSTRAINTS' Table
* key-column-usage-table       The `INFORMATION_SCHEMA KEY_COLUMN_USAGE' Table
* routines-table               The `INFORMATION_SCHEMA ROUTINES' Table
* views-table                  The `INFORMATION_SCHEMA VIEWS' Table
* triggers-table               The `INFORMATION_SCHEMA TRIGGERS' Table
* other-information-schema-tables  Other `INFORMATION_SCHEMA' Tables
* extended-show                Extensions to `SHOW' Statements
 
 `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 
 extended-show.
 
 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: `http://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.
 
 * 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.
 
Info Catalog (mysql.info) views (mysql.info) Top (mysql.info) precision-math
automatically generated byinfo2html