在PostgreSQL中，如何将bytea表示为一个整数？

Question

我有一个包含14个字节数据的bytea列。这14个字节的最后3个字节包含数据的CRC码。我想将CRC提取为一个整数，并存储在一个新列中。

我该如何操作？

为了澄清，在Java中实现这一点的一种方法是：

int crc = ((rawData[len - 3] & 0xff) << 16 |
            (rawData[len - 2] & 0xff) << 8 |
            (rawData[len - 1] & 0xff)) & 0xffffff;

我希望能找到一种不需要位移操作的解决方案，即像一个方法接受4个字节并将它们转换为整数的方式。

- Zoltán

Answer 1

另一种方法是提取十六进制表示法中的最后6个字符，前置一个x并直接转换：

db=# SELECT ('x' || right('\x00000000000001'::bytea::text, 6))::bit(24)::int;
 int4
------
    1

这段代码使用的是比get_byte()方法更短的方式，但也是PostgreSQL的一个未记录特性。然而，我在这里引用Tom Lane的话:

这依赖于一些未记录的位类型输入转换器行为，但我认为没有理由期望它会出错。一个可能更大的问题是它需要PG >= 8.3，因为在此之前没有文本到位的转换。

有关详细信息，请参见相关答案：

• 将文本表示中的十六进制转换为十进制数

这假设您的bytea_output设置为hex，自9.0版本以来默认为此设置。为确保正确，您可以测试/设置您的会话：

SET bytea_output = 'hex';

更多内容请查看:

• PostgreSQL 9.X中用于缩略图的bytea表示方式为'hex'或'escape'

性能

我在一个有10k行的表上进行了一次测试（取10次最佳结果）。实际上，在Postgres 9.1中，get_byte()速度更快:

CREATE TEMP TABLE t (a bytea);
INSERT INTO t
SELECT (12345670000000 + generate_series(1,10000))::text::bytea;

位移操作与乘法/加法一样快:

SELECT 
 ('x' || right(a::text, 6))::bit(24)::int                           -- 34.9 ms
,(get_byte(a, 11) << 16) + (get_byte(a, 12) << 8) + get_byte(a, 13) -- 27.0 ms
,(get_byte(a, 11) << 16) | (get_byte(a, 12) << 8) | get_byte(a, 13) -- 27.1 ms
, get_byte(a, 11) * 65536 + get_byte(a, 12) * 256 + get_byte(a, 13) -- 27.1 ms
FROM t

- Erwin Brandstetter

---

@CraigRinger：有时候，黑暗面的诱惑力就是太强大了。 :) - Erwin Brandstetter

使用位移操作是否比乘法更高效呢？请看我的回答。 - Zoltán

@Zoltán：差不多。我添加了一个测试。 - Erwin Brandstetter

确实。我现在也测试了一下。我接受你的答案，因为它提供了一种不需要逐字节操作并展示其低效性的解决方案。 - Zoltán

@Zoltán：仅仅三个字节的情况下，强制类型转换会稍微慢一些。但是对于更多的字节（整数最多4个字节，大整数最多8个字节），它开始胜出，因为其性能是恒定的，而三种替代方法的性能则会恶化。 - Erwin Brandstetter

Answer 2

select get_byte(b, 11) * 65536 + get_byte(b, 12) * 256 + get_byte(b, 13)
from (values ('12345678901234'::bytea)) s(b);
 ?column? 
----------
  3289908

- Clodoaldo Neto

---

你的 bytea 示例只有 13 个字节长。 - Erwin Brandstetter

我在想为什么你的计算结果不同。你打错了：2^16 = 65536，而不是65356。 - Erwin Brandstetter

Answer 3

如果我们要进行字节级操作，那么位移比乘法更加高效。

根据Clodoaldo Neto的回答，我认为：

select (get_byte(arm_data, 11) << 16) |
       (get_byte(arm_data, 12) << 8) |
       (get_byte(arm_data, 13))
            from adsb_raw_message;

大家都同意吗？

- Zoltán

---

虽然不是很高效，但我认为更加易读的是位操作。 - Diamondo25

Answer 4

如果您想将CRC作为单个整数存储在一个独立的列中，我建议在插入或更新时进行转换；然后与bytea的值一起持久化。

您可以在应用程序/业务层中执行此操作，也可以使用插入/更新触发器来填充CRC列。

- Beryllium

---

是的，自从这个更改被实施以来，我正在业务层中进行此操作，但是数据库已经包含了一个有价值的数据集，因此我希望创建一个数据库迁移脚本，该脚本将把CRC提取到新列中。 - Zoltán