16.4 Complex Data Types

MySQL's complex data types ENUM and SET are really nothing more than special string types. We break them out because they are conceptually more complex and represent a lead into the SQL3 data types that MySQL may support one day.

Syntax

ENUM(value1, value2, ...)

Storage

1-255 members: 1 byte

256-65,535 members: 2 bytes

Description

Stores one value of a predefined list of possible strings. When you create an ENUM column, you provide a list of all possible values. Inserts and updates are allowed to set the column to values only from that list. Any attempt to insert a value that is not part of the enumeration will cause an empty string to be stored instead.

You may reference the list of possible values by index where the index of the first possible value is 0. For example:

SELECT COLID FROM TBL WHERE COLENUM = 0;

Assuming COLID is a primary key column and COLENUM is the column of type ENUM, this SQL will retrieve the primary keys of all rows in which the COLENUM value equals the first value of that list. Similarly, sorting on ENUM columns happens according to index, not string value.

The maximum number of elements allowed for an ENUM column is 65,535.

Syntax

SET(value1, value2, ...)

Storage

1-8 members: 1 byte

9-16 members: 2 bytes

17-24 members: 3 bytes

25-32 members: 4 bytes

33-64 members: 8 bytes

Description

A list of values taken from a predefined set of values. A field can contain any number—including none—of the strings specified in the SET statement. A SET is basically an ENUM that allows each field to contain more than one of the specified values. A SET, however, is not stored according to index but as a complex bit map. Given a SET with the members Orange, Apple, Pear, and Banana, each element is represented by an "on" bit in a byte, as shown in Table 16-2.

Table 16-2. MySQL representations of set elements

Member	Decimal value	Bitwise representation
Orange	1	0001
Apple	2	0010
Pear	4	0100
Banana	8	1000

The values Orange and Pear are therefore stored in the database as 5 (0101).

You can store a maximum of 64 values in a SET column. Though you can assign the same value multiple times in an SQL statement updating a SET column, only a single value will actually be stored.