INTEGER datatype in MySQL

I’ve spent the week away from work, taking a MySQL DBA course here in DC and have learned a lot about things I’d never even seen and things that I had misconceptions about.  One of those misconceptions that I bet a lot of other developers share is regarding the INTEGER/INT datatype in MySQL.

When creating a new column, I have always put the max number of digits in parentheses after INTEGER.  For example, if I didn’t expect a particular field to ever be more than 9,999 (in value), I would create the column as INTEGER(4).  This is dead wrong, and according to my teacher quite a lot of people do this.  The number after INTEGER is actually the minimum number of characters that you want to show if you have  ZEROFILL turned on.

For example:

CREATE TABLE myintegers (theints INTEGER(4) ZEROFILL);

INSERT INTO myintegers VALUES (3),(333),(33333),(3333333);

SELECT theints FROM myintegers;

will return

In actuality, if you don’t ever plan on a column going above 4 digits long, you should use a small int datatype.  Not only is it more specific to what you want, but it results in only 2 bytes of storage space being allocated versus 8 for an INT.  On a high performance database, this can make a big difference.

Finally, while I’m on the subject, I thought I’d mention the UNSIGNED option.  Adding the UNSIGNED option tells the storage engine that there can be no negative numbers.  Because negative numbers are disallowed, your upper limit for the value of your column is twice the value minus 1 what it would be if you allowed negatives.  A lot of you my be saying ‘of course’, but that was one of those things I always wondered about and never bothered to look up.

The storage space and lower/upper limits for MySQL integer (lowercase) data can be found here.