MySQL CHAR and VARCHAR Types

The CHAR and VARCHAR types are String Types in MySQL as they store string (or say text type) data.

The CHAR and VARCHAR types in MySQL are quite similar, but there is a difference on the basis MySQL stores and retrieve them.

Difference between CHAR and VARCHAR is based on their maximum length and trailing spaces retained or not by the types.

When we declare CHAR and VARCHAR types, we define a length which indicates the maximum number of characters. When we declare CHAR and VARCHAR type, we declare then with a length (of some digits). That length indicates the maximum number of characters that will be stored in that particular column/filed of the table.

{table structure image for creating both type fields}

INSERT INTO sample_table (char_text, varchar_text) values (’15charactertext’, ’15vcharactertxt’), (’17 character text’,’17 vcharacter txt’), (’13 chars text’, ’13 vchar text’);

For example:
CHAR(15) can have maximum 15 characters. If more than 15 characters are entered then the data gets truncated after 15 characters.

In the same way, VARCHAR(15) can have maximum of 15 characters. If we will enter more than 15 characters, then because of limit validation, MySQL will truncate the data after 15 characters.

INSERT INTO sample_table (`char_text`,`varchar_text`) values ('15charactertext','15charactertext'), ('17 character text','17 character text'), ('13 chars text', '13 chars text');

The above query may not execute in most of the cases. Specifically in the non-windows platform and will throw an error for data limit (like mentioned below).

#1406 – Data too long for column ‘char_text’ at row 1

The CHAR type column length is fixed to a length which we declare at the time we create a column or table. The length can be any value ranging from 0 to 255. Which means that the CHAR type column can contain minimum zero and a maximum of 255 characters. When CHAR values are stored in the table, they will be stored as values right-padded with spaces to the specified length of that column.

When we retrieve CHAR type values, MySQL usually removes the trailing spaces. This depends on PAD_CHAR_TO_FULL_LENGTH SQL mode.

IF PAD_CHAR_TO_FULL_LENGTH is in the enabled state, in that case, MySQL will not remove the trailing spaces.

As the name says, VARCHAR type columns have values with variable length. The length specified at the time of creating a column and the length value can range from 0 to 65,535. The allowed maximum length of a VARCHAR depends on the character set used and maximum row size i.e. 65,353 bytes, which will be shared among all the columns. Usually, MySQL has a limit of 4096 columns for the table but can also be lesser for a given table.

Unlike CHAR type, VARCHAR type stores value as a 1-byte or 2-byte length prefix plus actual data.

CREATE TABLE table_varchar (col_one VARCHAR(65533) NOT NULL);

In the query above, col_one is of size 65533 which means 65533 + 2 (2 byte prefix) = 65535 in size. After adding this column table can not have any other column as the maximum row size is allocated to col_one. Hence no more bits are left for allocation.

Unlike CHAR types, VARCHAR values do not contain any padding at the time they are stored.