The CHAR and VARCHAR types are similar, but they 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 stored.
The syntax is as follows:
[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE or another attribute 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 ranges from 0 to 256. 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 M 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 stored.
The syntax is as follows:
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE or another attribute 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 ranges from 0 to 262,144.
In contrast to a CHAR value, a VARCHAR value is stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if the value requires no more than 255 bytes, or two length bytes if the value requires more than 255 bytes.
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.
In addition, OceanBase Database supports the extended CHARACTER VARYING(m) type, but we recommend that you use VARCHAR(m).