The COLUMNS table provides
information about columns in tables.
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 | |
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 |
Notes:
In
SHOW, theTypedisplay includes values from several differentCOLUMNScolumns.ORDINAL_POSITIONis necessary because you might want to sayORDER BY ORDINAL_POSITION. UnlikeSHOW,SELECTdoes not have automatic ordering.CHARACTER_OCTET_LENGTHshould be the same asCHARACTER_MAXIMUM_LENGTH, except for multi-byte character sets.CHARACTER_SET_NAMEcan be derived fromCollation. For example, if you saySHOW FULL COLUMNS FROM t, and you see in theCollationcolumn a value oflatin1_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 FROMtbl_name[FROMdb_name] [LIKE 'wild']