PostgreSQL 二进制字符串函数和操作符

概述

PostgreSQL 提供了丰富的二进制字符串函数和操作符，用于检查和操作 bytea 类型的值。这些函数在处理二进制数据、文件存储、加密哈希计算等场景中非常有用。

INFO

二进制字符串（bytea）是 PostgreSQL 中用于存储二进制数据的数据类型，可以存储任意字节序列，包括空字节（\0）。

SQL 标准二进制字符串函数

PostgreSQL 支持 SQL 标准定义的二进制字符串函数，这些函数使用关键字分隔参数，语法更接近自然语言。

基本操作符

连接操作符 (`||`)

功能描述：连接两个二进制字符串

语法：

sql

bytea || bytea → bytea

示例演示：

sql

-- 连接两个十六进制字符串
SELECT '\x123456'::bytea || '\x789a00bcde'::bytea AS result;

输出结果：

      result
------------------
 \x123456789a00bcde

分析过程：

\x123456 表示十六进制的二进制数据（3 个字节）
\x789a00bcde 表示十六进制的二进制数据（5 个字节）
连接后得到 8 个字节的二进制字符串

业务应用场景：

文件分片合并
数据包拼接
二进制协议构建

长度和位操作

位长度函数 (`bit_length`)

功能描述：返回二进制字符串中的位数（字节数 × 8）

语法：

sql

bit_length(bytea) → integer

示例演示：

sql

-- 计算二进制字符串的位数
SELECT
    '\x123456'::bytea AS data,
    bit_length('\x123456'::bytea) AS bits,
    octet_length('\x123456'::bytea) AS bytes;

输出结果：

   data   | bits | bytes
----------+------+-------
 \x123456 |   24 |     3

分析过程：

\x123456 包含 3 个字节
每个字节 8 位，所以总共 24 位
bit_length = octet_length × 8

字节长度函数 (`octet_length`)

功能描述：返回二进制字符串中的字节数

语法：

sql

octet_length(bytea) → integer

示例演示：

sql

-- 比较不同二进制数据的长度
SELECT
    data,
    octet_length(data) AS byte_count
FROM (VALUES
    ('\x00'::bytea),
    ('\x123456'::bytea),
    ('\x123456789abcdef0'::bytea)
) AS t(data);

输出结果：

        data         | byte_count
---------------------+------------
 \x00                |          1
 \x123456            |          3
 \x123456789abcdef0  |          8

字符串修剪函数

双端修剪 (`btrim`)

功能描述：从字符串开头和结尾删除包含指定字节的最长字符串

语法：

sql

btrim(bytes bytea, bytesremoved bytea) → bytea

示例演示：

sql

-- 从两端删除指定字节
SELECT
    original_data,
    btrim(original_data, '\x9012'::bytea) AS trimmed_result
FROM (VALUES
    ('\x1234567890'::bytea),
    ('\x9012345678901290'::bytea),
    ('\x1290567812'::bytea)
) AS t(original_data);

输出结果：

   original_data    | trimmed_result
--------------------+----------------
 \x1234567890       | \x345678
 \x9012345678901290 | \x34567890
 \x1290567812       | \x567812

分析过程：

btrim 会寻找开头和结尾连续包含 \x90 或 \x12 字节的部分
只删除连续匹配的字节，不会删除中间的匹配字节

左修剪和右修剪

左修剪 (ltrim)：

sql

-- 只从开头删除指定字节
SELECT ltrim('\x1234567890'::bytea, '\x9012'::bytea);
-- 结果: \x34567890

右修剪 (rtrim)：

sql

-- 只从结尾删除指定字节
SELECT rtrim('\x1234567890'::bytea, '\x9012'::bytea);
-- 结果: \x12345678

子字符串操作

覆盖函数 (`overlay`)

功能描述：替换二进制字符串中指定位置的子字符串

语法：

sql

overlay(bytes bytea PLACING newsubstring bytea FROM start integer [FOR count integer]) → bytea

示例演示：

sql

