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.24, “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
SRS_ID		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.18.8, “CREATE TABLE and Generated Columns”.

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

• SRS_ID applies to spatial columns. It contains the column SRID value that indicates the the spatial reference system for values stored in the column. See Section 11.5.1, “Spatial Data Types”, and Section 11.5.5, “Spatial Reference System Support”. The value is NULL for nonspatial columns and spatial columns with no SRID attribute. SRS_ID was added in MySQL 8.0-3.

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