Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.4Mb
PDF (A4) - 33.4Mb
PDF (RPM) - 31.3Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.8Mb
Man Pages (TGZ) - 145.7Kb
Man Pages (Zip) - 206.7Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


MySQL 8.0 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA COLUMNS Table

Pre-General Availability Draft: 2017-09-22

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 NameSHOW NameRemarks
TABLE_CATALOG def
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 
DATETIME_PRECISIONType 
CHARACTER_SET_NAME  
COLLATION_NAMECollation 
COLUMN_TYPETypeMySQL extension
COLUMN_KEYKeyMySQL extension
EXTRAExtraMySQL extension
PRIVILEGESPrivilegesMySQL extension
COLUMN_COMMENTCommentMySQL 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.16.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']

User Comments
  Posted by Gelo Tiglao on April 12, 2007
This is the equivalent syntax for "all_tab_columns" that is used for Oracle

-Gelomon
  Posted by Jens Lærkedal on August 22, 2010
When using select on INFORMATION_SCHEMA.COLUMNS, observe that it is independent of previous USE <db> statements. This means you should take care if you omit the "TABLE_SCHEMA = <db>". Your result could be a mix of data from other DB instances. This may be trivial, but it did fool me.
  Posted by Shailesh Humbad on September 7, 2015
It should be noted that INFORMATION_SCHEMA.COLUMNS does NOT contain information about temporary tables. To get temporary table column information, you must use SHOW COLUMNS. You can also use SHOW CREATE TABLE or in MySQL 5.7.1, INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO.
Sign Up Login You must be logged in to post a comment.