-- 替换指定位置的字节
SELECT
    '\x1234567890'::bytea AS original,
    overlay(
        '\x1234567890'::bytea
        PLACING '\x002003'::bytea
        FROM 2 FOR 3
    ) AS modified;

输出结果：

   original   |  modified
--------------+------------
 \x1234567890 | \x12020390

分析过程：

原始数据：\x1234567890（5 个字节）
从位置 2 开始（第 2 个字节，即 \x34）
替换 3 个字节（\x345678 → \x002003）
最终结果：\x12 + \x002003 + \x90

位置查找 (`position`)

功能描述：查找子字符串在二进制字符串中首次出现的位置

语法：

sql

position(substring bytea IN bytes bytea) → integer

示例演示：

sql

-- 查找子字符串位置
SELECT
    main_string,
    search_pattern,
    position(search_pattern IN main_string) AS found_at
FROM (VALUES
    ('\x1234567890'::bytea, '\x5678'::bytea),
    ('\x1234567890'::bytea, '\x1234'::bytea),
    ('\x1234567890'::bytea, '\xFFEE'::bytea)
) AS t(main_string, search_pattern);

输出结果：

  main_string  | search_pattern | found_at
---------------+----------------+----------
 \x1234567890  | \x5678         |        3
 \x1234567890  | \x1234         |        1
 \x1234567890  | \xffee         |        0

业务应用场景：

二进制协议中查找特定标识符
文件格式解析中定位关键字段
数据完整性检查

子字符串提取 (`substring`)

功能描述：从二进制字符串中提取指定范围的子字符串

语法：

sql

substring(bytes bytea [FROM start integer] [FOR count integer]) → bytea

示例演示：

sql

-- 提取子字符串的多种方式
SELECT
    '\x1234567890ABCDEF'::bytea AS original,
    substring('\x1234567890ABCDEF'::bytea FROM 3 FOR 2) AS middle_2_bytes,
    substring('\x1234567890ABCDEF'::bytea FROM 5) AS from_5th_byte,
    substring('\x1234567890ABCDEF'::bytea FOR 4) AS first_4_bytes;

输出结果：

      original      | middle_2_bytes | from_5th_byte |  first_4_bytes
--------------------+----------------+---------------+-----------------
 \x1234567890abcdef | \x5678         | \x90abcdef    | \x12345678

扩展二进制字符串函数

PostgreSQL 还提供了许多扩展函数，用于更精细的二进制数据操作。

位操作函数

位计数 (`bit_count`)

功能描述：计算二进制字符串中设置为 1 的位数（popcount）

示例演示：

sql

-- 计算不同数据的置位数
SELECT
    data,
    encode(data, 'hex') AS hex_display,
    bit_count(data) AS ones_count
FROM (VALUES
    ('\x00'::bytea),      -- 全0
    ('\xFF'::bytea),      -- 全1
    ('\x0F'::bytea),      -- 0000 1111
    ('\xAA'::bytea)       -- 1010 1010
) AS t(data);

输出结果：

 data | hex_display | ones_count
------+-------------+------------
 \x00 | 00          |          0
 \xff | ff          |          8
 \x0f | 0f          |          4
 \xaa | aa          |          4

位获取和设置

获取指定位 (get_bit)：

sql

-- 获取第30位的值
SELECT get_bit('\x1234567890'::bytea, 30);
-- 结果: 1

设置指定位 (set_bit)：

sql

-- 将第30位设置为0
SELECT set_bit('\x1234567890'::bytea, 30, 0);
-- 结果: \x1234563890

TIP

位编号从 0 开始，从每个字节的右侧（最低有效位）开始计数。例如：

位 0-7：第一个字节
位 8-15：第二个字节
以此类推

字节操作函数

字节获取和设置

获取指定字节 (get_byte)：

sql

-- 获取第4个字节（从0开始）
SELECT get_byte('\x1234567890'::bytea, 4);
-- 结果: 144 (0x90的十进制值)

设置指定字节 (set_byte)：

sql

-- 将第4个字节设置为64 (0x40)
SELECT set_byte('\x1234567890'::bytea, 4, 64);
-- 结果: \x1234567840

