MySQL Cookbook, 2nd Edition

Getting ENUM and SET Column Information

Problem

You want to know what members an ENUM or SET column has.

Solution

This problem is a subset of getting table structure metadata. Obtain the column definition from the table metadata, and then extract the member list from the definition.

Discussion

It’s often useful to know the list of legal values for an ENUM or SET column. Suppose that you want to present a web form containing a pop-up menu that has options corresponding to each legal value of an ENUM column, such as the sizes in which a garment can be ordered, or the available shipping methods for delivering a package. You could hardwire the choices into the script that generates the form, but if you alter the column later (for example, to add a new enumeration value), you introduce a discrepancy between the column and the script that uses it. If instead you look up the legal values using the table metadata, the script always produces a pop-up that contains the proper set of values. A similar approach can be used with SET columns.

To find out what values an ENUM or SET column can have, get the column definition using one of the techniques described in Accessing Table Column Definitions and look at the data type in the definition. For example, if you select from the INFORMATION_SCHEMA COLUMNS table, the COLUMN_TYPE value for the colors column of the item table looks like this:

set('chartreuse','mauve','lime green','puce')

ENUM columns are similar, except that they say enum rather than set ...