PostgreSQL --- 二进制数使用详解

（一）“二进制”概述

在 PostgreSQL 中，处理“二进制”数据通常涉及两个完全不同的概念：

一个是位串（Bit Strings），用于底层数据的按位操作；

一个是bytea数据类型，用于存储真正的原始字节流（如图片、文件等）。

以下是针对这两种类型的全面解析：

(1)、 位串类型（Bit Strings）

位串类型专门用于存储和操作由0和1组成的二进制数值序列。它分为固定长度的bit(n)和可变长度的varbit(n)。

1. 核心特性与转换规则

• 数值逻辑 vs 字符串逻辑：将整数转为bit(8)遵循的是数值逻辑（高位补零），而将一个较短的位串显式转换为较长的位串时，遵循的是字符串逻辑（右侧/低位补零）。
• 转回整数：使用::integer或CAST()可以安全地将位串还原为十进制整数。

2. 丰富的位运算支持

PostgreSQL 提供了完备的按位运算符，包括按位与 (&)、按位或 (|)、按位异或 (#)、按位取反 (~)，以及左移 (<<) 和右移 (>>)。在进行这些运算时，必须确保参与运算的两个位串长度一致。

(2)、 bytea 数据类型（原始字节流）

当需要存储程序员认为是“原始字节（raw bytes）”的数据（例如图像、音频、PDF文件或加密数据）时，应使用bytea类型。

1. 与普通文本字符串的区别

• 允许特殊字符：普通字符串不允许包含值为零的字节或其他不可打印的字节，而bytea完全支持。
• 不受区域设置影响：对文本字符串的处理取决于数据库的字符集编码和区域设置，而对bytea的操作纯粹是处理实际的物理字节。

2. 输入与输出格式

bytea支持两种外部表示格式：

• 十六进制格式（Hex，推荐）：这是自 PostgreSQL 9.0 引入的现代格式。它将每个字节编码为两个十六进制数字，并在整个字符串前加上\x作为标识符（例如\xDEADBEEF）。这种格式兼容性强且转换速度快，是新应用的首选。
• 转义格式（Escape，传统）：将不可打印的字节转换为以反斜杠开头的三位八进制值（如\000）。由于这种格式容易模糊二进制数据和文本字符串的界限，官方强烈建议在新应用中避免使用。

3. 常用函数与操作符

PostgreSQL 提供了一系列内置工具来操作bytea数据：

• 连接：使用||运算符可以将多个二进制字符串拼接在一起2。
• 长度计算：使用octet_length(string)返回二进制字符串的实际字节数2。
• 子串提取与替换：支持substring()、position()查找位置，以及overlay()进行字节级别的替换2。
• 编解码转换：可以使用encode(data, 'hex'/'base64')将二进制数据转为可读的文本字符串，或使用decode(text, format)将 Base64 等格式的文本还原为二进制数据6。

💡 最佳实践建议

1. 明确需求：如果是为了掩码、状态标志等数学运算，请使用bit/varbit；如果是为了存储文件附件或非结构化数据，请使用bytea。
2. 统一格式：在使用bytea时，保持默认的bytea_output = 'hex'配置，以确保最佳的跨平台兼容性和性能。
3. 注意类型匹配：在对bytea数据进行操作时，务必确保参与运算的值都是bytea类型，避免直接将普通的text字符串与其混合运算而导致报错。

（二），位串类型（Bit Strings）使用详解

(1)，整数转换为固定长度的位串

PostgreSQL 提供了强大的位串（Bit String）类型，可以通过将整数转换为固定长度的位串来实现这一需求。

以下是几种常用的转换方法：

1. 使用bit(n)进行固定长度转换

这是最标准且推荐的方法。可以将十进制整数显式转换为bit(n)类型，其中n是输出的二进制位数。

SELECT 10::bit(8); -- 返回结果: '00001010' (保留前导零)

2. 去除前导零

如果不需要补齐的前导零，希望看到最原始的二进制数值，可以结合LTRIM函数来去掉左侧多余的0：

SELECT LTRIM(10::bit(32)::text, '0'); -- 返回结果: '1010'

3. 自定义to_bin()函数

在 PostgreSQL 中，并没有像 MySQL 那样直接名为to_bin()或BIN()的内置函数。

如果希望在项目中频繁调用该功能，可以在数据库中创建一个自定义函数，之后就可以直接使用to_bin(数值)的方式调用了：

CREATE OR REPLACE FUNCTION to_bin(val integer) RETURNS text AS $$ BEGIN -- 保留64位长度，并使用 LTRIM 去掉多余的前导0 RETURN LTRIM(val::bit(64)::text, '0'); END; $$ LANGUAGE plpgsql; -- 测试使用 SELECT to_bin(42); -- 返回结果: '101010'

⚠️ 注意事项：

• 在进行转换时，请确保指定的bit(n)长度n足够容纳您的十进制数，否则高位数据会被截断。
• PostgreSQL 的标准库默认不支持直接将smallint(int2) 强制转换为bit类型。如果您的数字是int2类型，建议先将其转换为integer(int4) 或bigint，再进行bit转换2。

(2)，表示二进制数

在 PostgreSQL 中，“表示二进制数”通常分为两种情况：

一是表示二进制的数值（位串），

二是存储和处理真正的原始二进制数据（字节流）。

以下是具体的表示方法：

1. 使用B'...'语法表示二进制数值

如果需要在 SQL 语句中直接书写一个由0和1组成的二进制数值，

可以使用B'...'（或b'...'）的格式。

这种写法常用于位运算或赋值给位串类型（bit/varbit）。

SELECT B'10101010'; -- 返回结果: '10101010'

2. 使用\x前缀表示十六进制二进制数据

如果要向bytea（可变长度二进制字符串）字段插入真实的二进制数据（如图片、加密文件等），PostgreSQL 默认且推荐使用十六进制格式。该格式会将每个字节用两个十六进制数字表示，并在整个字符串前面加上\x作为前缀标识。

SELECT '\xDEADBEEF'::bytea; -- 返回结果: \xdeadbeef

注：除了十六进制格式外，PostgreSQL 还支持历史遗留的“转义格式”（将不可打印字符转换为\xxx形式的八进制值），但由于其容易与文本字符串混淆且处理笨拙，官方强烈建议在新应用中避免使用。

3. 使用字面量前缀进行十进制与二进制的转换标示

在较新的 PostgreSQL 版本中，支持直接使用带前缀的字面量来表示不同进制的整数，并可以将其转换为常规数据类型：

• 二进制标示 (0b)：例如'0b10101010'::int4（结果为十进制的 170）
• 十六进制标示 (0x)：例如'0xdecaf'::int4（结果为十进制的 912559）
• 八进制标示 (0o)：例如'0o666'::int4（结果为十进制的 438）

4. 内置函数转换输出为二进制字符串

如果将普通的十进制整数直接转换为以0和1组成的文本字符串，可以使用以下函数：

截止PostgreSQL 16还没有相关函数，可以自定义。

• to_bin()：在最新的 PostgreSQL 版本中引入，专门用于将整数转为二进制字符串。例如：select to_bin(1234567);会返回100101101011010000111。
• to_hex()：将整数转为十六进制字符串。例如：select to_hex(1234567);会返回12d687。

(3)，二进制数运算

在 PostgreSQL 中，对两个二进制数（位串）进行位运算非常直接。PostgreSQL 提供了一系列专门的按位运算符来支持这些操作。

假设有两个二进制数A = B'10101010'和B = B'11001100'，

以下是具体的位运算方法和示例：

1. 按位与 (&)

如果两个对应位都为 1，结果位才为 1；否则为 01。

SELECT B'10101010' & B'11001100'; -- 返回结果: '10001000'

2. 按位或 (|)

只要两个对应位中有一个为 1，结果位就为 11。

SELECT B'10101010' | B'11001100'; -- 返回结果: '11101110'

3. 按位异或 (#)

当两个对应位不同时，结果位为 1；相同时为 01。

SELECT B'10101010' # B'11001100'; -- 返回结果: '01100110'

4. 按位取反 (~)

这是一个一元运算符，将所有的 1 变为 0，所有的 0 变为 11。

SELECT ~B'10101010'; -- 返回结果: '01010101'

5. 位移运算 (<<左移 />>右移)

• 左移 (<<)：将所有位向左移动指定的位数，左边丢弃，右边补 01。
• 右移 (>>)：将所有位向右移动指定的位数，右边丢弃，正数左边补 0，负数左边补 11。

-- 左移 2 位 SELECT B'10101010' << 2; -- 返回结果: '10101000' -- 右移 2 位 SELECT B'10101010' >> 2; -- 返回结果: '00101010'

⚠️ 注意事项：
在进行位运算时，参与运算的两个二进制位串的长度必须相同。

如果长度不一致，PostgreSQL 会抛出错误。可以使用lpad函数手动补齐前导零，或者将其转换为相同长度的bit(n)类型后再进行运算。

(4)，“位串”与“整数”转换

在 PostgreSQL 中，将bit(8)转换回整数非常简单。使用类型转换操作符::或标准的CAST()函数来实现：

1. 使用::操作符（推荐，最简洁）

SELECT '00000011'::bit(8)::integer; -- 返回结果: 3

2. 使用CAST()函数

SELECT CAST('00000011'::bit(8) AS integer); -- 返回结果: 3

⚠️ 注意事项：

• 必须指定目标整数类型：如果只写'00000011'::bit(8)::bit，由于不指定长度时默认等同于bit(1)，它只会截取整数的最低位，导致结果变为1。因此，请务必显式转换为integer（或int4、bigint等）。
• 负数处理：PostgreSQL 同样支持包含符号位的二进制字符串转回整数。例如，cast('111111010100' as bit(12))::integer会正确还原为-44。

(5)，“整数转位串”和“位串长度转换”

现象分析：分析 select 3::bit(8), (B'11')::bit(8)； 结果： 00000011 11000000，为啥不一致？

这两个结果之所以不一致，是因为 PostgreSQL 在处理“整数转位串”和“位串长度转换”时，遵循了不同的底层规则：

1.3::bit(8)的结果是00000011（数值逻辑）

当一个十进制整数直接转换为bit(n)类型时，PostgreSQL 会将其视为数值的二进制表示。

• 数字 3 的二进制形式是11。
• 在扩展为 8 位长度时，系统会在左侧补齐前导零（非负数补0），以保留其原本的数值大小。
• 因此结果为00000011。

2.(B'11')::bit(8)的结果是11000000（字符串截断/填充逻辑）

当一个已经存在的位串常量进行显式长度转换时，PostgreSQL 遵循的是 SQL 标准的字符串处理规则：

• B'11'本身是一个只有两位的位串。
• 当显式将其转换为更长的bit(8)类型时，系统会在其右侧补齐零（即低位补零）。
• 因此，原本在最左边的11被保留在高位，后面补上六个0，最终结果变成11000000。

💡 总结与最佳实践

• 整型转位串：按数值逻辑转换，高位补零（如3::bit(8)->00000011）。
• 位串转位串：按字符串逻辑转换，右侧补零（如B'11'::bit(8)->11000000）。

如果希望将B'11'也当作数值来处理并得到00000011，可以先将其转为整数，再转为指定位数的位串。

SELECT (B'11'::bit(2)::integer)::bit(8); -- 可以不写B SELECT ('11'::bit(2)::integer)::bit(8); -- 返回结果: '00000011'

（三）， bytea 数据类型（原始字节流）使用详解

在 PostgreSQL 中，bytea（Byte Array）数据类型专门用于存储“原始字节流”（raw bytes），例如图像、音频、视频或加密数据。

与普通文本字符串不同，它允许存储零值字节和其他不可打印的字符，且处理过程不受数据库区域设置和字符集编码的影响。

以下是操作bytea数据类型的核心指南：

1. 数据的输入与输出格式

bytea支持两种外部表示格式，PostgreSQL 默认使用十六进制格式（Hex）：

• 十六进制格式（推荐）：将二进制数据编码为每字节两个十六进制数字，并在整个字符串前加上\x作为标识符。例如：\xDEADBEEF。这种格式兼容性强且转换速度快。
• 转义格式（传统）：将不可打印的字节转换为以反斜杠开头的三位八进制值（如\000）。由于容易模糊二进制数据和文本字符串的界限，官方建议在新应用中避免使用。

2. 基础 SQL 操作

创建表后，可以直接使用标准的 SQL 语句对bytea字段进行增删改查：

-- 1. 创建包含 bytea 字段的表 CREATE TABLE images ( id serial PRIMARY KEY, name varchar(50), data bytea ); -- 2. 插入二进制数据（使用 \x 语法） INSERT INTO images (name, data) VALUES ('image1', E'\\x89504e470d0a1a0a'); -- 3. 读取二进制数据 SELECT name, data FROM images; -- 4. 更新二进制数据 UPDATE images SET data = E'\\x776f726c6421' WHERE name = 'image1';

3. 查看明文与编解码转换

由于直接查询bytea字段通常会返回带有\x前缀的十六进制字符串，如果您需要查看其可读形式，可以使用以下内置函数：

• encode()函数：将二进制数据转换为指定的文本格式（如hex、base64或escape）。

  SELECT encode(data, 'hex') AS hex_text, encode(data, 'base64') AS base64_text FROM images; -- 输出 -- 776f726c6421 ， d29ybGQh

• convert_from()函数：如果bytea内部原本存储的是特定字符集的文本（如 UTF-8），可以将其正确解码为可读字符串。

  SELECT convert_from(data, 'UTF8') AS plain_text FROM images; -- 输出 -- world!

  注意：如果原始数据是图片等非文本内容，强行转为文本会显示乱码。

4. 常用内置函数与操作符

PostgreSQL 提供了丰富的工具来处理二进制字符串：

• 连接：使用||运算符拼接多个二进制字符串。
• 长度计算：使用octet_length(bytea_column)获取实际占用的字节数。
• 子串提取与查找：支持substring()提取部分字节，以及position()查找特定字节序列的位置。
• 还原为二进制：使用decode(text, format)可以将 Base64 等格式的文本重新转换回bytea数据。

5. 容量限制与性能注意事项

• 最大长度：bytea理论上可以存储高达1 GB(230230 字节) 的数据5。
• TOAST 机制：当单个bytea字段的数据超过一定阈值（默认约 2KB）时，PostgreSQL 会自动启用 TOAST（超大属性存储技术），将其压缩并分割存储在独立的 TOAST 表中，从而不影响主表的查询性能5。
• 最佳实践：尽管bytea可以存储大文件，但对于大于 10MB 甚至 100KB 的二进制文件，通常建议将其存储在文件系统或对象存储中，仅在数据库中保存文件路径，以避免高昂的网络传输开销和内存消耗5。