12.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.10.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 6.1.8, “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).

Value

CHAR(4)

Storage Required

VARCHAR(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.

InnoDB encodes fixed-length fields greater
than or equal to 768 bytes in length as variable-length fields,
which can be stored off-page. For example, a
CHAR(255) column can exceed 768 bytes if the
maximum byte length of the character set is greater than 3, as
it is with utf8mb4.

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:

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:

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.

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 char2) alter column type back to varchar.

Posted by
Philip Gollucci
on
September 15, 2008

in regards to the above (stripping trailing whitespace)

update table tset 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 87.10.231.110 is writed like FLOAT without 0 on end of number, 87.10.231.11

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 findqueries like this all over in my code:SELECT INET_NTOA(ip) from ips; orINSERT INTO ips SET ip=INET_ATON('1.1.1.1');

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 128.0.0.0 and up are handled correctly.

Posted by
Alfonso Baqueiro Bernal
on
April 27, 2016

Best way to save IP info is as binary. IPv4 use 32 bits (4 bytes) and IPv6 uses 128 bits (16 bytes)

create your ip field as:

varbinary(16)

to allocate both, IPv4 and IPv6 addresses. It will use 4 or 16 bytes according to the IP being saved.

// Like the data is loaded in binary from the PDO driver into a variable, so hex2bin is not a step required$r = $qr->fetch(PDO::FETCHNUM);// $r contains the record, $r[0] is the ip field in binary// we need to convert from binary to human representation 127.0.0.1 using inet_ntop$IP = inet_ntop($r[0]);