23.3. The INFORMATION_SCHEMA COLUMNS Table
The COLUMNS table provides information about
columns in tables.
Notes:
In SHOW, the Type
display includes values from several different
COLUMNS columns.
ORDINAL_POSITION is necessary because you
might someday 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'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
FROM tbl_name
[FROM db_name]
[LIKE 'wild']
