The CHAR
and
VARCHAR
types are similar, but
differ in the way they are stored and retrieved.
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. (Before MySQL 4.1, the length is interpreted
as number of bytes.)
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. (Before MySQL 3.23,
the length of CHAR
may be from 1
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.
Values in VARCHAR
columns are
variable-length strings. The length can be specified as a value
from 1 to 255 before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.
In contrast to CHAR
,
VARCHAR
values are stored as a
one-byte length prefix plus data. The length prefix indicates
the number of bytes in the value.
If you assign a value to a CHAR
or VARCHAR
column that exceeds
the column's maximum length, the value is truncated to fit. If
the truncated characters are not spaces, a warning is generated.
VARCHAR
values are not padded
when they are stored. Trailing spaces in MySQL version up to and
including 4.1 are removed from values when stored in a
VARCHAR
column; this also means
that the spaces are absent from retrieved values.
If you need a data type for which trailing spaces are not
removed, consider using a BLOB
or
TEXT
type. If you want to store
binary values such as results from an encryption or compression
function that might contain arbitrary byte values, use a
BLOB
column rather than a
CHAR
or
VARCHAR
column, to avoid
potential problems with trailing space removal that would change
data values.
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 |
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.
As of MySQL 4.1, values in CHAR
and VARCHAR
columns are sorted
and compared according to the character set collation assigned
to the column. Before MySQL 4.1, sorting and comparison are
based on the collation of the server character set; you can
declare the column with the BINARY
attribute
to cause sorting and comparison to be based on the numeric
values of the bytes in column values. BINARY
does not affect how column values are stored or retrieved.
All MySQL collations are of type PADSPACE
.
This means that all CHAR
and
VARCHAR
values in MySQL are
compared without regard to any trailing spaces. For example:
mysql>CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec) mysql>INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec) mysql>SELECT myname = 'Monty ', yourname = 'Monty ' FROM names;
+--------------------+----------------------+ | myname = 'Monty ' | yourname = 'Monty ' | +--------------------+----------------------+ | 1 | 1 | +--------------------+----------------------+ 1 row in set (0.00 sec)
This is true for all MySQL versions, and it is not affected by
the trimming of trailing spaces from
VARCHAR
values before storing
them. Nor does the server SQL mode make any difference in this
regard.
For more information about MySQL character sets and collations, see Section 9.1, “Character Set Support”.
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.
The BINARY
attribute is sticky. This means
that if a column marked BINARY
is used in an
expression, the whole expression is treated as a
BINARY
value.
MySQL may silently change the type of a
CHAR
or
VARCHAR
column at table creation
time. See Section 12.1.5.1, “Silent Column Specification Changes”.
User Comments
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.
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).
"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.
in regards to the above (stripping trailing whitespace)
update table t
set c = rtrim(c);
or you can use trim(), or ltrim()
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 :)
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.
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.
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;
or
INSERT INTO ips SET ip=INET_ATON('1.1.1.1');
Add your own comment.