Chapter 20. The INFORMATION

Chapter 20. The `INFORMATION_SCHEMA` Database
Prev		Next

Chapter 20. The `INFORMATION_SCHEMA` Database

Table of Contents

20.1. The INFORMATION_SCHEMA SCHEMATA Table
20.2. The INFORMATION_SCHEMA TABLES Table
20.3. The INFORMATION_SCHEMA COLUMNS Table
20.4. The INFORMATION_SCHEMA STATISTICS Table
20.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table
20.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
20.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
20.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
20.9. The INFORMATION_SCHEMA CHARACTER_SETS Table
20.10. The INFORMATION_SCHEMA COLLATIONS Table
20.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
20.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
20.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
20.14. The INFORMATION_SCHEMA ROUTINES Table
20.15. The INFORMATION_SCHEMA VIEWS Table
20.16. The INFORMATION_SCHEMA TRIGGERS Table
20.17. Other INFORMATION_SCHEMA Tables
20.18. 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 Section 20.18, “Extensions to SHOW Statements”.

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.

Note: 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.

20.1. The `INFORMATION_SCHEMA SCHEMATA` Table

A schema is a database, so the SCHEMATA table provides information about databases.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`CATALOG_NAME`		`NULL`
`SCHEMA_NAME`		Database
`DEFAULT_CHARACTER_SET_NAME`
`DEFAULT_COLLATION_NAME`
`SQL_PATH`		`NULL`

Notes:

DEFAULT_COLLATION_NAME was added in MySQL 5.0.6.

The following statements are equivalent:

SELECT SCHEMA_NAME AS `Database`
  FROM INFORMATION_SCHEMA.SCHEMATA
  [WHERE SCHEMA_NAME LIKE 'wild']

SHOW DATABASES
  [LIKE 'wild']

20.2. The `INFORMATION_SCHEMA TABLES` Table

The TABLES table provides information about tables in databases.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`	`Table_`...
`TABLE_NAME`	`Table_`...
`TABLE_TYPE`
`ENGINE`	`Engine`	MySQL extension
`VERSION`	`Version`	MySQL extension
`ROW_FORMAT`	`Row_format`	MySQL extension
`TABLE_ROWS`	`Rows`	MySQL extension
`AVG_ROW_LENGTH`	`Avg_row_length`	MySQL extension
`DATA_LENGTH`	`Data_length`	MySQL extension
`MAX_DATA_LENGTH`	`Max_data_length`	MySQL extension
`INDEX_LENGTH`	`Index_length`	MySQL extension
`DATA_FREE`	`Data_free`	MySQL extension
`AUTO_INCREMENT`	`Auto_increment`	MySQL extension
`CREATE_TIME`	`Create_time`	MySQL extension
`UPDATE_TIME`	`Update_time`	MySQL extension
`CHECK_TIME`	`Check_time`	MySQL extension
`TABLE_COLLATION`	`Collation`	MySQL extension
`CHECKSUM`	`Checksum`	MySQL extension
`CREATE_OPTIONS`	`Create_options`	MySQL extension
`TABLE_COMMENT`	`Comment`	MySQL extension

Notes:

TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.
TABLE_TYPE should be BASE TABLE or VIEW. If table is temporary, then TABLE_TYPE = TEMPORARY. (There are no temporary views, so this is not ambiguous.)
The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database. For InnoDB tables, the row count is only a rough estimate used in SQL optimization.
We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

The following statements are equivalent:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  [WHERE table_schema = 'db_name']
  [WHERE|AND table_name LIKE 'wild']

SHOW TABLES
  [FROM db_name]
  [LIKE 'wild']

20.3. The `INFORMATION_SCHEMA COLUMNS` Table

The COLUMNS table provides information about columns in tables.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`COLUMN_NAME`	`Field`
`ORDINAL_POSITION`		see notes
`COLUMN_DEFAULT`	`Default`
`IS_NULLABLE`	`Null`
`DATA_TYPE`	`Type`
`CHARACTER_MAXIMUM_LENGTH`	`Type`
`CHARACTER_OCTET_LENGTH`
`NUMERIC_PRECISION`	`Type`
`NUMERIC_SCALE`	`Type`
`CHARACTER_SET_NAME`
`COLLATION_NAME`	`Collation`
`COLUMN_TYPE`	`Type`	MySQL extension
`COLUMN_KEY`	`Key`	MySQL extension
`EXTRA`	`Extra`	MySQL extension
`COLUMN_COMMENT`	`Comment`	MySQL extension

Notes:

In SHOW, the Type display includes values from several different COLUMNS columns.
ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW, SELECT does not have automatic ordering.
CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multi-byte character sets.
CHARACTER_SET_NAME can be derived from Collation. For example, if you say SHOW FULL COLUMNS FROM t, and you see in the Collation column a value of latin1_swedish_ci, the character set is what's before the first underscore: latin1.

The following statements are nearly equivalent:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE 'wild']

20.4. The `INFORMATION_SCHEMA STATISTICS` Table

The STATISTICS table provides information about table indexes.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`		= Database
`TABLE_NAME`	`Table`
`NON_UNIQUE`	`Non_unique`
`INDEX_SCHEMA`		= Database
`INDEX_NAME`	`Key_name`
`SEQ_IN_INDEX`	`Seq_in_index`
`COLUMN_NAME`	`Column_name`
`COLLATION`	`Collation`
`CARDINALITY`	`Cardinality`
`SUB_PART`	`Sub_part`	MySQL extension
`PACKED`	`Packed`	MySQL extension
`NULLABLE`	`Null`	MySQL extension
`INDEX_TYPE`	`Index_type`	MySQL extension
`COMMENT`	`Comment`	MySQL extension

