CHAR and VARCHAR

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

CHAR

The length of a CHAR type specifies the maximum number of characters that can be stored. For example, CHAR(30) can store 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. You can use the COLLATE attribute and other attributes to specify the collation of the character set. If you specify the binary attribute of CHARACTER SET, 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 on the right to fill the specified length.

For a CHAR column, 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 length M of a VARCHAR type specifies the maximum number of characters that can be stored. For example, VARCHAR(50) can store 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. You can use the COLLATE attribute and other attributes to specify the collation of the character set. If you specify the binary attribute of CHARACTER SET, the column is created as the corresponding binary string data type, and VARCHAR becomes VARBINARY.

The length of a VARCHAR column can be a value between 0 and 262,144.

Compared with CHAR, VARCHAR values are stored with a 1-byte or 2-byte prefix indicating the length of the value. If the value is 255 bytes or less, the prefix is 1 byte. If the value may exceed 255 bytes, the prefix is 2 bytes.

For a VARCHAR column, 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. Trailing spaces are preserved during storage and retrieval according to standard SQL.

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