哈希函数

PostgreSQL 提供多种哈希算法用于数据完整性验证和安全应用。

MD5 哈希

功能描述：计算 MD5 哈希值，返回十六进制字符串

示例演示：

sql

-- 计算不同数据的MD5哈希
SELECT
    input_data,
    md5(input_data) AS md5_hash,
    length(md5(input_data)) AS hash_length
FROM (VALUES
    ('hello'::bytea),
    ('world'::bytea),
    ('Th\000omas'::bytea)  -- 包含空字节
) AS t(input_data);

输出结果：

 input_data |            md5_hash            | hash_length
------------+--------------------------------+-------------
 \x68656c6c6f | 5d41402abc4b2a76b9719d911017c592 |          32
 \x776f726c64 | 7d793037a0760186574b0282f2f435e7 |          32
 \x54680000616d6173 | 8ab2d3c9689aaf18b4958c334c82d8b1 |          32

SHA 系列哈希

SHA-256 示例：

sql

SELECT
    'abc'::bytea AS input,
    sha256('abc'::bytea) AS sha256_binary,
    encode(sha256('abc'::bytea), 'hex') AS sha256_hex;

输出结果：

 input |                           sha256_binary                           |                            sha256_hex
-------+-------------------------------------------------------------------+------------------------------------------------------------------
 \x616263 | \xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad | ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad

哈希函数对比表：

哈希算法	输出长度	安全性	性能	适用场景
MD5	128 位/16 字节	较低	最快	非安全校验
SHA-224	224 位/28 字节	中等	快	一般安全需求
SHA-256	256 位/32 字节	高	中等	推荐使用
SHA-384	384 位/48 字节	很高	较慢	高安全需求
SHA-512	512 位/64 字节	很高	较慢	最高安全需求

WARNING

MD5 已被发现存在碰撞漏洞，不建议用于安全相关的场景。对于安全敏感的应用，建议使用 SHA-256 或更高级别的哈希算法。

编码转换函数

文本与二进制转换

PostgreSQL 提供了在不同字符编码之间转换的函数，以及将二进制数据编码为文本表示的功能。

编码转换流程图

编码转换示例

文本到二进制 (convert_to)：

sql

-- 将文本转换为不同编码的二进制数据
SELECT
    original_text,
    convert_to(original_text, 'UTF8') AS utf8_bytes,
    convert_to(original_text, 'LATIN1') AS latin1_bytes
FROM (VALUES
    ('Hello'),
    ('数据库'),
    ('café')
) AS t(original_text);

二进制到文本 (convert_from)：

sql

-- 将UTF8编码的二进制数据转回文本
SELECT
    utf8_data,
    convert_from(utf8_data, 'UTF8') AS decoded_text
FROM (VALUES
    ('\xe6\x95\xb0\xe6\x8d\xae\xe5\xba\x93'::bytea)  -- "数据库"的UTF8编码
) AS t(utf8_data);

数据格式化

encode/decode 函数

功能描述：在二进制数据和文本表示之间转换

支持的格式：

base64：Base64 编码
hex：十六进制编码
escape：转义序列编码

Base64 编码示例：

sql

-- Base64 编码和解码
SELECT
    original_data,
    encode(original_data, 'base64') AS base64_encoded,
    decode(encode(original_data, 'base64'), 'base64') AS decoded_back
FROM (VALUES
    ('Hello World'::bytea),
    ('\x123456789ABCDEF0'::bytea)
) AS t(original_data);

输出结果：

     original_data     | base64_encoded |     decoded_back
-----------------------+----------------+----------------------
 \x48656c6c6f20576f726c64 | SGVsbG8gV29ybGQ= | \x48656c6c6f20576f726c64
 \x123456789abcdef0    | EjRWeJq83vA=   | \x123456789abcdef0

十六进制编码示例：

sql

-- 十六进制编码演示
SELECT
    raw_data,
    encode(raw_data, 'hex') AS hex_string,
    upper(encode(raw_data, 'hex')) AS hex_upper,
    decode(encode(raw_data, 'hex'), 'hex') AS decoded
