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