Character data types store characters such as numbers and letters as strings. This topic describes the most commonly used character data types, including CHAR and VARCHAR types, as well as BINARY and VARBINARY types.
CHAR and VARCHAR types
The CHAR and VARCHAR types are similar, but they differ in how they store and retrieve data and in how they handle trailing spaces.
Both the CHAR and VARCHAR types can specify a maximum length. For example, CHAR(30) can store up to 30 characters.
For a CHAR column, the length can range from 0 to 256. When a CHAR value is stored, trailing spaces are removed. When the value is retrieved, if the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled, the value is padded with spaces to the maximum length.
For a VARCHAR column, the length can range from 0 to 262,144, with a maximum of 262,144 bytes.
For both CHAR and VARCHAR types, if the inserted data exceeds the maximum length, an error is returned if sql_mode contains STRICT_TRANS_TABLES. If sql_mode does not contain STRICT_TRANS_TABLES, the data is automatically truncated to the maximum length, and a warning is issued.
When storing VARCHAR and CHAR values, comparisons are based on the character set.
BINARY and VARBINARY types
The BINARY and VARBINARY types are similar to the CHAR and VARCHAR types, but they store binary data instead.
The maximum length for BINARY and VARBINARY types is the same as for CHAR and VARCHAR types, but the length is calculated in bytes.
When storing a BINARY value, the value is padded with 0x00 (i.e., '\0') on the right to the maximum length.
If strict mode is enabled, inserting data that exceeds the maximum length will result in an error. If strict mode is not enabled, the data will be truncated and a warning will be issued.
For BINARY types, comparisons are made explicitly, including trailing null bytes. 0x00 (null byte) is different from a space, and 0x00 is sorted before a space.