Database Reference
In-Depth Information
+----------------------------------------------------+
| COLUMN_TYPE |
+----------------------------------------------------+
| enum('blue','red','green','brown','black','white') |
+----------------------------------------------------+
If you extract the list of enumeration members from the
COLUMN_TYPE
value and store
them in an array
@members
, you can perform the membership test like this:
$valid
=
grep
(
/^$val$/i
,
@members
);
The pattern constructor begins and ends with
^
and
$
to require
$val
to match an entire
enumeration member (rather than just a substring). It also is followed by an
i
to specify
a case-insensitive comparison because the default collation is
latin1_swedish_ci
,
which is case-insensitive. (If you have a column with a different collation, adjust ac‐
cordingly.)
In
Recipe 10.7
, we wrote a function
get_enumorset_info()
that returns
ENUM
or
SET
column metadata. This includes the list of members, so it's easy to use that function to
write another utility routine,
check_enum_value()
, that gets the legal enumeration val‐
ues and performs the membership test. The routine takes four arguments: a database
handle, the table name and column name for the
ENUM
column, and the value to check.
It returns true or false to indicate whether the value is legal:
sub
check_enum_value
{
my
(
$dbh
,
$db_name
,
$tbl_name
,
$col_name
,
$val
)
=
@_
;
my
$valid
=
0
;
my
$info
=
get_enumorset_info
(
$dbh
,
$db_name
,
$tbl_name
,
$col_name
);
if
(
$info
&&
uc
(
$info
->
{
type
})
eq
"ENUM"
)
{
# use case-insensitive comparison because default collation
# (latin1_swedish_ci) is case-insensitive (adjust if you use
# a different collation)
$valid
=
grep
(
/^$val$/i
,
@
{
$info
->
{
values
}});
}
return
$valid
;
}
For single-value testing, such as to validate a value submitted in a web form, that kind
of test works well. However, to test a lot of values (like an entire column in a datafile),
it's better to read the enumeration values into memory once, then use them repeatedly
to check each data value. Furthermore, it's a lot more efficient to perform hash lookups
than array lookups (in Perl at least). To do so, retrieve the legal enumeration values and
store them as keys of a hash. Then test each input value by checking whether it exists
as a hash key. It's a little more effort to construct the hash, which is why
check_enum_val
ue()
doesn't do so. But for bulk validation, the improved lookup speed more than makes
up for the hash construction overhead. (To check for yourself the relative efficiency of
