MySQL MySQL CHAR and VARCHAR Types

MySQL CHAR and VARCHAR Types

MySQL CHAR and VARCHAR types techbriefers.com

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 both differ on the basis they are stored and retrieved.
Difference between CHAR and VARCHAR is based on the their maximum length and trailing spaces retained or not by thee types.

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

MySQL CHAR and VARCHAR type column definition - techbriefers.com
MySQL CHAR and VARCHAR type column definition – techbriefers.com

{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 15 characters, if more than 15 characters are entered then the data gets truncated after 15 characters.

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

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

MySQL CHAR and VARCHAR type retrieve data - techbriefers.com
MySQL CHAR and VARCHAR type retrieve data – techbriefers.com

The CHAR type column length is fixed to the length declared at the time of creating 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 maximum 255 characters. When CHAR values are stored in table, they are stored as values right-padded with spaces to the specified length of that column. When CHAR type values are retrieved, trailing spaces are usually removed, this depends on PAD_CHAR_TO_FULL_LENGTH SQL mode. IF PAD_CHAR_TO_FULL_LENGTH mode is enabled the trailing spaces are not removed.

As the name says, VARCHAR type columns have values with variable length. The length specified at the time of creating column and the length value can be a value from 0 to 65,535. The allowed maximum length of a VARCHAR depends on the character setused and maximum row size i.e. 65,353 bytes, which is shared among all the columns. Usually MySQL has a limit of 4096 columns for 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.

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.

Share your thoughts

avatar
  Subscribe  
Notify of