MySQL Cookbook

Getting ENUM and SET Column Information

Problem

You want to know what the legal members of an ENUM or SET column are.

Solution

Use SHOW COLUMNS to get the column definition and extract the member list from it.

Discussion

It’s often useful to know the list of legal values for an ENUM or SET column. Suppose 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, issue a SHOW COLUMNS statement for the column and look at the Type value in the result. For example, the colors column of the item table has a Type value that looks like this:

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

ENUM columns are similar, except that they say enum rather than set. For either column type, the allowable values can be extracted by stripping off the initial word and the parentheses, splitting at the commas, and removing the surrounding quotes from the individual ...