ENUM
and
SET
columns provide an
efficient way to define columns that can contain only a given
set of values. See Section 11.3.5, “The ENUM Type”, and
Section 11.3.6, “The SET Type”.
With strict mode enabled (see Section 5.1.10, “Server SQL Modes”), the
definition of a ENUM
or
SET
column acts as a constraint
on values entered into the column. An error occurs for values
that do not satisfy these conditions:
An
ENUM
value must be one of those listed in the column definition, or the internal numeric equivalent thereof. The value cannot be the error value (that is, 0 or the empty string). For a column defined asENUM('a','b','c')
, values such as''
,'d'
, or'ax'
are invalid and are rejected.A
SET
value must be the empty string or a value consisting only of the values listed in the column definition separated by commas. For a column defined asSET('a','b','c')
, values such as'd'
or'a,b,c,d'
are invalid and are rejected.
Errors for invalid values can be suppressed in strict mode if
you use INSERT
IGNORE
or UPDATE IGNORE
. In this
case, a warning is generated rather than an error. For
ENUM
, the value is inserted as
the error member (0
). For
SET
, the value is inserted as
given except that any invalid substrings are deleted. For
example, 'a,x,b,y'
results in a value of
'a,b'
.