How to choose a string data type in MySQL

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:

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

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 NUL). CHAR values are padded with spaces. Trailing pad bytes or characters are stripped from BINARY and CHAR values when they are retrieved.

For VARBINARY, VARCHAR, and the BLOB and TEXT types, MySQL stores values using only as much storage as required, up to the maximum column length. No padding is added or stripped when values are stored or retrieved.

If you want to preserve trailing pad values that are present in the original strings that are stored, use a data type for which no stripping occurs. For example, if you’re storing character (nonbinary) strings that might end with spaces, and you want to preserve them, use VARCHAR or one of the TEXT data types. The following statements illustrate the difference in trailing-space handling for CHAR and VARCHAR columns:

mysql>CREATE TABLE t (c1 CHAR(10), c2 VARCHAR(10));

mysql> INSERT INTO t (c1,c2) VALUES('abc       ','abc       ');

mysql> SELECT c1, c2, CHAR_LENGTH(c1), CHAR_LENGTH(c2) FROM t;

+------+------------+-----------------+-----------------+

| c1   | c2         | CHAR_LENGTH(c1) | CHAR_LENGTH(c2) |

+------+------------+-----------------+-----------------+

| abc  | abc        |               3 |              10 |

+------+------------+-----------------+-----------------+

Thus, if you store a string that contains trailing spaces into a CHAR column, you will find that they’re gone when you retrieve the value. Similar padding and stripping occurs for BINARY columns, except that the pad value is 0x00.

A table can include a mix of binary and nonbinary string columns, and its nonbinary columns can use different character sets and collations. When you declare a nonbinary string column, use the CHARACTER SET and COLLATE attributes if you require a particular character set and collation. For example, if you need to store utf8 (Unicode) and sjis (Japanese) strings, you might define a table with two columns like this:

CREATE TABLE mytbl

(

  utf8data VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_danish_ci,

  sjisdata VARCHAR(100) CHARACTER SET sjis COLLATE sjis_japanese_ci

);

It is allowable to omit CHARACTER SET, COLLATE, or both from a column definition:

The server default character set and collation are latin1 and latin1_swedish_ci unless you start the server with the --character-set-server and --collation-server options to specify different values. This means that, by default, strings use the latin1 character set and are not case-sensitive.

MySQL also supports ENUM and SET string types, which are used for data that has a fixed set of allowable values. You can use the CHARACTER SET and COLLATE attributes for these data types as well.