CHAR and VARCHAR

CHAR and VARCHAR types are similar, but differ in how they are stored and retrieved, their maximum length, and whether trailing spaces are preserved.

CHAR

The declared length of the CHAR type is the maximum number of characters that can be stored. For example, CHAR(30) can contain up to 30 characters.

Syntax:

[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 along with other attributes to specify the collation rules for the character set. If the binary attribute of CHARACTER SET is specified, the column will be created as the corresponding binary string data type, and CHAR becomes BINARY.

CHAR column length can be any value between 0 and 256. When storing CHAR values, they are right-padded with spaces to the specified length.

For CHAR columns, excess 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 declared length M of the VARCHAR type is the maximum number of characters that can be stored. For example, VARCHAR(50) can contain up to 50 characters.

Syntax:

[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 along with other attributes to specify the collation rules for the character set. If the binary attribute of CHARACTER SET is specified, the column will be created as the corresponding binary string data type, and VARCHAR becomes VARBINARY.

VARCHAR column length can be specified as any value between 0 and 262144.

Compared with CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus the data. The length prefix indicates the number of bytes in the value. If the value does not exceed 255 bytes, the column uses one byte; if the value may exceed 255 bytes, it uses two bytes.

For VARCHAR columns, trailing spaces that exceed the column length are truncated before insertion and generate a warning, regardless of the SQL mode.

VARCHAR values are not padded when stored. According to standard SQL, trailing spaces are preserved during both storage and retrieval.

Additionally, seekdb also supports the extended type CHARACTER VARYING(m), but VARCHAR(m) is recommended.