MySQL 5.1 Reference Manual  /  ...  /  The CHAR and VARCHAR Types

11.4.1 The CHAR and VARCHAR Types

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section C.7.4, “Limits on Table Column Count and Row Size”.

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, “Server SQL Modes”.

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns (assuming that the column uses a single-byte character set such as latin1).

ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required
''' '4 bytes''1 byte
'ab''ab '4 bytes'ab'3 bytes
'abcd''abcd'4 bytes'abcd'5 bytes
'abcdefgh''abcd'4 bytes'abcd'5 bytes

The values shown as stored in the last row of the table apply only when not using strict mode; if MySQL is running in strict mode, values that exceed the column length are not stored, and an error results.

If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the values retrieved from the columns are not always the same because trailing spaces are removed from CHAR columns upon retrieval. The following example illustrates this difference:

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
| (ab  )              | (ab)                |
1 row in set (0.06 sec)

Values in CHAR and VARCHAR columns are sorted and compared according to the character set collation assigned to the column.

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. Comparison in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. For example:

mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO names VALUES ('Monty');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty', myname = 'Monty  ' FROM names;
| myname = 'Monty' | myname = 'Monty  ' |
|                1 |                  1 |
1 row in set (0.00 sec)

mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;
| myname LIKE 'Monty' | myname LIKE 'Monty  ' |
|                   1 |                     0 |
1 row in set (0.00 sec)

This is true for all MySQL versions, and is not affected by the server SQL mode.


For more information about MySQL character sets and collations, see Section 10.1, “Character Set Support”. For additional information about storage requirements, see Section 11.7, “Data Type Storage Requirements”.

For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.

Download this Manual
User Comments
  Posted by Julian Morrison on July 14, 2006
Note that using CHAR will only speed up your access if the whole record is fixed size. That is, if you use any variable size object, you might as well make all of them variable size. You gain no speed by using a CHAR in a table that also contains a VARCHAR.
  Posted by Kirby Wirby on April 9, 2007
Keep in mind that defining a column as VARCHAR will only save space if the data in a particular column is variable in length. I've worked on plenty of data sets where data in a given column is fixed in size -- e.g., code values or indicator/flag fields. In these cases, it's more space-efficient to use CHAR. Consider the case of an indicator field where the value is either 'Y' or 'N'. If defined as a CHAR, the field requires only one byte. However, if defined as a VARCHAR, the field requires two bytes. I worked on a multi-terabyte project at Bank of America where the DBAs actually went to the trouble to rebuild some tables that contained numerous flag or indicator fields because the fields were originally defined as VARCHAR(1) instead of CHAR(1).
  Posted by San jeet on May 7, 2007
"Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values."

Post mySQL 5.0.3, if you are stuck with trailing whitespace in a VARCHAR column, you can remove it through a two step process:

1) alter column type to char
2) alter column type back to varchar.

  Posted by Philip Gollucci on September 15, 2008
in regards to the above (stripping trailing whitespace)

update table t
set c = rtrim(c);

or you can use trim(), or ltrim()

  Posted by Michał Sierzchuła on July 21, 2009
When I try to save IP of visitor to VARCHAR (12) I noticed that, saving IP with dots and ending 0, like is writed like FLOAT without 0 on end of number,

It takes me 8h to resolve this simple problem ;) and save value in other type :)
  Posted by Andrew Deighton on July 22, 2009
In reply to Michał Sierzchuła above:
This is because you have 13 characters you are trying to put into a varchar(12) field, not because of the trailing 0. You need 15 characters to store an IP address, 12 digits and 3 dots.
  Posted by Alex Palmer on July 23, 2009
Yes, you do need a maximum of 15 characters to store an IP address as a string.
Alternatively you can save a lot of space by storing your IPs as 4-byte unsigned integers. Then use ip2long(), long2ip() string conversions in your application layer.
  Posted by Matt Simerson on September 11, 2009
The best way to store an IP addresses in a RDBMS is by converting it into an INT.
MySQL is especially nice because it will do the conversion between INT and
dotted quad for you. See the INET_ATON and INET_NTOA functions. You'll find
queries like this all over in my code:
SELECT INET_NTOA(ip) from ips;
  Posted by Bill Meier on June 18, 2012
Note that if you use the built in IP functions the column must be declared as UNSIGNED INT so IP addresses and up are handled correctly.
Sign Up Login You must be logged in to post a comment.