The CHAR and VARCHAR types are similar, but differ in the storage and retrieval methods, the maximum length, and whether trailing spaces are retained.
CHAR
The length specified in the CHAR type is the maximum number of characters that can be stored. For example, CHAR(30) indicates that 30 characters can be contained.
Syntax:
[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If CHARACTER SET specifies the binary character set, the column is created as a binary string column, and the CHAR type changes to the BINARY type.
The length of a CHAR column can be a value between 0 and 255 characters. When a CHAR value is stored, the value is right-padded with spaces to the specified length.
Excess trailing spaces in the value inserted to a CHAR column are silently truncated, regardless of the SQL mode. In the retrieval of a CHAR value, if the PAD_CHAR_TO_FULL_LENGTH SQL mode is disabled, trailing spaces are truncated.
VARCHAR
The length specified in the VARCHAR type is the maximum number of characters that can be stored. For example, VARCHAR(50) indicates that 50 characters can be contained.
Syntax:
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If CHARACTER SET specifies the binary character set, the column is created as a binary string column, and the VARCHAR type changes to the VARBINARY type.
The length of a VARCHAR column can be a value between 0 and 65,535 characters.
Compared with the CHAR value, the VARCHAR value is stored with a prefix of one or two characters. The prefix indicates the number of characters in the value. The prefix occupies one character if the value does not exceed 255 characters, or two characters if the value exceeds 255 characters.
Excess trailing spaces in the value inserted to a VARCHAR column are truncated, regardless of the SQL mode, and an alert is generated.
A VARCHAR value is not padded when it is stored. To comply with standard SQL, trailing spaces are preserved when the value is stored or retrieved.