Comparison between VARCHAR and CHAR data types

These are the two highly used string types in MySQL. Both store the character values. It’s very difficult to explain exactly how these values are stored on disk and in memory, because the implementations are storage engine-dependent (for example, Falcon uses its own storage formats for almost every data type). I am going to explain for MyISAM and InnoDB.

VARCHAR

VARCHAR as name represent variable character. It stores variable-length character strings and is the most common used string data type. It can require less storage space than fixed-length types, because it uses only as much space as it needs i.e. less space is used to store shorter values.

The exception is a MyISAM table created with ROW_FORMAT=FIXED, which uses a fixed amount of space on disk for each row and can thus waste space and used lot of disk space.

VARCHAR uses 1 or 2 extra bytes to record the value’s length: 1 byte if the column’s maximum length is 255 bytes or less, and 2 bytes if it’s more. For instance the latin1 character set, a VARCHAR (10) will use up to 11 bytes of storage space. A VARCHAR (1000) can use up to 1002 bytes, because it needs 2 bytes to store length information.

VARCHAR helps performance because it saves space. However, because the rows are variable-length, they can grow when you update them, which can cause extra work. If a row grows and no longer fits in its original location, the behavior is storage engine-dependent. For example, MyISAM may fragment the row, and InnoDB may need to split the page to fit the row into it. Other storage engines may never update data in place at all.

It’s usually worth using VARCHAR when the maximum column length is much larger than the average length; when updates to the field are rare, so fragmentation is not a problem; and when you’re using a complex character set such as UTF-8, where each character uses a variable number of bytes of storage.

CHAR

CHAR is second most common used string data type. It is fixed-length. MySQL always allocates enough space for the specified number of characters. When storing a CHAR value, MySQL removes any trailing spaces. Values are padded with spaces as needed for comparisons. CHAR is useful if you want to store very short strings, or if all the values are nearly the same length. For example, CHAR is a good choice for MD5 values for user passwords, which are always the same length. CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to fragmentation.

For very short columns, CHAR is also more efficient than VARCHAR; a CHAR (1) designed to hold only Y and N values will use only one byte in a single byte character set, but a VARCHAR(1) would use two bytes because of the length byte.

I am going to explain the difference between VARCHAR and CHAR with respect of storing the value.

I am going to create a table with one column of CHAR data type.


mysql> CREATE TABLE mysql_char( char_val CHAR(10));
mysql> INSERT INTO mysql_char (char_val) VALUES
->('mysql'), ('  mysql'), ('mysql  ');


When you retrieve the values, the trailing spaces have been stripped away:

mysql> SELECT CONCAT("'", char_val, "'") FROM  mysql_char;

+—————————-+

| CONCAT(“‘”, char_val, “‘”) |

+—————————-+

| ‘mysql’ |

| ‘ mysql’ |

| ‘mysql’ |

+—————————-+

If we store the same values into a VARCHAR (10) column, we get the following result upon retrieval:

mysql> SELECT CONCAT("'", varchar_val, "'") FROM mysql_varchar;

+——————————-+

| CONCAT(“‘”, varchar_val, “‘”) |

+——————————-+

| ‘mysql’ |

| ‘ mysql’ |

| ‘mysql ‘ |

+——————————-+

References:

1. MySQL Manual
2. High Performance MySQL