Notes:

There is no standard table for indexes. The preceding list is similar to what SQL Server 2000 returns for sp_statistics, except that we replaced the name QUALIFIER with CATALOG and we replaced the name OWNER with SCHEMA.
Clearly, the preceding table and the output from SHOW INDEX are derived from the same parent. So the correlation is already close.

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']

SHOW INDEX
  FROM tbl_name
  [FROM db_name]

20.5. The `INFORMATION_SCHEMA USER_PRIVILEGES` Table

The USER_PRIVILEGES table provides information about global privileges. This information comes from the mysql.user grant table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`GRANTEE`		`'user_name'@'host_name'` value, MySQL extension
`TABLE_CATALOG`		`NULL`, MySQL extension
`PRIVILEGE_TYPE`		MySQL extension
`IS_GRANTABLE`		MySQL extension

Notes:

This is a non-standard table. It takes its values from the mysql.user table.

20.6. The `INFORMATION_SCHEMA SCHEMA_PRIVILEGES` Table

The SCHEMA_PRIVILEGES table provides information about schema (database) privileges. This information comes from the mysql.db grant table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`GRANTEE`		`'user_name'@'host_name'` value, MySQL extension
`TABLE_CATALOG`		`NULL`, MySQL extension
`TABLE_SCHEMA`		MySQL extension
`PRIVILEGE_TYPE`		MySQL extension
`IS_GRANTABLE`		MySQL extension

Notes:

This is a non-standard table. It takes its values from the mysql.db table.

20.7. The `INFORMATION_SCHEMA TABLE_PRIVILEGES` Table

The TABLE_PRIVILEGES table provides information about table privileges. This information comes from the mysql.tables_priv grant table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`GRANTEE`		`'user_name'@'host_name'` value
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`PRIVILEGE_TYPE`
`IS_GRANTABLE`

Notes:

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES, ALTER, INDEX, DROP, CREATE VIEW.

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

SHOW GRANTS ...

20.8. The `INFORMATION_SCHEMA COLUMN_PRIVILEGES` Table

The COLUMN_PRIVILEGES table provides information about column privileges. This information comes from the mysql.columns_priv grant table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`GRANTEE`		`'user_name'@'host_name'` value
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`COLUMN_NAME`
`PRIVILEGE_TYPE`
`IS_GRANTABLE`

Notes:

