Watch out for calculations with MySQL unsigned integers

Unsigned integers are a useful column type in MySQL if you want to make your tables as small as possible. Making tables small is a good idea because at the end of the day, database access is limited by hard drive access speed and if there is less data to read, you will access it faster.

A signed MEDIUMINT has the range -8388608 to 8388607, whereas an UNSIGNED MEDIUMINT goes from 0 to 16777215. If you are sure your number is NEVER going to be negative, then you can save 1 Byte per row by using an UNSIGNED MEDIUMINT instead of an INT.

However – do make sure that any calculations you do in your SQL never involve negative numbers.

Lets set up a simple example, a table with signed and unsigned columns:

CREATE TABLE  `temp` (
`id` int(10) unsigned NOT NULL auto_increment,
`myUnsigned` mediumint(9) NOT NULL,
`mySigned` mediumint(8) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `temp` (myUnsigned,mySigned) VALUES (1,1);

Now here’s a simple calculation:

SELECT myUnsigned – 10, mySigned – 10 FROM `temp`;

And the results:

myUnsigned  – 10 = -9
mySigned – 10 = 18446744073709551607

I would prefer it if MySQL could issue some kind of error and fail, but instead it produces this stupidly high number. Watch Out!