The COLUMNS
table provides information about
columns in tables.
INFORMATION_SCHEMA
Name
|
SHOW Name
|
Remarks |
TABLE_CATALOG
|
NULL
|
|
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 |
COLUMN_COMMENT
|
Comment
|
MySQL extension |
Notes:
-
In
SHOW
, theType
display includes values from several differentCOLUMNS
columns. -
ORDINAL_POSITION
is necessary because you might want to sayORDER BY ORDINAL_POSITION
. UnlikeSHOW
,SELECT
does not have automatic ordering. -
CHARACTER_OCTET_LENGTH
should be the same asCHARACTER_MAXIMUM_LENGTH
, except for multi-byte character sets. -
CHARACTER_SET_NAME
can be derived fromCollation
. For example, if you saySHOW FULL COLUMNS FROM t
, and you see in theCollation
column a value oflatin1_swedish_ci
, the character set is what's 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
']