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 ...
Get MySQL Cookbook, 2nd Edition 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.