Choosing a String Data Type

Problem

You need 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 allowable values?

Discussion

MySQL provides several binary and nonbinary string data types. These types come in pairs as shown in the following table.

Binary data typeNonbinary data typeMaximum length
BINARY CHAR 255
VARBINARY VARCHAR 65,535
TINYBLOB TINYTEXT 255
BLOB TEXT 65,535
MEDIUMBLOB MEDIUMTEXT 16,777,215
LONGBLOB LONGTEXT 4,294,967,295

For the binary data types, the maximum length is the number of bytes the string must be able to hold. For the nonbinary types, the maximum length is the number of characters the string must be able to hold (which for a string containing multibyte characters requires more than that many bytes).

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 necessary when stored. For BINARY, the pad value is 0x00 (the zero-valued byte, also known as ASCII ...

Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.