CHAR() vs. VARCHAR()

So, a little gotcha:

The CHAR() and VARCHAR() types are different types. MySQL silently converts any CHAR() fields to VARCHAR() when creating a table with at least 1 VARCHAR() field.

http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html

If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster.

However, that’s not entirely accurate. Because according to the manual page at http://dev.mysql.com/doc/refman/5.0/en/char.html:

As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. 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.

If you have a field such as name, and require it to not be blank, you probably have some function testing it before it goes into the database. However, most languages are perfectly happy that ” ” isn’t blank. When it gets put into the database, however, it becomes blank if your column is a VARCHAR. Which means folks may be able to get beyond your requirement of a blank field, and actually store a blank field in the database (as opposed to storing a space or series of spaces).

One Response to CHAR() vs. VARCHAR()

  1. Hi,

    I believe this paragraph from the Silent Column Specification Changes page is no longer accurate. I’ve asked the MySQL Documentation team to take a look at it and fix as necessary. Thanks for pointing this out!

    mysql 4.0.27-max (root) [test]> create table t1 (v varchar(10), c char(10));
    Query OK, 0 rows affected (0.01 sec)

    mysql 4.0.27-max (root) [test]> show create table t1\G
    *************************** 1. row ***************************
    Table: t1
    Create Table: CREATE TABLE `t1` (
    `v` varchar(10) default NULL,
    `c` varchar(10) default NULL
    ) TYPE=MyISAM
    1 row in set (0.00 sec)

    mysql 4.0.27-max (root) [test]> insert into t1 values (‘a’,’a’),(‘b ‘,’b ‘),(”,”),(‘ ‘,’ ‘);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4 Duplicates: 0 Warnings: 0

    mysql 4.0.27-max (root) [test]> select concat(‘>’,v,”,c,”,v,”,c,’aabb create table t1 (v varchar(10), c char(10));
    Query OK, 0 rows affected (0.01 sec)

    mysql 5.0.32-enterprise-gpl-log (root) [test]> show create table t1\G
    *************************** 1. row ***************************
    Table: t1
    Create Table: CREATE TABLE `t1` (
    `v` varchar(10) default NULL,
    `c` char(10) default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    mysql 5.0.32-enterprise-gpl-log (root) [test]> insert into t1 values (‘a’,’a’),(‘b ‘,’b ‘),(”,”),(‘ ‘,’ ‘);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4 Duplicates: 0 Warnings: 0

    mysql 5.0.32-enterprise-gpl-log (root) [test]> select concat(‘>’,v,”,c,”,v,”,c,’aab b