Chapter 19. INFORMATION_SCHEMA Tables

Table of Contents

19.1. The INFORMATION_SCHEMA SCHEMATA Table
19.2. The INFORMATION_SCHEMA TABLES Table
19.3. The INFORMATION_SCHEMA COLUMNS Table
19.4. The INFORMATION_SCHEMA STATISTICS Table
19.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table
19.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
19.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
19.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
19.9. The INFORMATION_SCHEMA CHARACTER_SETS Table
19.10. The INFORMATION_SCHEMA COLLATIONS Table
19.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
19.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
19.14. The INFORMATION_SCHEMA ROUTINES Table
19.15. The INFORMATION_SCHEMA VIEWS Table
19.16. The INFORMATION_SCHEMA TRIGGERS Table
19.17. The INFORMATION_SCHEMA PROFILING Table
19.18. Other INFORMATION_SCHEMA Tables
19.19. 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. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL.

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:

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 19.19, “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:

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://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5.

The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8 where N is at least 64. MySQL uses the default collation for this character set (utf8_general_ci) for all searches, sorts, comparisons, and other string operations on such columns. If the default collation is not correct for your needs, you can force a suitable collation with a COLLATE clause (Section 9.1.6.1, “Using COLLATE in SQL Statements”).

Each section indicates what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA, if there is such a statement. For SHOW statements that display information for the current database if you omit a FROM db_name clause, you can often select information for the current database by adding an AND TABLE_SCHEMA = CURRENT_DATABASE() condition to the WHERE clause of a query that retrieves information from an INFORMATION_SCHEMA table.

Note

At present, there are some missing columns and some columns out of order. We are working on this and updating the documentation as changes are made.

For answers to questions that are often asked concerning the INFORMATION_SCHEMA database, see Section A.7, “MySQL 5.0 FAQ — INFORMATION_SCHEMA.

19.1. The INFORMATION_SCHEMA SCHEMATA Table

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

INFORMATION_SCHEMA NameSHOW NameRemarks
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']

19.2. The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG NULL
TABLE_SCHEMATable_... 
TABLE_NAMETable_... 
TABLE_TYPE  
ENGINEEngineMySQL extension
VERSIONVersionThe version number of the table's .frm file, MySQL extension
ROW_FORMATRow_formatMySQL extension
TABLE_ROWSRowsMySQL extension
AVG_ROW_LENGTHAvg_row_lengthMySQL extension
DATA_LENGTHData_lengthMySQL extension
MAX_DATA_LENGTHMax_data_lengthMySQL extension
INDEX_LENGTHIndex_lengthMySQL extension
DATA_FREEData_freeMySQL extension
AUTO_INCREMENTAuto_incrementMySQL extension
CREATE_TIMECreate_timeMySQL extension
UPDATE_TIMEUpdate_timeMySQL extension
CHECK_TIMECheck_timeMySQL extension
TABLE_COLLATIONCollationMySQL extension
CHECKSUMChecksumMySQL extension
CREATE_OPTIONSCreate_optionsMySQL extension
TABLE_COMMENTCommentMySQL 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. Currently, the TABLES table does not list TEMPORARY tables.

  • 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'
  [AND table_name LIKE 'wild']

SHOW TABLES
  FROM db_name
  [LIKE 'wild']

19.3. The INFORMATION_SCHEMA COLUMNS Table

The COLUMNS table provides information about columns in tables.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG NULL
TABLE_SCHEMA  
TABLE_NAME  
COLUMN_NAMEField 
ORDINAL_POSITION see notes
COLUMN_DEFAULTDefault 
IS_NULLABLENull 
DATA_TYPEType 
CHARACTER_MAXIMUM_LENGTHType 
CHARACTER_OCTET_LENGTH  
NUMERIC_PRECISIONType 
NUMERIC_SCALEType 
CHARACTER_SET_NAME  
COLLATION_NAMECollation 
COLUMN_TYPETypeMySQL extension
COLUMN_KEYKeyMySQL extension
EXTRAExtraMySQL extension
PRIVILEGESPrivilegesMySQL extension
COLUMN_COMMENTCommentMySQL 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 is 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']

