When MySQL knows best

When creating or altering a table, occasionally you will find that the exact specification you have asked for will not be put into practice, or that seemingly strange things will happen. For example, consider the following "conversation" between myself and the MySQL monitor:

mysql> DESC mblist;
+-------------+------------+
| Field       | Type       |
+-------------+------------+
| ID          | int(11)    |
| Name        | char(255)  |
| Password    | char(40)   |
| Filtered    | tinyint(4) |
| Description | char(255)  |
+-------------+------------+
5 rows in set (0.05 sec)

mysql> ALTER TABLE mblist ADD mytest VARCHAR(255);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> DESC mblist;
+-------------+--------------+
| Field       | Type         |
+-------------+--------------+
| ID          | int(11)      |
| Name        | varchar(255) |
| Password    | varchar(40)  |
| Filtered    | tinyint(4)   |
| Description | varchar(255) |
| mytest      | varchar(255) |
+-------------+--------------+
6 rows in set (0.00 sec)

First, notice I request a description of the table mblist. As you can see it has five fields, of which three are fixed-length char fields and two are integers. To make things easier to read I have removed several of the fields from the DESC output, so that you only see what is important.

Now, I execute a command to alter the mblist table by adding a variable-length character field, my test, to it. All well and good, MySQL has reported that the query is OK. However, take a close look at the field types that are output the second time I execute the DESC command - all those CHAR fields have now become VARCHAR fields, which are variable-length rather than fixed-length. Confusing, huh?

Well, one of the reasons MySQL is such a fast database system is because it has a great deal of logic built in to help it keep everything optimised. In the case of fixed-length and variable-length fields, MySQL is programmed to know that if any one field in a table is variable-length, the entire table is marked as variable-length. As a result of this, using CHAR as opposed to VARCHAR is no longer faster, and simply takes up more space, so MySQL converts all CHAR fields to VARCHAR for you.

This situation extends to further operations on dynamic tables, for example this next conversation comes immediately after the previous one:

mysql> ALTER TABLE mblist ADD fixedfield CHAR(20);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> DESC mblist;
+-------------+--------------+
| Field       | Type         |
+-------------+--------------+
| ID          | int(11)      |
| Name        | varchar(255) |
| Password    | varchar(40)  |
| Filtered    | tinyint(4)   |
| Description | varchar(255) |
| mytest      | varchar(255) |
| fixedfield  | varchar(20)  |
+-------------+--------------+
7 rows in set (0.00 sec)

Here I have requested that MySQL add a fixed-length field to a table with a variable-length row format, and MySQL has again silently overruled me and created a variable-length field. It is important to note that MySQL will not destructively overrule you - that is, it will not substitute one type for another if it will result in any loss of data or if it needs to be treated differently by you.

The exception to this rule is another situation where MySQL will overrule your decision, and that is when you specify a VARCHAR field that is of size 3 or under. In this situation the space saving offered by VARCHAR is basically irrelevant, so MySQL will convert the field to CHAR(3) even if the table has a variable-length row format.

Finally, specifying a table as CHAR(256)/VARCHAR(256) or larger will automatically have their field silently converted to the TEXT data type.

 

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Persistent connections >>

Previous chapter: Picking the perfect data type

Jump to:

 

Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.