Working with Strings - MySQL - page 144

| lk |
| lm |
| ll |
+------+

5.2. Choosing a String Data Type

Problem
You want to store string data but aren't sure which is the most appropriate data type.

Solution
Choose the data type according to the characteristics of the information to be stored
and how you need to use it. Consider questions such as these:

• Are the strings binary or nonbinary?
• Does case sensitivity matter?
• What is the maximum string length?
• Do you want to store fixed- or variable-length values?
• Do you need to retain trailing spaces?
• Is there a fixed set of permitted values?

Discussion
MySQL provides several binary and nonbinary string data types. These types come in
pairs as shown in the following table. The maximum length is in bytes, whether the type
is binary or nonbinary. For nonbinary types, the maximum number of characters is less
for strings that contain multibyte characters:

Binary data type Nonbinary data type Maximum length

255

BINARY CHAR

65,535

VARBINARY VARCHAR

255

TINYBLOB TINYTEXT

65,535

BLOB TEXT

16,777,215

MEDIUMBLOB MEDIUMTEXT

4,294,967,295

LONGBLOB LONGTEXT

For the BINARY and CHAR data types, MySQL stores column values using a fixed width.
For example, values stored in a BINARY(10) or CHAR(10) column always take 10 bytes
or 10 characters, respectively. Shorter values are padded to the required length as nec‐