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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.