The CHAR and VARCHAR types are similar but differ in storage and retrieval methods, maximum length, and whether trailing spaces are preserved.
CHAR
The length specified for the CHAR type is the maximum number of characters it can store. 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]
CHARACTER SET specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If the binary property of CHARACTER SET is specified, the column is created as the corresponding binary string data type, and the CHAR type changes to the BINARY type.
The length of a CHAR column can be any value from 0 to 255. When a CHAR value is stored, the value is right-padded with spaces to the specified length.
For CHAR columns, trailing spaces in inserted values are silently truncated, regardless of the SQL mode. When retrieving CHAR values, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
VARCHAR
The length M specified for the VARCHAR type is the maximum number of characters it can store. 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]
CHARACTER SET specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If the binary property of CHARACTER SET is specified, 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 any value from 0 to 262,144.
Unlike CHAR, VARCHAR values are stored with a 1-byte or 2-byte length prefix plus data. The prefix indicates the number of bytes in the value. If the value does not exceed 255 bytes, it uses one byte; if it may exceed 255 bytes, it uses two bytes.
For VARCHAR columns, trailing spaces beyond the column length are truncated before insertion, generating a warning, regardless of the SQL mode.
VARCHAR values are not padded when stored. Per standard SQL, trailing spaces are preserved when storing and retrieving values.
In addition, OceanBase Database supports the extended CHARACTER VARYING(m) type, but we recommend that you use VARCHAR(m).