You may have heard recently that Psy's "Gangum Style" video "broke" YouTube due to the database using an signed integer that was 32 bits, never expecting a single video to ger more than 2.147 trillon views. So they upped it to a 64 bit integer. interestingly enough, they still left it signed, despite unsigned giving them a significantly greater range and the fact that no video can ever have NEGATIVE views – people can't "unwatch" stuff no matter how much they might want to.
(and yes, I love that video still and added to its continuing to grow views LOL)
Anyway, in our more recent DB designs, we've been taking greater care to explicitly declare if an integer should be signed/unsigned. While working on one such schema and creating the documents necessary to map the old schema to the new, I noticed the old tables were displaying a slightly different had a different INT than the new. Namely INTEGER fields were showing as INT(11) while in the new they came across as INT(10).
Curious, I decided to test the first (and only real) difference in the schema of the two – declaring the integer signed or unsigned. Running this quick test table creation statement…
CREATE TABLE a ( int_shortdec INT, int_undeclared INTEGER, int_signed INTEGER SIGNED, int_unsigned INTEGER UNSIGNED );
…confirmed my suspicions:
At first it was a little confusing (admittedly, I'm tired as crap and I've only had one cup of coffee and Sprite which has no caffeine), then my brain clicked on. Remember the number in the parenthesis is the width of the field. Since the max number on an unsigned integer is 4,294,967,295, the extra character isn't needed. While a signed integer needs it to hold the potential negative sign. Kudos to MySQL for smart defaults 🙂
Since I could see myself being thrown for a loop by this when I was still new to DB design (or am coffee deprived), I figured I'd throw up a post in case anyone else wondered.
BTW, you can set the width on that unsigned integer to 11, but with the max value, it would be pointless.