FROM (VALUES
    ('PostgreSQL'::bytea),
    ('\x00\x01\x02\xFF\xFE'::bytea)
) AS t(raw_data);

输出结果：

      raw_data       |    hex_string     |    hex_upper      |      decoded
---------------------+-------------------+-------------------+--------------------
 \x506f737467726553514c | 506f737467726553514c | 506F737467726553514C | \x506f737467726553514c
 \x000102fffe         | 000102fffe        | 000102FFFE        | \x000102fffe

实际应用场景

文件存储与处理

sql

-- 创建文件存储表
CREATE TABLE file_storage (
    id SERIAL PRIMARY KEY,
    filename TEXT NOT NULL,
    content_type TEXT,
    file_data BYTEA NOT NULL,
    file_size INTEGER,
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    file_hash TEXT
);

-- 插入文件数据并计算哈希
INSERT INTO file_storage (filename, content_type, file_data, file_size, file_hash)
VALUES (
    'example.txt',
    'text/plain',
    'This is example file content'::bytea,
    octet_length('This is example file content'::bytea),
    encode(sha256('This is example file content'::bytea), 'hex')
);

-- 查询文件信息
SELECT
    filename,
    file_size,
    bit_length(file_data) / 8 AS calculated_size,
    left(file_hash, 16) || '...' AS hash_preview,
    encode(substring(file_data FROM 1 FOR 20), 'escape') AS content_preview
FROM file_storage;

二进制协议解析

sql

-- 解析网络数据包
WITH packet_data AS (
    SELECT '\x4500003c1234400040067890c0a80001c0a80002'::bytea AS packet
)
SELECT
    packet,
    get_byte(packet, 0) AS version_ihl,
    get_byte(packet, 1) AS type_of_service,
    (get_byte(packet, 2) << 8) + get_byte(packet, 3) AS total_length,
    substring(packet FROM 13 FOR 4) AS source_ip,
    substring(packet FROM 17 FOR 4) AS dest_ip
FROM packet_data;

数据完整性验证

sql

