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,

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:

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

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.