Pre-General Availability Draft: 2017-07-21

24.4 The INFORMATION_SCHEMA COLUMNS Table

The COLUMNS table provides information about columns in tables.

The related ST_GEOMETRY_COLUMNS table is a view on COLUMNS that provides information about table columns that store spatial data. See Section 24.22, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”.

INFORMATION_SCHEMA Name	SHOW Name	Remarks
TABLE_CATALOG		def
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
DATETIME_PRECISION	Type
CHARACTER_SET_NAME
COLLATION_NAME	Collation
COLUMN_TYPE	Type	MySQL extension
COLUMN_KEY	Key	MySQL extension
EXTRA	Extra	MySQL extension
PRIVILEGES	Privileges	MySQL extension
COLUMN_COMMENT	Comment	MySQL extension
GENERATION_EXPRESSION		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 multibyte 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 utf8_swedish_ci, the character set is what is before the first underscore: utf8.

• GENERATION_EXPRESSION is nonempty for generated columns and displays the expression used to compute column values. For information about generated columns, see Section 13.1.15.8, “CREATE TABLE and Generated Columns”.

• The EXTRA column contains VIRTUAL GENERATED or VIRTUAL STORED for generated columns.

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']