The CHAR and VARCHAR types are similar, but differ in how they store and retrieve data, their maximum lengths, and whether trailing spaces are retained.
CHAR
The length specified for the CHAR type indicates the maximum number of characters that can be stored. For example, CHAR(30) means it can hold up to 30 characters.
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 the COLLATE attribute and other attributes to specify the collation for the character set. If you specify the binary attribute for CHARACTER SET, the column will be created as the corresponding binary string data type, and CHAR becomes BINARY.
The length of a CHAR column can be any value from 0 to 256. When storing CHAR values, they are right-padded with spaces to the specified length.
For CHAR columns, regardless of the SQL mode, trailing spaces in inserted values are silently truncated. When retrieving CHAR values, trailing spaces are deleted if the PAD_CHAR_TO_FULL_LENGTH SQL mode is not enabled.
VARCHAR
The length M specified for the VARCHAR type indicates the maximum number of characters that can be stored. For example, VARCHAR(50) means it can accommodate up to 50 characters.
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 the COLLATE attribute and other attributes to specify the collation for the character set. If you specify the binary attribute for CHARACTER SET, the column will be created as the corresponding binary string data type, and VARCHAR will become VARBINARY.
The length of a VARCHAR column can be set to a value between 0 and 262,144.
Compared with CHAR, a VARCHAR value is stored as a length prefix of 1 or 2 bytes followed by the data. The length prefix indicates the number of bytes in the value. If the value does not exceed 255 bytes, the column occupies one character; if the value may exceed 255 bytes, it occupies two characters.
For VARCHAR columns, regardless of the SQL mode, trailing spaces exceeding the column length are truncated and an alert is generated before insertion.
VARCHAR values are not padded when stored. According to standard SQL, trailing spaces are retained when storing and retrieving values.
In addition, OceanBase AI Database also supports the extended type CHARACTER VARYING(m), but it is recommended to use VARCHAR(m).