24.5 The INFORMATION_SCHEMA COLUMNS Table

The COLUMNS table provides information about columns in tables.

The COLUMNS table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the table containing the column belongs. This value is always def.

  • TABLE_SCHEMA

    The name of the schema (database) to which the table containing the column belongs.

  • TABLE_NAME

    The name of the table containing the column.

  • COLUMN_NAME

    The name of the column.

  • ORDINAL_POSITION

    The position of the column within the table. ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW COLUMNS, SELECT from the COLUMNS table does not have automatic ordering.

  • COLUMN_DEFAULT

    The default value for the column. This is NULL if the column has an explicit default of NULL, or if the column definition includes no DEFAULT clause.

  • IS_NULLABLE

    The column nullability. The value is YES if NULL values can be stored in the column, NO if not.

  • DATA_TYPE

    The column data type.

    The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.

  • CHARACTER_MAXIMUM_LENGTH

    For string columns, the maximum length in characters.

  • CHARACTER_OCTET_LENGTH

    For string columns, the maximum length in bytes.

  • NUMERIC_PRECISION

    For numeric columns, the numeric precision.

  • NUMERIC_SCALE

    For numeric columns, the numeric scale.

  • DATETIME_PRECISION

    For temporal columns, the fractional seconds precision.

  • CHARACTER_SET_NAME

    For character string columns, the character set name.

  • COLLATION_NAME

    For character string columns, the collation name.

  • COLUMN_TYPE

    The column data type.

    The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.

  • COLUMN_KEY

    Whether the column is indexed:

    • If COLUMN_KEY is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.

    • If COLUMN_KEY is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

    • If COLUMN_KEY is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null column.)

    • If COLUMN_KEY is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.

    If more than one of the COLUMN_KEY values applies to a given column of a table, COLUMN_KEY displays the one with the highest priority, in the order PRI, UNI, MUL.

    A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

  • EXTRA

    Any additional information that is available about a given column. The value is nonempty in these cases:

    • auto_increment for columns that have the AUTO_INCREMENT attribute.

    • on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.

    • STORED GENERATED or VIRTUAL GENERATED for generated columns.

  • PRIVILEGES

    The privileges you have for the column.

  • COLUMN_COMMENT

    Any comment included in the column definition.

  • GENERATION_EXPRESSION

    For generated columns, displays the expression used to compute column values. Empty for nongenerated columns. For information about generated columns, see Section 13.1.18.7, “CREATE TABLE and Generated Columns”.

Notes

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

  • CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multibyte character sets.

  • CHARACTER_SET_NAME can be derived from COLLATION_NAME. For example, if you say SHOW FULL COLUMNS FROM t, and you see in the COLLATION_NAME column a value of latin1_swedish_ci, the character set is what is before the first underscore: latin1.

Column information is also available from the SHOW COLUMNS statement. See Section 13.7.5.5, “SHOW COLUMNS Statement”. 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']