Working with Metadata - MySQL - page 357

Determine whether the column can contain NULL values. If it can, set the column
to NULL ; otherwise, set it to the empty string.
Mapping column definitions onto web page elements
Some data types such as ENUM and SET correspond naturally to elements of web
forms:
• An ENUM has a fixed set of values from which you choose a single value. This is
analogous to a group of radio buttons, a pop-up menu, or a single-pick scrolling
list.
• A SET column is similar, except that you can select multiple values; this corre‐
sponds to a group of checkboxes or a multiple-pick scrolling list.
By using table metadata to access definitions for these types of columns, you can
easily determine a column's legal values and map them onto an appropriate form
element. This enables you to present users with a list of applicable values from which
they can make a selection easily with no typing. Recipe 10.7 discusses how to get
definitions for these types of columns. The methods developed there are used in
Chapter 20 , which discusses form generation in more detail.
MySQL provides several ways to find out about a table's structure:
• Retrieve the information from INFORMATION_SCHEMA . The COLUMNS table contains
the column definitions.
• Use a SHOW COLUMNS statement.
• Use the SHOW CREATE TABLE statement or the mysqldump command-line program
to obtain a CREATE TABLE statement that displays the table's structure.
The following discussion shows how to ask MySQL for table information using each
method. To try the examples, create an item table that lists item IDs, names, and colors
in which each item is available:
CREATE TABLE item
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
name CHAR ( 20 ),
colors ENUM ( 'chartreuse' , 'mauve' , 'lime green' , 'puce' ) DEFAULT 'puce' ,
PRIMARY KEY ( id )
);
Using INFORMATION_SCHEMA to get table structure
To obtain information about a single column in a table by checking INFORMATION_SCHE
MA , use a statement of the following form:
mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'item'