MySQL char & varchar character sets & storage sizes
CHAR
and VARCHAR
both count characters. Both of them count the maximum storage that they might require given the character encoding and length. For ASCII, that's 1 byte per character. For UTF-8, that's 3 bytes per character (not 4 as you'd expect, because MySQL's Unicode support is limited for some reason, and it doesn't support any Unicode characters which would require 4 bytes in UTF-8). So far, CHAR
and VARCHAR
are the same.
Now, CHAR
just goes ahead and reserves this amount of storage.
VARCHAR
instead allocated 1 or 2 bytes, depending on whether this maximum storage is < 256 or ≥ 256. And the actual amount of space occupied by the entry is these one or two bytes, plus the amount of space actually occupied by the string.
Interestingly, this makes 85 a magic number for UTF-8 VARCHAR
:
VARCHAR(85)
uses 1 byte for the length because the maximum possible length of 85 UTF-8 characters is 3 × 85 = 255.VARCHAR(86)
uses 2 byte for the length because the maximum possible length of 86 UTF-8 characters is 3 × 86 = 258.