Working with Database Structures - Learning MySQL - page 201

Other string types
The remaining types in MySQL are variants of the string type; here's a list that you can
choose from—excepting CHAR , which was described in “Common column types”:
VARCHAR( width )
A commonly used string type. Stores variable-length strings (such as names, ad-
dresses, or cities) up to a maximum width . The maximum value of width is 65,535
characters.
Prior to MySQL version 5.0.3, the maximum length was 255 characters. Trying to
specify a longer length would cause an error in versions up to 4.1.0. Between ver-
sions 4.1.0 and 5.0.3, the server would silently change the column type to the
smallest TEXT type that would hold values of that length
A VARCHAR type incurs one or two extra bytes of overhead to store the length of the
string, depending on whether the string is shorter than or longer than 255
characters.
Trailing spaces are removed when a value is stored; you can use TEXT or BLOB types
to avoid this behavior.
BINARY(width) and VARBINARY(width)
Available since MySQL 4.1.2, these are equivalent to CHAR and VARCHAR but allow
you to store binary strings. Binary strings have no character set, and sorting them
is case-sensitive. Read the descriptions of CHAR and VARCHAR for other details. If
you're using a MySQL version earlier than 4.1.2, you can create the same behavior
by adding the keyword BINARY after the CHAR or VARCHAR declaration, as in CHAR(12)
BINARY .
BLOB
The commonly used type for storing large data. Stores a variable amount of data
(such as an image, video, or other nontext file) up to 65,535 bytes in length. The
data is treated as binary—that is, no character set is assumed, and comparisons
and sorts are case-sensitive. There is no trailing-space-removal behavior as for the
CHAR or VARCHAR types. In addition, a DEFAULT clause is not permitted, and you must
take a prefix of the value when using it in an index (this is discussed in the next
section).
TEXT
A commonly used type for storing large string data objects. Stores a variable
amount of data (such as a document or other text file) up to 65,535 bytes in length.
It is identical to BLOB , except that the data is treated as belonging to a character set.
Since MySQL 4.1, the character set can be set for each column, and prior to that
the character set of the server was assumed. Comparisons and sorts are case- in-
sensitive .
TINYBLOB and TINYTEXT
Identical to BLOB and TEXT , respectively, except that a maximum of 255 bytes can
be stored.