Working with Metadata - MySQL - page 358

-> AND COLUMN_NAME = 'colors'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: cookbook
TABLE_NAME: item
COLUMN_NAME: colors
ORDINAL_POSITION: 3
COLUMN_DEFAULT: puce
IS_NULLABLE: YES
DATA_TYPE: enum
CHARACTER_MAXIMUM_LENGTH: 10
CHARACTER_OCTET_LENGTH: 10
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: latin1
COLLATION_NAME: latin1_swedish_ci
COLUMN_TYPE: enum('chartreuse','mauve','lime green','puce')
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
To obtain information about all columns, omit the COLUMN_NAME condition from the
WHERE clause.
To retrieve only certain types of information, replace SELECT * with the columns of
interest:
mysql> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'item';
+-------------+-----------+-------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+-------------+-----------+-------------+
| id | int | NO |
| name | char | YES |
| colors | enum | YES |
+-------------+-----------+-------------+
Here are some COLUMNS table columns likely to be of most use:
• COLUMN_NAME : The column name.
• ORDINAL_POSITION : The position of the column within the table definition.
• COLUMN_DEFAULT : The column's default value.
• IS_NULLABLE : YES or NO to indicate whether the column can contain NULL values.
• DATA_TYPE , COLUMN_TYPE : Data type information. DATA_TYPE is the data-type key‐
word and COLUMN_TYPE contains additional information such as type attributes.