Note: In MySQL 5.0, the value of the SQL_PATH column is always NULL.

DEFAULT_COLLATION_NAME was added in MySQL 5.0.6.

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.

In SHOW, the Type display includes values from several different COLUMNS columns.

ORDINAL_POSITION is necessary because you might someday 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.

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.

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

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

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 row per privilege, and it's uppercase.

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.

If the user has GRANT OPTION privilege, then IS_GRANTABLE should be YES. Otherwise, IS_GRANTABLE should be NO. The output does not list GRANT OPTION as a separate privilege.

We have added two non-standard columns corresponding to the Description and Maxlen columns in the output from SHOW CHARACTER SET.

We have added five non-standard columns corresponding to the Charset, Id, Default, Compiled, and Sortlen columns in the output from SHOW COLLATION.

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.

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:

REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, and REFERENCED_COLUMN_NAME were added in MySQL 5.0.6.

MySQL calculates EXTERNAL_LANGUAGE thus:

There is a new privilege, SHOW VIEW, without which you cannot see the VIEWS table.

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. For example, if 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 is:

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.

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 19, 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.3.2, “The Server SQL Mode”.

The following columns currently always contain NULL: TRIGGER_CATALOG, EVENT_OBJECT_CATALOG, ACTION_CONDITION, ACTION_REFERENCE_OLD_TABLE, ACTION_REFERENCE_NEW_TABLE, and CREATED.