20.3. The INFORMATION_SCHEMA COLUMNS Table

The COLUMNS table provides information about columns in tables.

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 
CHARACTER_SET_NAME  
COLLATION_NAMECollation 
COLUMN_TYPETypeMySQL extension
COLUMN_KEYKeyMySQL extension
EXTRAExtraMySQL extension
PRIVILEGESPrivilegesMySQL extension
COLUMN_COMMENTCommentMySQL 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 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 is before the first underscore: latin1.

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']
Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout