Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
ENUM
and SET
columns provide an efficient way to define columns that can contain only a given set of values. See Section 11.4.4, "The ENUM Type", and Section 11.4.5, "The SET Type". However, before MySQL 5.0.2, ENUM and SET
columns do not provide true constraints on entry of invalid data:
ENUM columns always have a default value. If you specify no default
value, then it is NULL for columns that can have NULL, otherwise it is the first enumeration value in the column
definition.
If you insert an incorrect value into an ENUM column or if you force a value into an ENUM column with IGNORE, it is set to the
reserved enumeration value of 0, which is displayed as an empty string in
string context.
If you insert an incorrect value into a SET column, the incorrect value is ignored. For example, if the column
can contain the values 'a', 'b', and 'c', an attempt to assign 'a,x,b,y' results
in a value of 'a,b'.
As of MySQL 5.0.2, you can configure the server to use strict SQL mode. See Section
5.1.7, "Server SQL Modes". With strict mode enabled, the definition of a ENUM or SET
column does act 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'.