DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) INFORMATION_SCHEMA

Info Catalog (mysql.info.gz) Triggers (mysql.info.gz) Top (mysql.info.gz) MySQL APIs
 
 21 The `INFORMATION_SCHEMA' Information Database
 ************************************************
 
 `INFORMATION_SCHEMA' support is available in MySQL 5.0.2 and later.  It
 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" or
 "system catalog."
 
 Here is an example:
 
      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 engine.
 
 `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 you won't actually
 see any file associated with them.
 
 Each MySQL user has the right to access these tables, but only the rows
 in the tables that correspond to objects for which the user has the
 proper access privileges.
 
 *Advantages of `SELECT'*
 
 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, there are enhancements to `SHOW' in MySQL 5.0, too.  These are
 described in  Extended `SHOW' Extended SHOW.
 
 *Standards*
 
 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) will 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'.
 
 In effect, we have a new "database" named `information_schema', though
 there is never a need to make a file by that name.  It will not be
 possible to say `USE INFORMATION_SCHEMA'.  The only thing you can do
 with these tables is `SELECT'.  You cannot update, insert, delete, or
 even reference them.
 
 *Privileges*
 
 There is no difference between the current (`SHOW') privilege
 requirement and the `SELECT' requirement.  In either case, you have to
 have some privilege on an object in order to see information about it.
 

Menu

 
* INFORMATION_SCHEMA Tables   `INFORMATION_SCHEMA' Tables
* Extended SHOW               Extensions to `SHOW' Statements
 
Info Catalog (mysql.info.gz) Triggers (mysql.info.gz) Top (mysql.info.gz) MySQL APIs
automatically generated byinfo2html