19.4. The INFORMATION_SCHEMA STATISTICS Table

The STATISTICS table provides information about table indexes.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG NULL
TABLE_SCHEMA = Database
TABLE_NAMETable 
NON_UNIQUENon_unique 
INDEX_SCHEMA = Database
INDEX_NAMEKey_name 
SEQ_IN_INDEXSeq_in_index 
COLUMN_NAMEColumn_name 
COLLATIONCollation 
CARDINALITYCardinality 
SUB_PARTSub_partMySQL extension
PACKEDPackedMySQL extension
NULLABLENullMySQL extension
INDEX_TYPEIndex_typeMySQL extension
COMMENTCommentMySQL 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

19.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 NameSHOW NameRemarks
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 nonstandard table. It takes its values from the mysql.user table.

19.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 NameSHOW NameRemarks
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 nonstandard table. It takes its values from the mysql.db table.

19.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 NameSHOW NameRemarks
GRANTEE 'user_name'@'host_name' value
TABLE_CATALOG NULL
TABLE_SCHEMA  
TABLE_NAME  
PRIVILEGE_TYPE  
IS_GRANTABLE  

Notes:

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

SHOW GRANTS ...

19.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 NameSHOW NameRemarks
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 ...

19.9. The INFORMATION_SCHEMA CHARACTER_SETS Table

The CHARACTER_SETS table provides information about available character sets.

INFORMATION_SCHEMA NameSHOW NameRemarks
CHARACTER_SET_NAMECharset 
DEFAULT_COLLATE_NAMEDefault collation 
DESCRIPIONDescriptionMySQL extension
MAXLENMaxlenMySQL extension

The following statements are equivalent:

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

SHOW CHARACTER SET
  [LIKE 'wild']

19.10. The INFORMATION_SCHEMA COLLATIONS Table

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

INFORMATION_SCHEMA NameSHOW NameRemarks
COLLATION_NAMECollation 
CHARACTER_SET_NAMECharsetMySQL extension
IDIdMySQL extension
IS_DEFAULTDefaultMySQL extension
IS_COMPILEDCompiledMySQL extension
SORTLENSortlenMySQL extension

The following statements are equivalent:

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

SHOW COLLATION
  [LIKE 'wild']

19.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 NameSHOW NameRemarks
COLLATION_NAMECollation 
CHARACTER_SET_NAMECharset 

19.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

The TABLE_CONSTRAINTS table describes which tables have constraints.

INFORMATION_SCHEMA NameSHOW NameRemarks
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.

19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

The KEY_COLUMN_USAGE table describes which key columns have constraints.

INFORMATION_SCHEMA NameSHOW NameRemarks
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.

19.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 Namemysql.proc NameRemarks
SPECIFIC_NAMEspecific_name 
ROUTINE_CATALOG NULL
ROUTINE_SCHEMAdb 
ROUTINE_NAMEname 
ROUTINE_TYPEtype{PROCEDURE|FUNCTION}
DTD_IDENTIFIER data type descriptor
ROUTINE_BODY SQL
ROUTINE_DEFINITIONbody 
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGElanguageNULL
PARAMETER_STYLE SQL
IS_DETERMINISTICis_deterministic 
SQL_DATA_ACCESSsql_data_access 
SQL_PATH NULL
SECURITY_TYPEsecurity_type 
CREATEDcreated 
LAST_ALTEREDmodified 
SQL_MODEsql_modeMySQL extension
ROUTINE_COMMENTcommentMySQL extension
DEFINERdefinerMySQL 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.

19.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 NameSHOW NameRemarks
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 has a value of NONE, CASCADE, or LOCAL.

  • MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the VIEWS table displays the status of this flag. It means that the server always knows whether a view is updatable. If the view is not updatable, statements such UPDATE, DELETE, and INSERT are illegal and will be rejected. (Note that even if a view is updatable, it might not be possible to insert into it; for details, refer to Section 12.1.12, “CREATE VIEW Syntax”.)

  • 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.

