While I was optimizing the database for the application I am currently developing, I thoroughly check the differences between these two types, VAR and VARCHAR, so might as well share my notes here. Keep in mind that these are for MySQL 5.0.3 and later, things were different in certain cases before.
CHAR
- string, length from 0 to 255
- required storage depends on the set length
- values are right-padded with spaces to the specified length when stored
- trailing spaces are removed when values are retrieved
- values that exceeds the specified length are truncated, warning is generated (strict SQL mode disabled, otherwise error and not stored)
VARCHAR
- string, length from 0 to 65,535 (but subject to maximum row size which is shared among all columns)
- required storage depends on the stored value
- values are not padded when stored
- trailing spaces are retained when values are stored or retrieved
- values that exceeds the specified length are truncated, warning is generated (strict SQL mode disabled, otherwise error and not stored)
Finally, a couple examples to illustrate it all:
mysql > CREATE DATABASE sandbox;
mysql > USE sandbox;
mysql > CREATE TABLE vc (`c` char(3), `v` char(3));
mysql > INSERT INTO vc VALUES ('', ''), (' ', ' '), ('a', 'a'), ('a ', 'a '), ('abc', 'abc');
mysql > SELECT CONCAT('(', c, ')'), CONCAT('(', v, ')');
That last query will return:
+---------------------+---------------------+
| CONCAT('(', c, ')') | CONCAT('(', v, ')') |
+---------------------+---------------------+
| () | () |
| () | ( ) |
| (a) | (a) |
| (a) | (a ) |
| (abc) | (abc) |
+---------------------+---------------------+
Notice how for CHAR the trailing space is removed. Another interesting thing to note, sorting and comparing CHAR and VARCHAR columns:
mysql > SELECT c = 'a ', v = 'a ' FROM vc;
+---------------------+
| c = 'a ' | v = 'a ' |
+---------------------+
| 0 | 0 |
| 0 | 0 |
| 1 | 1 |
| 1 | 1 |
| 0 | 0 |
+---------------------+
As you can see, it compares values without regard to any trailing spaces.
There, that should resume it all and hopefully help next time there is a field type to set. Did I forget to mention anything? Let me know in the comments.