In the output from SHOW FULL COLUMNS, the privileges are all in one field and in lowercase, for example, select,insert,update,references. In COLUMN_PRIVILEGES, there is one privilege per row, in uppercase.
PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.
If the user has GRANT OPTION privilege, IS_GRANTABLE should be YES. Otherwise, IS_GRANTABLE should be NO. The output does not list GRANT OPTION as a separate privilege.

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

SHOW GRANTS ...

20.9. The `INFORMATION_SCHEMA CHARACTER_SETS` Table

The CHARACTER_SETS table provides information about available character sets.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`CHARACTER_SET_NAME`	`Charset`
`DEFAULT_COLLATE_NAME`	`Default collation`
`DESCRIPION`	`Description`	MySQL extension
`MAXLEN`	`Maxlen`	MySQL extension

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
  [WHERE name LIKE 'wild']

SHOW CHARACTER SET
  [LIKE 'wild']

20.10. The `INFORMATION_SCHEMA COLLATIONS` Table

The COLLATIONS table provides information about collations for each character set.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`COLLATION_NAME`	`Collation`
`CHARACTER_SET_NAME`	`Charset`	MySQL extension
`ID`	`Id`	MySQL extension
`IS_DEFAULT`	`Default`	MySQL extension
`IS_COMPILED`	`Compiled`	MySQL extension
`SORTLEN`	`Sortlen`	MySQL extension

The following statements are equivalent:

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
  [WHERE collation_name LIKE 'wild']

SHOW COLLATION
  [LIKE 'wild']

20.11. The `INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY` Table

The COLLATION_CHARACTER_SET_APPLICABILITY table indicates what character set is applicable for what collation. The columns are equivalent to the first two display fields that we get from SHOW COLLATION.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`COLLATION_NAME`	`Collation`
`CHARACTER_SET_NAME`	`Charset`

20.12. The `INFORMATION_SCHEMA TABLE_CONSTRAINTS` Table

The TABLE_CONSTRAINTS table describes which tables have constraints.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`CONSTRAINT_CATALOG`		`NULL`
`CONSTRAINT_SCHEMA`
`CONSTRAINT_NAME`
`TABLE_SCHEMA`
`TABLE_NAME`
`CONSTRAINT_TYPE`

Notes:

The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.
The UNIQUE and PRIMARY KEY information is about the same as what you get from the Key_name field in the output from SHOW INDEX when the Non_unique field is 0.
The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. This is a CHAR (not ENUM) column. The CHECK value is not available until we support CHECK.

20.13. The `INFORMATION_SCHEMA KEY_COLUMN_USAGE` Table

The KEY_COLUMN_USAGE table describes which key columns have constraints.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`CONSTRAINT_CATALOG`		`NULL`
`CONSTRAINT_SCHEMA`
`CONSTRAINT_NAME`
`TABLE_CATALOG`
`TABLE_SCHEMA`
`TABLE_NAME`
`COLUMN_NAME`
`ORDINAL_POSITION`
`POSITION_IN_UNIQUE_CONSTRAINT`
`REFERENCED_TABLE_SCHEMA`
`REFERENCED_TABLE_NAME`
`REFERENCED_COLUMN_NAME`

Notes:

If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.
The value of ORDINAL_POSITION is the column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.
The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced.
For example, suppose that there are two tables name t1 and t3 that have the following definitions:
```
CREATE TABLE t1
(
    s1 INT,
    s2 INT,
    s3 INT,
    PRIMARY KEY(s3)
) ENGINE=InnoDB;

CREATE TABLE t3
(
    s1 INT,
    s2 INT,
    s3 INT,
    KEY(s1),
    CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;
```
For those two tables, the KEY_COLUMN_USAGE table has two rows:
- One row with CONSTRAINT_NAME = 'PRIMARY', TABLE_NAME = 't1', COLUMN_NAME = 's3', ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = NULL.
- One row with CONSTRAINT_NAME = 'CO', TABLE_NAME = 't3', COLUMN_NAME = 's2', ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1.
REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, and REFERENCED_COLUMN_NAME were added in MySQL 5.0.6.

