PostgreSQL 64位数字按位位移运算符结果异常问题求解

问题原因 #

你遇到的返回负数的问题是因为当前SQL环境下的位移操作默认基于32位有符号整数实现，最高位是符号位，1左移31位后触发了符号位溢出，所以得到负数结果。

可行解决方案 #

方案1：显式转换为64位数值类型后再做位移 #

不同数据库的转换语法略有差异，核心是将参与位移的操作数转换为64位整数类型，避免32位溢出：

• MySQL/MariaDB 写法：

select
  (CAST(1 AS UNSIGNED) << 1)  as ok_1,
  (CAST(1 AS UNSIGNED) << 2)  as ok_2,
  -- ...
  (CAST(1 AS UNSIGNED) << 30)  as ok_30,
  (CAST(1 AS UNSIGNED) << 31)  as ok_31

转换为UNSIGNED无符号类型后，最高可支持左移63位，得到64位无符号整数结果。

• PostgreSQL 写法：

select
  (1::bigint << 1)  as ok_1,
  (1::bigint << 2)  as ok_2,
  -- ...
  (1::bigint << 30)  as ok_30,
  (1::bigint << 31)  as ok_31

bigint为64位有符号整数，最高支持左移62位，若需要无符号64位结果可配合bit(64)类型处理。

• SQL Server 写法：

select
  (CAST(1 AS BIGINT) << 1)  as ok_1,
  (CAST(1 AS BIGINT) << 2)  as ok_2,
  -- ...
  (CAST(1 AS BIGINT) << 30)  as ok_30,
  (CAST(1 AS BIGINT) << 31)  as ok_31

方案2：用数学计算替代位移操作（全库兼容） #

左移n位的数学本质是乘以2的n次方，该方法不受内置位移操作的位数限制，兼容性最高：

select
  pow(2,1) as ok_1,
  pow(2,2) as ok_2,
  -- ...
  pow(2,30) as ok_30,
  pow(2,31) as ok_31,
  pow(2,63) as ok_63

如果需要实现右移操作，对应替换为整数除法除以2的n次方即可。

内容的提问来源于stack exchange，提问作者Mitko Keckaroski