In SQL how do I get the maximum value for an integer?

Question

I am trying to find out the maximum value for an integer (signed or unsigned) from a MySQL database. Is there a way to pull back this information from the database itself?

Are there any built-in constants or functions I can use (either standard SQL or MySQL specific).

At http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html it lists the values - but is there a way for the database to tell me.

The following gives me the MAX_BIGINT - what I'd like is the MAX_INT.

SELECT CAST( 99999999999999999999999 AS SIGNED ) as max_int; # max_int | 9223372036854775807 

Answer 1

In Mysql there is a cheap trick to do this:

mysql> select ~0; +----------------------+ | ~0                   | +----------------------+ | 18446744073709551615 | +----------------------+ 

the tilde is the bitwise negation. The resulting value is a bigint. See: http://dev.mysql.com/doc/refman/5.1/en/bit-functions.html#operator_bitwise-invert

For the other integer flavours, you can use the right bitshift operator >> like so:

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)