20.14. The `INFORMATION_SCHEMA ROUTINES` Table

The ROUTINES table provides information about stored routines (both procedures and functions). The ROUTINES table does not include user-defined functions (UDFs) at this time.

The column named “mysql.proc name” indicates the mysql.proc table column that corresponds to the INFORMATION_SCHEMA.ROUTINES table column, if any.

`INFORMATION_SCHEMA` Name	`mysql.proc` Name	Remarks
`SPECIFIC_NAME`	`specific_name`
`ROUTINE_CATALOG`		`NULL`
`ROUTINE_SCHEMA`	`db`
`ROUTINE_NAME`	`name`
`ROUTINE_TYPE`	`type`	`{PROCEDURE\|FUNCTION}`
`DTD_IDENTIFIER`		(data type descriptor)
`ROUTINE_BODY`		`SQL`
`ROUTINE_DEFINITION`	`body`
`EXTERNAL_NAME`		`NULL`
`EXTERNAL_LANGUAGE`	`language`	`NULL`
`PARAMETER_STYLE`		`SQL`
`IS_DETERMINISTIC`	`is_deterministic`
`SQL_DATA_ACCESS`	`sql_data_access`
`SQL_PATH`		`NULL`
`SECURITY_TYPE`	`security_type`
`CREATED`	`created`
`LAST_ALTERED`	`modified`
`SQL_MODE`	`sql_mode`	MySQL extension
`ROUTINE_COMMENT`	`comment`	MySQL extension
`DEFINER`	`definer`	MySQL extension

Notes:

MySQL calculates EXTERNAL_LANGUAGE thus:
- If mysql.proc.language='SQL', EXTERNAL_LANGUAGE is NULL
- Otherwise, EXTERNAL_LANGUAGE is what is in mysql.proc.language. However, we do not have external languages yet, so it is always NULL.

20.15. The `INFORMATION_SCHEMA VIEWS` Table

The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`VIEW_DEFINITION`
`CHECK_OPTION`
`IS_UPDATABLE`
`DEFINER`
`SECURITY_TYPE`

Notes:

The VIEW_DEFINITION column has most of what you see in the Create Table field that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION. Suppose that the original statement was:
```
CREATE VIEW v AS
  SELECT s2,s1 FROM t
  WHERE s1 > 5
  ORDER BY s1
  WITH CHECK OPTION;
```
Then the view definition looks like this:
```
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
```
The CHECK_OPTION column always has a value of NONE.
The IS_UPDATABLE column is YES if the view is updatable, NO if the view is not updatable.
The DEFINER and SECURITY_TYPE columns were added in MySQL 5.0.14. DEFINER indicates who defined the view. SECURITY_TYPE has a value of DEFINER or INVOKER.

20.16. The `INFORMATION_SCHEMA TRIGGERS` Table

