Oracle 5.0, Справочное руководство

Страница: 874 / 3232

String Types
854
columns considered as a group. For more information on these limits, see Section E.7.5, “Limits
Imposed by
.frm
File Structure” .
Trailing spaces are automatically deleted from
ENUM
member values in the table definition when a table
is created.
When retrieved, values stored into an
ENUM
column are displayed using the lettercase that was used
in the column definition. Note that
ENUM
columns can be assigned a character set and collation. For
binary or case-sensitive collations, lettercase is taken into account when assigning values to the
column.
If you retrieve an
ENUM
value in a numeric context, the column value's index is returned. For example,
you can retrieve numeric values from an
ENUM
column like this:
mysql> SELECT enum_col+0 FROM tbl_name;
If you store a number into an
ENUM
column, the number is treated as the index into the possible values,
and the value stored is the enumeration member with that index. (However, this does not work with
LOAD DATA
, which treats all input as strings.) If the numeric value is quoted, it is still interpreted as
an index if there is no matching string in the list of enumeration values. For these reasons, it is not
advisable to define an
ENUM
column with enumeration values that look like numbers, because this can
easily become confusing. For example, the following column has enumeration members with string
values of
'0'
,
'1'
, and
'2'
, but numeric index values of
1
,
2
, and
3
:
numbers ENUM('0','1','2')
If you store
2
, it is interpreted as an index value, and becomes
'1'
(the value with index 2). If you store
'2'
, it matches an enumeration value, so it is stored as
'2'
. If you store
'3'
, it does not match any
enumeration value, so it is treated as an index and becomes
'2'
(the value with index 3).
mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1 |
| 2 |
| 2 |
+---------+
ENUM
values are sorted according to the order in which the enumeration members were listed in the
column specification. (In other words,
ENUM
values are sorted according to their index numbers.) For
example,
'a'
sorts before
'b'
for
ENUM('a', 'b')
, but
'b'
sorts before
'a'
for
ENUM('b',
'a')
. The empty string sorts before nonempty strings, and
NULL
values sort before all other
enumeration values. To prevent unexpected results, specify the
ENUM
list in alphabetic order. You
can also use
ORDER BY CAST(col AS CHAR)
or
ORDER BY CONCAT(col)
to make sure that the
column is sorted lexically rather than by index number.
Functions such as
SUM()
[972] or
AVG()
[969] that expect a numeric argument cast the argument
to a number if necessary. For
ENUM
values, the cast operation causes the index number to be used.
To determine all possible values for an
ENUM
column, use
SHOW COLUMNS FROM tbl_name LIKE
'enum_col'
and parse the
ENUM
definition in the
Type
column of the output.
In the C API,
ENUM
values are returned as strings. For information about using result set metadata to
distinguish them from other strings, see Section 20.6.4, “C API Data Structures” .
11.1.6.5. The
SET
Type
A
SET
is a string object that can have zero or more values, each of which must be chosen from a list
of permitted values specified when the table is created.
SET
column values that consist of multiple set
members are specified with members separated by commas (“
,
”). A consequence of this is that
SET
member values should not themselves contain commas.