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 ...