The TRIGGERS table provides information about triggers. You must have the SUPER privilege to access this table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TRIGGER_CATALOG`		`NULL`
`TRIGGER_SCHEMA`
`TRIGGER_NAME`	`Trigger`
`EVENT_MANIPULATION`	`Event`
`EVENT_OBJECT_CATALOG`		`NULL`
`EVENT_OBJECT_SCHEMA`
`EVENT_OBJECT_TABLE`	`Table`
`ACTION_ORDER`		`0`
`ACTION_CONDITION`		`NULL`
`ACTION_STATEMENT`	`Statement`
`ACTION_ORIENTATION`		`ROW`
`ACTION_TIMING`	`Timing`
`ACTION_REFERENCE_OLD_TABLE`		`NULL`
`ACTION_REFERENCE_NEW_TABLE`		`NULL`
`ACTION_REFERENCE_OLD_ROW`		`OLD`
`ACTION_REFERENCE_NEW_ROW`		`NEW`
`CREATED`		`NULL` (`0`)
`SQL_MODE`		MySQL extension
`DEFINER`		MySQL extension

Notes:

The TRIGGERS table was added in MySQL 5.0.10.
The TRIGGER_SCHEMA and TRIGGER_NAME columns contain the name of the database in which the trigger occurs and the trigger name, respectively.
The EVENT_MANIPULATION column contains one of the values 'INSERT', 'DELETE', or 'UPDATE'.
As noted in Chapter 18, Triggers, every trigger is associated with exactly one table. The EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE columns contain the database in which this table occurs, and the table's name.
The ACTION_ORDER statement contains the ordinal position of the trigger's action within the list of all similar triggers on the same table. Currently, this value is always 0, because it is not possible to have more than one trigger with the same EVENT_MANIPULATION and ACTION_TIMING on the same table.
The ACTION_STATEMENT column contains the statement to be executed when the trigger is invoked. This is the same as the text displayed in the Statement column of the output from SHOW TRIGGERS. Note that this text uses UTF-8 encoding.
The ACTION_ORIENTATION column always contains the value 'ROW'.
The ACTION_TIMING column contains one of the two values 'BEFORE' or 'AFTER'.
The columns ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW contain the old and new column identifiers, respectively. This means that ACTION_REFERENCE_OLD_ROW always contains the value 'OLD' and ACTION_REFERENCE_NEW_ROW always contains the value 'NEW'.
The SQL_MODE column shows the server SQL mode that was in effect at the time when the trigger was created (and thus which remains in effect for this trigger whenever it is invoked, regardless of the current server SQL mode). The possible range of values for this column is the same as that of the sql_mode system variable. See Section 5.2.5, “The Server SQL Mode”.
The DEFINER column was added in MySQL 5.0.17. DEFINER indicates who defined the trigger.
The following columns currently always contain NULL: TRIGGER_CATALOG, EVENT_OBJECT_CATALOG, ACTION_CONDITION, ACTION_REFERENCE_OLD_TABLE, ACTION_REFERENCE_NEW_TABLE, and CREATED.

Example, using the ins_sum trigger defined in Section 18.3, “Using Triggers”:

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: NULL
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ins_sum
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: NULL
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: account
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SET @sum = @sum + NEW.amount
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE:
                   DEFINER: me@localhost

20.17. Other `INFORMATION_SCHEMA` Tables

We intend to implement additional INFORMATION_SCHEMA tables. In particular, we acknowledge the need for the PARAMETERS and REFERENTIAL_CONSTRAINTS tables.

20.18. Extensions to `SHOW` Statements

Some extensions to SHOW statements accompany the implementation of INFORMATION_SCHEMA:

SHOW can be used to get information about the structure of INFORMATION_SCHEMA itself.
Several SHOW statements accept a WHERE clause that provides more flexibility in specifying which rows to display.

These extensions are available beginning with MySQL 5.0.3.

INFORMATION_SCHEMA is an information database, so its name is included in the output from SHOW DATABASES. Similarly, SHOW TABLES can be used with INFORMATION_SCHEMA to obtain a list of its tables:

mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
16 rows in set (0.00 sec)

SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables.

Several SHOW statement have been extended to allow a WHERE clause:

SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW KEYS
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES

The WHERE clause, if present, is evaluated against the column names displayed by the SHOW statement. For example, the SHOW CHARACTER SET statement produces these output columns:

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
...

To use a WHERE clause with SHOW CHARACTER SET, you would refer to those column names. As an example, the following statement displays information about character sets for which the default collation contains the string 'japanese':

mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci    |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci   |      2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+

This statement displays the multi-byte character sets:

mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5    | Big5 Traditional Chinese  | big5_chinese_ci     |      2 |
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci    |      2 |
| euckr   | EUC-KR Korean             | euckr_korean_ci     |      2 |
| gb2312  | GB2312 Simplified Chinese | gb2312_chinese_ci   |      2 |
| gbk     | GBK Simplified Chinese    | gbk_chinese_ci      |      2 |
| utf8    | UTF-8 Unicode             | utf8_general_ci     |      3 |
| ucs2    | UCS-2 Unicode             | ucs2_general_ci     |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci   |      2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+

Prev		Next
Chapter 19. Views	Home	Chapter 21. Precision Math