MySQL lets you use different sql_mode settings to tell the server the type of SQL syntax to support. For example, you might use the ANSI SQL mode to ensure MySQL correctly interprets the standard SQL concatenation operator, the double bar (||), in your queries. If you then create a view that concatenates items, you might worry that changing the sql_mode setting to a value different from ANSI could cause the view to become invalid. But this is not the case. No matter how you write out a view definition, MySQL always stores it the same way, in a canonical form. Here is an example that shows how the server changes a double bar concatenation operator to a CONCAT() function:

mysql> SET sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
    -> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
+----------------------------------+
| VIEW_DEFINITION                  |
+----------------------------------+
| select concat('a','b') AS `col1` |
+----------------------------------+
1 row in set (0.00 sec)

The advantage of storing a view definition in canonical form is that changes made later to the value of sql_mode will not affect the results from the view. However an additional consequence is that comments prior to SELECT are stripped from the definition by the server.

19.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 NameSHOW NameRemarks
TRIGGER_CATALOG NULL
TRIGGER_SCHEMA  
TRIGGER_NAMETrigger 
EVENT_MANIPULATIONEvent 
EVENT_OBJECT_CATALOG NULL
EVENT_OBJECT_SCHEMA  
EVENT_OBJECT_TABLETable 
ACTION_ORDER 0
ACTION_CONDITION NULL
ACTION_STATEMENTStatement 
ACTION_ORIENTATION ROW
ACTION_TIMINGTiming 
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 Section 18.3, “Using 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.1.7, “Server SQL Modes”.

  • 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 12.5.5.35, “SHOW TRIGGERS Syntax”.

19.17. The INFORMATION_SCHEMA PROFILING Table

This section does not apply to MySQL Enterprise Server users.

The PROFILING table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILES and SHOW PROFILE statements (see Section 12.5.5.29, “SHOW PROFILES Syntax”). The table is empty unless the profiling session variable is set to 1.

INFORMATION_SCHEMA NameSHOW NameRemarks
QUERY_IDQuery_ID 
SEQ 
STATEStatus 
DURATIONDuration 
CPU_USERCPU_user 
CPU_SYSTEMCPU_system 
CONTEXT_VOLUNTARYContext_voluntary 
CONTEXT_INVOLUNTARYContext_involuntary 
BLOCK_OPS_INBlock_ops_in 
BLOCK_OPS_OUTBlock_ops_out 
MESSAGES_SENTMessages_sent 
MESSAGES_RECEIVEDMessages_received 
PAGE_FAULTS_MAJORPage_faults_major 
PAGE_FAULTS_MINORPage_faults_minor 
SWAPSSwaps 
SOURCE_FUNCTIONSource_function 
SOURCE_FILESource_file 
SOURCE_LINESource_line 

Notes:

  • The PROFILING table was added in MySQL 5.0.37.

  • QUERY_ID is a numeric statement identifier.

  • SEQ is a sequence number indicating the display order for rows with the same QUERY_ID value.

  • STATE is the profiling state to which the row measurements apply.

  • DURATION indicates how long statement execution remained in the given state, in seconds.

  • CPU_USER and CPU_SYSTEM indicate user and system CPU use, in seconds.

  • CONTEXT_VOLUNTARY and CONTEXT_INVOLUNTARY indicate how many voluntary and involuntary context switches occurred.

  • BLOCK_OPS_IN and BLOCK_OPS_OUT indicate the number of block input and output operations.

  • MESSAGES_SENT and MESSAGES_RECEIVED indicate the number of communication messages sent and received.

  • PAGE_FAULTS_MAJOR and PAGE_FAULTS_MINOR indicate the number of major and minor page faults.

  • SWAPS indicates how many swaps occurred.

  • SOURCE_FUNCTION, SOURCE_FILE, and SOURCE_LINE provide information indicating where in the source code the profiled state executes.

19.18. 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. (REFERENTIAL_CONSTRAINTS is implemented in MySQL 5.1, and PARAMETERS is implemented in MySQL 6.0.)

19.19. 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.

SHOW statements that accept a LIKE clause to limit the rows displayed have been extended to allow a WHERE clause that enables specification of more general conditions that selected rows must satisfy:

SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW INDEX
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 |
+---------+---------------------------+---------------------+--------+