-- 创建数据完整性验证函数
CREATE OR REPLACE FUNCTION verify_data_integrity(
    data_content BYTEA,
    expected_hash TEXT
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN encode(sha256(data_content), 'hex') = expected_hash;
END;
$$ LANGUAGE plpgsql;

-- 验证数据完整性
SELECT
    filename,
    verify_data_integrity(
        file_data,
        file_hash
    ) AS integrity_check
FROM file_storage;

性能优化建议

索引策略

TIP

索引优化对于包含二进制数据的表，考虑以下索引策略：

哈希索引：用于精确匹配查询
GIN 索引：用于复杂的二进制搜索
部分索引：基于数据大小或类型的条件索引

sql

-- 为文件哈希创建索引
CREATE INDEX idx_file_hash ON file_storage USING hash(file_hash);

-- 为文件大小创建部分索引
CREATE INDEX idx_large_files ON file_storage (file_size)
WHERE file_size > 1048576; -- 大于1MB的文件

内存使用优化

sql

-- 分块处理大型二进制数据
CREATE OR REPLACE FUNCTION process_large_binary(
    input_data BYTEA,
    chunk_size INTEGER DEFAULT 8192
) RETURNS TABLE(chunk_num INTEGER, chunk_data BYTEA, chunk_hash TEXT) AS $$
DECLARE
    total_length INTEGER;
    current_pos INTEGER := 1;
    chunk_counter INTEGER := 1;
BEGIN
    total_length := octet_length(input_data);

    WHILE current_pos <= total_length LOOP
        RETURN QUERY SELECT
            chunk_counter,
            substring(input_data FROM current_pos FOR chunk_size),
            encode(sha256(substring(input_data FROM current_pos FOR chunk_size)), 'hex');

        current_pos := current_pos + chunk_size;
        chunk_counter := chunk_counter + 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

常见错误和解决方案

编码问题

在处理不同字符编码时，常见的错误包括：

编码不匹配导致的乱码
无效的 UTF-8 字节序列
字符集转换失败

解决方案：

sql

-- 安全的编码转换
CREATE OR REPLACE FUNCTION safe_convert_encoding(
    input_text TEXT,
    target_encoding TEXT
) RETURNS BYTEA AS $$
BEGIN
    RETURN convert_to(input_text, target_encoding);
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Encoding conversion failed: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

大数据处理

处理大型二进制数据时需要注意：

内存限制
查询超时
网络传输效率

最佳实践：

sql

-- 使用流式处理代替一次性加载
CREATE OR REPLACE FUNCTION stream_binary_data(
    table_name TEXT,
    column_name TEXT,
    row_id INTEGER
) RETURNS VOID AS $$
DECLARE
    chunk_size CONSTANT INTEGER := 65536; -- 64KB chunks
    total_size INTEGER;
    processed_size INTEGER := 0;
BEGIN
    -- 获取总大小
    EXECUTE format('SELECT octet_length(%I) FROM %I WHERE id = $1',
                   column_name, table_name)
    INTO total_size USING row_id;

    -- 分块处理
    WHILE processed_size < total_size LOOP
        -- 处理当前块
        EXECUTE format('SELECT substring(%I FROM $1 FOR $2) FROM %I WHERE id = $3',
                       column_name, table_name)
        USING processed_size + 1, chunk_size, row_id;

        processed_size := processed_size + chunk_size;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

总结

PostgreSQL 的二进制字符串函数提供了完整的二进制数据处理能力，涵盖了从基本操作到高级应用的各个方面：

基础操作：连接、长度计算、子字符串提取
高级处理：位操作、哈希计算、编码转换
实际应用：文件存储、协议解析、数据完整性验证
性能优化：索引策略、内存管理、流式处理

掌握这些函数能够帮助开发者高效地处理各种二进制数据场景，从简单的数据存储到复杂的二进制协议解析都能游刃有余。

Details

PostgreSQL 二进制字符串函数和操作符 ​

概述 ​

SQL 标准二进制字符串函数 ​

基本操作符 ​

连接操作符 (||) ​

长度和位操作 ​

位长度函数 (bit_length) ​

字节长度函数 (octet_length) ​

字符串修剪函数 ​

双端修剪 (btrim) ​

左修剪和右修剪 ​

子字符串操作 ​

覆盖函数 (overlay) ​

位置查找 (position) ​

子字符串提取 (substring) ​

扩展二进制字符串函数 ​

位操作函数 ​

位计数 (bit_count) ​

位获取和设置 ​

字节操作函数 ​

字节获取和设置 ​

哈希函数 ​

MD5 哈希 ​

SHA 系列哈希 ​

编码转换函数 ​

文本与二进制转换 ​

编码转换流程图 ​

编码转换示例 ​

数据格式化 ​

encode/decode 函数 ​

实际应用场景 ​

文件存储与处理 ​

二进制协议解析 ​

数据完整性验证 ​

性能优化建议 ​

索引策略 ​

内存使用优化 ​

常见错误和解决方案 ​

编码问题 ​

大数据处理 ​

总结 ​

PostgreSQL 二进制字符串函数和操作符

概述

SQL 标准二进制字符串函数

基本操作符

连接操作符 (`||`)

长度和位操作

位长度函数 (`bit_length`)

字节长度函数 (`octet_length`)

字符串修剪函数

双端修剪 (`btrim`)

左修剪和右修剪

子字符串操作

覆盖函数 (`overlay`)

位置查找 (`position`)

子字符串提取 (`substring`)

扩展二进制字符串函数

位操作函数

位计数 (`bit_count`)

位获取和设置

字节操作函数

字节获取和设置

哈希函数

MD5 哈希

SHA 系列哈希

编码转换函数

文本与二进制转换

编码转换流程图

编码转换示例

数据格式化

encode/decode 函数

实际应用场景

文件存储与处理

二进制协议解析

数据完整性验证

性能优化建议

索引策略

内存使用优化

常见错误和解决方案

编码问题

大数据处理

总结