Systems for MySQLThe world's most popular open source database
- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 10.4 String Types
- 10.4.1 The CHAR and VARCHAR Types
- 10.4.2 The BINARY and VARBINARY Types
- 10.4.3 The BLOB and TEXT Types
- 10.4.4 The ENUM Type
- 10.4.5 The SET Type
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 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
Section 7.4.2, linked here: http://dev.mysql.com/doc/mysql/en/Data_size.html
says to use varchar if you want to keep table size down and char if you want to make searches faster (fourth bullet down).
Of course, the first bullet says that the way to make things faster is to keep table size down.
In response to the above comment (about CHAR vs. VARCHAR performance), I think the explanation is that if your query can't operate solely with a table's indicies/keys (most large, complex queries can't) then table size is the primary factor for search speed, especially if the table is very large (if it's so large it has to be read off disk each time it is searched, then disk is almost certainly going to be the bottleneck, and the search speed will be directly proportional to the total data size). In addition, if the database is able to narrow down the query to a subset of rows using indicies/keys but then has to read in each row to check it against other factors, the total size is still a consideration, but so is the fact that if each row is of a variable size, finding the rows incurs some overhead.
Overall I have found that size is by far the most important factor in terms of performance so I always use VARCHAR over CHAR, however if you have a small and simple table you may want to consider using CHAR since it will make the database's job of finding rows and columns easier. If this table is likely to stay in RAM, using CHAR may provide an improvement in performance, since total size is no longer such an issue (although obviously the smaller your tables the less RAM is needed to cache them, but this applies mostly to your large table, small ones are usually inconsequential in such terms).
So I'd recommend using CHAR for performance reasons only in specific circumstances where you are CPU bound for relatively simple but frequent queries.
With regards to the speed discussion above... yes VARCHAR does keep the size of your database down, but that doesn't necessarily make it faster to search... because CHAR is null-padded, it makes all records the same length so that the database can just skip through the table without having to check the length of each record as it goes. In addition, queries are not performed on the null-padded part of the CHAR and so the actual number of bytes searched is no more than for a VARCHAR. There's trade off between disk time for CHAR and size-checking for VARCHAR, but to be honest I'm not sure it's noticeable on modern machines. So the only real concern is the amount of disk-space used. Personally I prefer not to waste disk space so I use VARCHAR.
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()
Add your own comment.