Book HomeManaging and Using MySQLSearch this book

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.

ENUM

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.

SET

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.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.