SELECT CAST(POWER(2.0,55) AS BIGINT) 36028797018963968
SELECT CAST(POWER(2.0,56) AS BIGINT) 72057594037927936
SELECT CAST(POWER(2.0,57) AS BIGINT) 288230376151711740
SELECT CAST(POWER(2.0,58) AS BIGINT) 576460752303423490
……
SELECT CAST(POWER(2.0,56) AS BIGINT) 72057594037927936
SELECT CAST(POWER(2.0,57) AS BIGINT) 288230376151711740
SELECT CAST(POWER(2.0,58) AS BIGINT) 576460752303423490
……
SELECT CAST(POWER(2.0,63) AS BIGINT)
SELECT CAST(POWER(2.0,64) AS BIGINT) /*
--------------------
9223372036854775800(所影响的行数为 1 行)服务器: 消息 8115,级别 16,状态 2,行 2
将 expression 转换为数据类型 bigint 时发生算术溢出错误。
*/
SELECT CAST(POWER(2.0,55) AS BIGINT) 36028797018963968 --结果正确!
SELECT CAST(POWER(2.0,56) AS BIGINT) 72057594037927936 --结果正确!
SELECT CAST(POWER(2.0,57) AS BIGINT) 288230376151711740 --结果错误!
SELECT CAST(POWER(2.0,58) AS BIGINT) 576460752303423490 --结果错误!……
SELECT CAST(POWER(2.0,57) AS BIGINT)
/*
--------------------
144115188075855870(所影响的行数为 1 行)
*/
SELECT CAST(POWER(2.0,58) AS BIGINT)
/*
--------------------
288230376151711740(所影响的行数为 1 行)
*/ 我的OK啊
整数
bigint
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字)。
/*
--------------------
144115188075855870为什么从2^57开始,结果多了一个0?
正确结果应该是:14411518807585587
144115188075855870结果根本是错的,应该是:144115188075855872。
2> SELECT CAST(POWER(2.0,56) AS BIGINT)
3> SELECT CAST(POWER(2.0,57) AS BIGINT)
4> SELECT CAST(POWER(2.0,58) AS BIGINT)
5> SELECT CAST(POWER(2.0,59) AS BIGINT)
6> SELECT CAST(POWER(2.0,60) AS BIGINT)
7> SELECT CAST(POWER(2.0,61) AS BIGINT)
8> SELECT CAST(POWER(2.0,62) AS BIGINT)
9> SELECT CAST(POWER(2.0,63) AS BIGINT)
10> SELECT CAST(POWER(2.0,64) AS BIGINT)
11> go --------------------
36028797018963968(1 行受到影响) --------------------
72057594037927936(1 行受到影响) --------------------
144115188075855870(1 行受到影响) --------------------
288230376151711740(1 行受到影响) --------------------
576460752303423490(1 行受到影响) --------------------
1152921504606847000(1 行受到影响) --------------------
2305843009213694000(1 行受到影响) --------------------
4611686018427387900(1 行受到影响) --------------------
9223372036854775800(1 行受到影响)
消息 8115,级别 16,状态 2,服务器 NB-MASTER,行 10
将 expression 转换为数据类型 bigint 时发生算术溢出错误。后面为 0 的结果都是错的。
--9223372036854775799--正确结果 9223372036854775807从float转化成BIGINT 肯定会丢失
SELECT CAST(POWER(2.0,63) AS NVARCHAR(4000))
这样应该不会丢失了吧?结果一样是错的。
--------------------
144115188075855872(所影响的行数为 1 行)
**/
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,55) AS BIGINT)--正确结果:36028797018963968
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,56) AS BIGINT)--正确结果:72057594037927936
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,57) AS BIGINT)--正确结果:144115188075855872
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,58) AS BIGINT)--正确结果:288230376151711744
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,59) AS BIGINT)--正确结果:576460752303423488
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,60) AS BIGINT)--正确结果:1152921504606846976
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,61) AS BIGINT)--正确结果:2305843009213693952
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,62) AS BIGINT)--正确结果:4611686018427387904
INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,63) AS BIGINT)--正确结果:9223372036854775808
--INSERT @TempTBL(oValue)SELECT CAST(POWER(2.0,64) AS BIGINT)--正确结果:18446744073709551616
SELECT * FROM @TempTBL执行后结果如下:36028797018963968
72057594037927936
144115188075855870
288230376151711740
576460752303423490
1152921504606847000
2305843009213694000
4611686018427387900
9223372036854775800