Managing & Using MySQL, 2nd Edition

Character Types

Managing character types is a little more complicated. Not only do you have to worry about the minimum and maximum string lengths, but you also have to worry about the average size and the amount of variation. For our current purposes, an index is a field or combination of fields on which you plan to search—basically, the fields in your WHERE clause. Indexing is, however, much more complicated, so we will provide further details later in the chapter. What’s important to note here is that indexing on character fields works best when the field is a fixed length. If there is little or, preferably, no variation in the length of your character-based fields, then a CHAR type is appropriate. An example of a good candidate for a CHAR field is a country code. The ISO provides a comprehensive list of standard two-character representations of country codes (US for the U.S., FR for France, etc.).^[3] Because these codes are always exactly two characters, a CHAR(2) is the best way to maintain the country code based on the ISO representation

A value does not need to be constant length to use a CHAR field. It should, however, have very little variance. Phone numbers, for example, can be stored safely in a CHAR(13) field even though phone number lengths vary from nation to nation. The variance is little enough that there is no point in making a phone number field variable in length. Keep in mind that with a CHAR field, no matter how big the actual string being stored is, the ...