在SQL中,如何获取整数的最大值? [英] In SQL how do I get the maximum value for an integer?
问题描述
我正在尝试从MySQL数据库中找出整数(有符号或无符号)的最大值.有没有办法从数据库本身中撤回这些信息?
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?
有没有我可以使用的内置常量或函数(标准SQL或MySQL专用).
Are there any built-in constants or functions I can use (either standard SQL or MySQL specific).
在 http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html 列出了值-但是数据库有一种方法可以告诉我.
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.
以下内容为我提供了MAX_BIGINT-我想要的是MAX_INT.
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
推荐答案
在Mysql中,有一个便宜的技巧可以做到这一点:
In Mysql there is a cheap trick to do this:
mysql> select ~0;
+----------------------+
| ~0 |
+----------------------+
| 18446744073709551615 |
+----------------------+
波浪号是按位取反.结果值是bigint.请参阅: http://dev.mysql.com /doc/refman/5.1/en/bit-functions.html#operator_bitwise-invert
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)
这篇关于在SQL中,如何获取整数的最大值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!