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 0000000011111111 11111111 11111111 11111111 = 4294967295

~0 >>40=00000000 00000000 00000000 00000000 0000000011111111 11111111 11111111 = 16777215

~0 >>48=00000000 00000000 00000000 00000000 00000000 0000000011111111 11111111 = 65535

~0 >>56=00000000 00000000 00000000 00000000 00000000 00000000 0000000011111111 = 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 0000000001111111 11111111 11111111 11111111 = 2147483647

~0 >> 41 = 00000000 00000000 00000000 00000000 0000000001111111 11111111 11111111 = 8388607

~0 >> 49 = 00000000 00000000 00000000 00000000 00000000 0000000001111111 11111111 = 32767

~0 >> 57 = 00000000 00000000 00000000 00000000 00000000 00000000 0000000001111111 = 127

## 12 comments:

very nicely explained

;o)

Thanks rudy!

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

Giuseppe

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.

@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.

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

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

hi Roland,

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

@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.

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

I'm one of these people that needed this value ;)

thanks for the hack.

I needed it as well. Google give very large ID's for their users.

Post a Comment