Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.3Mb
PDF (A4) - 35.3Mb
PDF (RPM) - 34.3Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 305.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

1.8.3.4 ENUM and SET Constraints

ENUM and SET columns provide an efficient way to define columns that can contain only a given set of values. See Section 12.4.4, “The ENUM Type”, and Section 12.4.5, “The SET Type”.

With strict mode enabled (see Section 6.1.7, “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 as ENUM('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 as SET('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'.


User Comments
  Posted by flobee (c) on November 6, 2005
like already metioned: "An ENUM value must be one of those listed ..."
when fetching enum fields eg. to an array:
enum('a','b','c') == array(1=>'a', 2=>'b', 3=>'c')
Sign Up Login You must be logged in to post a comment.