20.4. The `INFORMATION_SCHEMA COLUMNS` Table

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`
`DATETIME_PRECISION`	`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, 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.
DATETIME_PRECISION was added in MySQL 5.6.4.

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']

20.4. The INFORMATION_SCHEMA COLUMNS Table

20.4. The `INFORMATION_SCHEMA COLUMNS` Table