Percona Live MySQL User's Conference, San Francisco, April 10-12th, 2012 Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Tuesday, May 04, 2010

MySQL: The maximum value of an integer

Did you ever have the need to find the maximum value of an integer in MySQL? Yeah, me neither. Anyway, some people seem to need this, and this is what I came up with:

SELECT ~0 as max_bigint_unsigned
, ~0 >> 32 AS max_int_unsigned
, ~0 >> 40 AS max_mediumint_unsigned
, ~0 >> 48 AS max_smallint_unsigned
, ~0 >> 56 AS max_tinyint_unsigned
, ~0 >> 1 AS max_bigint_signed
, ~0 >> 33 AS max_int_signed
, ~0 >> 41 AS max_mediumint_signed
, ~0 >> 49 AS max_smallint_signed
, ~0 >> 57 AS max_tinyint_signed
\G

*************************** 1. row ***************************
max_bigint_unsigned: 18446744073709551615
max_int_unsigned: 4294967295
max_mediumint_unsigned: 16777215
max_smallint_unsigned: 65535
max_tinyint_unsigned: 255
max_bigint_signed: 9223372036854775807
max_int_signed: 2147483647
max_mediumint_signed: 8388607
max_smallint_signed: 32767
max_tinyint_signed: 127
1 row in set (0.00 sec)

In case you're wondering how it works, read up on what the tilde (~) does: it peforms a bitwise negation. In other words, if flips bits that are 1 to 0, and vice versa. So, ~0 means, set all the bits to 1, because in the integer one 0, all the bits are a binary 0. Now, in MySQL, at runtime, there is only one integer type, which is an 8-byte integer value or a bigint. Therefore, ~0 is by definition the largest possible integer value.

MySQL defines a family of integer types for storage: bigint (8 bytes), int (4 bytes), mediumint (3 bytes), smallint (2 bytes) and tinyint (1 byte). To find the maximum values of those types, we can use the right-bitshift operator >> to push the most-significant bits at the left side of ~0 down to the right, for the appropriate number of bytes to get the maximum values of the other integer flavors. So,

int type: big int medium small tiny
bit #: 64 56 48 40 32 24 16 8 1
~0 = 11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 = 18446744073709551615
~0 >> 32 = 00000000 00000000 00000000 00000000 11111111 11111111 11111111 11111111 = 4294967295
~0 >> 40 = 00000000 00000000 00000000 00000000 00000000 11111111 11111111 11111111 = 16777215
~0 >> 48 = 00000000 00000000 00000000 00000000 00000000 00000000 11111111 11111111 = 65535
~0 >> 56 = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 11111111 = 255

Now, for each of the integer flavors, MySQL lets you define them to be either signed or unsigned. This is implemented using a so-called sign bit. The sign bit is the most significant bit (so, bit #64 in a bigint, bit #32 in an int, and so on and so forth). If the sign bit equals 0, the integer is positive and if it equals 1, the integer is negative. So, to get the maximum values for the signed integer flavors, we can use the same recipe, we just need to push the bits even one more bit to the right, like so:

int type: big int medium small tiny
bit #: 64 56 48 40 32 24 16 8 1
~0 >> 1 = 01111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 = 9223372036854775807
~0 >> 33 = 00000000 00000000 00000000 00000000 01111111 11111111 11111111 11111111 = 2147483647
~0 >> 41 = 00000000 00000000 00000000 00000000 00000000 01111111 11111111 11111111 = 8388607
~0 >> 49 = 00000000 00000000 00000000 00000000 00000000 00000000 01111111 11111111 = 32767
~0 >> 57 = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 01111111 = 127

11 comments:

rudy said...

very nicely explained

;o)

Roland Bouman said...

Thanks rudy!

datacharmer said...

I always marvel at the clarity of your explanations! Well done!
Giuseppe

Fortxun said...

Roland,

You said "Now, in MySQL, at runtime there is only one integer type, which is a 8-byte integer value or a bigint."

Can you clarify what specific parts of MySQL need 64-bit integer for handling integer values of any length.

Roland Bouman said...

@datacharmer: thanks! You're doing a pretty good job yourself :)

@Fortxun: well, I should clarify that I am not an expert with regard to MySQL internals, and I don't know why MySQL "needs" this.

But it is like I wrote: you can verify for yourself by looking at the source code. In MySQL, the runtime representation of any value or expression is done with the Item class. You can find it in the source tree under sql/item.h and sql/item.cc. In item.h you will find class Item, from which all other specific item types are derived. In class Item, there are virtual functions for getting a representation of the item's value in any supported data type. For integer representation, there are just two methods:

virtual longlong val_int()=0;
inline ulonglong val_uint() { return (ulonglong) val_int(); }

Well, if you lookup longlong and ulonglong, you will find that these are 8 byte integers. So no matter how the items represent integers internally, when processing items (such as when applying an operator to them - operators are also represented as items), integer values are always represented as 8 byte integers.

Touch Typing said...

Great blog. Your style of making things simple is really awesome. Numbers were never my foray but now will keep visiting your blog.

Anonymous said...

too great you are, very nice explaination
bitwise shifting operation is nicely explained
Can you help on IBdata file on Mysql, how its increase with database size

Anonymous said...

hi Roland,

can u help me how to check load of Mysql Server and in which units its measure.

Roland Bouman said...

@Anonymous #1: thanks for the compliment! The IBdata file is used for storing InnoDB tables (and indexes). I couldn't tell you anything about it that you can't find in the manual, sorry.

@Anonymous #2: please check forums or irc channels. I suspect you'll need to ask a more specific question to get good answers though.

good luck.

Roland.

Thomas said...

very great explaination, exactly what I'm looking for. Thanks a lot

François said...

I'm one of these people that needed this value ;)
thanks for the hack.