String Datatypes

The most commonly used datatypes are string datatypes. These store strings: for example, names, addresses, phone numbers, and ZIP Codes. As listed in Table D.1, there are basically two types of string datatype that you can usefixed-length strings and variable-length strings.

Table D.1. String Datatypes

Datatype	Description
CHAR	Fixed-length string from 1 to 255 chars long. Its size must be specified at create time, or MySQL assumes CHAR(1).
ENUM	Accepts one of a predefined set of up to 64K strings.
LONGTEXT	Same as TEXT, but with a maximum size of 4GB.
MEDIUMTEXT	Same as TEXT, but with a maximum size of 16K.
SET	Accepts zero or more of a predefined set of up to 64 strings.
TEXT	Variable-length text with a maximum size of 64K.
TINYTEXT	Same as TEXT, but with a maximum size of 255 bytes.
VARCHAR	Same as CHAR, but stores just the text. The size is a maximum, not a minimum.

Fixed-length strings are datatypes that are defined to accept a fixed number of characters, and that number is specified when the table is created. For example, you might allow 30 characters in a firstname column or 11 characters in a social-security-number column (the exact number needed allowing for the two dashes). Fixed-length columns do not allow more than the specified number of characters. They also allocate storage space for as many characters as specified. So, if the string Ben is stored in a 30-character firstname field, a full 30 bytes are stored. CHAR is an example of a fixed-length string type.

Variable-length strings store text of variable length. Some variable-length datatypes have a defined maximum size. Others are entirely variable. Either way, only the data specified is saved (and no extra data is stored). TEXT is an example of a variable-length string type.

If variable-length datatypes are so flexible, why would you ever want to use fixed-length datatypes? The answer is performance. MySQL can sort and manipulate fixed-length columns far more quickly than it can sort variable-length columns. In addition, MySQL does not allow you to index variable-length columns (or the variable portion of a column). This also dramatically affects performance.

Tip

Using Quotes Regardless of the form of string datatype being used, string values must always be surrounded by quotes (single quotes are often preferred).

Caution

When Numeric Values Are Not Numeric Values You might think that phone numbers and ZIP Codes should be stored in numeric fields (after all, they only store numeric data), but doing so would not be advisable. If you store the ZIP Code 01234 in a numeric field, the number 1234 would be saved. You'd actually lose a digit.

The basic rule to follow is this: If the number is a number used in calculations (sums, averages, and so on), it belongs in a numeric datatype column. If it is used as a literal string (that happens to contain only digits), it belongs in a string datatype column.