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.
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
. For either data type, the
allowable values can be extracted by stripping off the initial word
and the parentheses, splitting at the commas, and removing the
enclosing quotes from the individual values. Let’s write a get_enumorset_info()
routine to break
out these values from the data-type definition. While we’re at it, we
can have the routine return the column’s type, its default value, and
whether values can be NULL
. Then
the routine can be used by scripts that may need more than just the
list of values. Here is a version in Ruby. Its arguments are a database handle, a database name, a
table name, and a column name. It returns a hash with entries
corresponding to the various aspects of the column definition (or
nil
if the column does not
exist):
def get_enumorset_info(dbh, db_name, tbl_name, col_name) row = dbh.select_one( "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?", db_name, tbl_name, col_name) return nil if row.nil? info = {} info["name"] = row[0] return nil unless row[1] =~ /^(ENUM|SET)((.*))$/i info["type"] = $1 # split value list on commas, trim quotes from end of each word info["values"] = $2.split(",").collect { |val| val.sub(/^'(.*)'$/, "\1") } # determine whether column can contain NULL values info["nullable"] = (row[2].upcase == "YES") # get default value (nil represents NULL) info["default"] = row[3] return info end
The routine uses case-insensitive matching when checking the data type and nullable attributes. This guards against future possible lettercase changes in metadata results.
The following example shows one way to access and display each
element of the hash returned by get_enumorset_info()
:
info = get_enumorset_info(dbh, db_name, tbl_name, col_name) puts "Information for " + db_name + "." + tbl_name + "." + col_name + ":" if info.nil? puts "No information available (not an ENUM or SET column?)" else puts "Name: " + info["name"] puts "Type: " + info["type"] puts "Legal values: " + info["values"].join(",") puts "Nullable: " + (info["nullable"] ? "yes" : "no") puts "Default value: " + (info["default"].nil? ? "NULL" : info["default"]) end
That code produces the following output for the item
table colors
column:
Information for cookbook.item.colors: Name: colors Type: set Legal values: chartreuse,mauve,lime green,puce Nullable: yes Default value: puce
Equivalent routines for other APIs are similar. Such routines are especially handy for generating list elements in web forms. (See Recipes and .)
3.143.4.181