CHAR and VARCHAR

The CHAR and VARCHAR types are similar, but they differ in terms of storage and retrieval methods, maximum length, and whether trailing spaces are preserved.

CHAR

The CHAR type declares the maximum number of characters that can be stored. For example, CHAR(30) indicates that up to 30 characters can be stored.

The syntax is as follows:

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

CHARACTER SET is used to specify the character set. If needed, you can use the COLLATE attribute and other attributes to specify the collation of the character set. If the binary attribute of CHARACTER SET is specified, the column is created as the corresponding binary string data type, and CHAR becomes BINARY.

The length of a CHAR column can be a value between 0 and 256. When storing CHAR values, trailing spaces are added to the right to fill 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 if the PAD_CHAR_TO_FULL_LENGTH SQL mode is not enabled.

VARCHAR

The VARCHAR type declares the maximum number of characters that can be stored as M. For example, VARCHAR(50) indicates that up to 50 characters can be stored.

The syntax is as follows:

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

CHARACTER SET is used to specify the character set. If needed, you can use the COLLATE attribute and other attributes to specify the collation of the character set. If the binary attribute of CHARACTER SET is specified, the column is created as the corresponding binary string data type, and VARCHAR becomes VARBINARY.

The length of a VARCHAR column can be specified as a value between 0 and 262144.

Compared with CHAR, VARCHAR values are stored with a 1-byte or 2-byte length prefix followed by the data. The length prefix indicates the number of bytes in the value. If the value is 255 bytes or less, the column occupies one character. If the value may exceed 255 bytes, the column occupies two characters.

For VARCHAR columns, trailing spaces exceeding the column length are silently truncated and an alert is generated, regardless of the SQL mode.

VARCHAR values are not padded when stored. According to standard SQL, trailing spaces are preserved when storing and retrieving values.

In addition, OceanBase Database supports the extended type CHARACTER VARYING(m), but we recommend that you use VARCHAR(m).