MySQL: The maximum value of an integer

[Latest News Headlines]

Did you ever have a 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. So, ~0 means, set all the bits to 1 (since in 0, all the bits are 0). Now, in MySQL, at runtime there is only one integer type, which is a 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 have 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 defined 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), and if it is 0, the integer is positive, if it is 1 the number 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



More from Roland Bouman's Blog ...