Specifications

String Types
String types fall into three groups. First, there are plain old strings, that is, short pieces of text.
These are the CHAR (fixed length character) and VARCHAR (variable length character) types. You
can specify the width of each. Columns of type CHAR will be padded with spaces to the maxi-
mum width regardless of the size of the data, whereas VARCHAR columns vary in width with the
data. (Note that MySQL will strip the trailing spaces from CHARs when they are retrieved, and
from VARCHARs when they are stored.) There is a space versus speed trade off with these two
types, which we will discuss in more detail in Chapter 11.
Second, there are
TEXT and BLOB types. These come in various sizes. These are for longer text
or binary data, respectively. BLOBs are binary large objects. These can hold anything you like,
for example, image or sound data.
In practice, BLOB and TEXT columns are the same except that TEXT is case sensitive and BLOB is
not. Because these column types can hold large amounts of data, they require some special
considerations. We will discuss this in Chapter 11.
The third group has two special types, SET and ENUM. The SET type is used to specify that val-
ues in this column must come from a particular set of specified values. Column values can
contain more than one value from the set. You can have a maximum of 64 things in the speci-
fied set.
ENUM is an enumeration. It is very similar to SET, except that columns of this type can have
only one of the specified values or NULL, and that you can have a maximum of 65535 things in
the enumeration.
We’ve summarized the string data types in Tables 8.9, 8.10, and 8.11. Table 8.9 shows the plain
string types.
TABLE 8.9 Regular String Types
Type Range Description
[NATIONAL] 1 to 255 Fixed length string of length M, where M
CHAR(M) [BINARY] characters is between 1 and 255. The NATIONAL key-
word specifies that the default character set
should be used. This is the default in
MySQL anyway, but is included as it is part
of the ANSI SQL standard. The BINARY key-
word specifies that the data should be
treated as not case insensitive. (The default
is case sensitive.)
[NATIONAL] 1 to 255 Same as above, except they are
VARCHAR(M) characters variable length.
[BINARY]
Using MySQL
P
ART II
204
11 7842 CH08 3/6/01 3:38 PM Page 204