Character data types store characters such as numbers and letters as strings. This topic describes the most common character data types: CHAR, VARCHAR, BINARY, and VARBINARY.
CHAR and VARCHAR
The CHAR and VARCHAR types are similar but differ in the way they are stored and retrieved and how they process trailing spaces.
You can specify the maximum length for both the CHAR and VARCHAR types. For example, CHAR(30) can store up to 30 characters.
You can specify the length of a CHAR column to a value from 0 to 255. When CHAR values are stored, their consecutive trailing spaces are truncated. When CHAR values are retrieved, they are padded with spaces to the specified length if the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
You can specify the length of a VARCHAR column to a value from 0 to 65536. The valid maximum length of a VARCHAR value is 65,536 bytes.
If the length of a value inserted into a CHAR or VARCHAR column exceeds the maximum length of the column and the value of sql_mode contains STRICT_TRANS_TABLES, an error is reported. However, if the value of sql_mode does not contain STRICT_TRANS_TABLES, the excess part of the inserted value is automatically truncated to fit the maximum length and an alert is reported.
When values in the VARCHAR and CHAR columns are stored, the values are compared based on the collation configured for the columns.
BINARY and VARBINARY
The BINARY and VARBINARY types are similar to the CHAR and VARCHAR types. The difference is that the BINARY and VARBINARY types store binary data.
The maximum length is the same for the BINARY and VARBINARY types as it is for the CHAR and VARCHAR types. However, the maximum length for the BINARY and VARBINARY types is measured in bytes.
When BINARY values are stored, the values are right-padded with 0x00, that is, \0, to the specified maximum length.
In strict mode, an error is reported if the length of a value to be inserted into a column exceeds the maximum length of the column. In non-strict mode, if the length of a value to be inserted into a column exceeds the maximum length of the column, the excess part is truncated and an alert is reported.
The trailing zero bytes (0x00) of BINARY values are explicitly compared. 0x00 and space differ in comparisons, with 0x00 sorting before space.