Chapter 20. The `INFORMATION_SCHEMA` Database
Prev |  | Next

## Chapter 20. The `INFORMATION_SCHEMA` Database

`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

See also Section 13.5.4.23, “`SHOW TRIGGERS` Syntax”.

## 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