9.5. 二进制字符串函数和运算符#

本节介绍用于检查和操作二进制字符串（即类型为bytea的值）的函数和运算符。其中许多函数在目的和语法上等同于上一节中描述的文本字符串函数。

SQL定义了一些字符串函数，它们使用关键字（而不是逗号）来分隔参数。详细信息请参见表 9.11。PostgreSQL还提供了使用常规函数调用语法（请参见表 9.12）的这些函数版本。

表 9.11.SQL二进制字符串函数和运算符

函数/运算符 说明 示例
bytea || bytea → bytea 连接两个二进制字符串。 '\x123456'::bytea || '\x789a00bcde'::bytea → \x123456789a00bcde
bit_length ( bytea ) → integer 返回二进制字符串中的位数（octet_length 的 8 倍）。 bit_length('\x123456'::bytea) → 24
btrim ( bytes bytea, bytesremoved bytea ) → bytea 从 bytes 的开头和结尾移除最长的仅包含出现在 bytesremoved 中的字节的字符串。 btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678
ltrim ( bytes bytea, bytesremoved bytea ) → bytea 从 bytes 的开头移除最长的仅包含出现在 bytesremoved 中的字节的字符串。 ltrim('\x1234567890'::bytea, '\x9012'::bytea) → \x34567890
octet_length ( bytea ) → integer 返回二进制字符串中的字节数。 octet_length('\x123456'::bytea) → 3
overlay ( bytes bytea PLACING newsubstring bytea FROM start integer [ FOR count integer ] ) → bytea 使用 newsubstring 替换 bytes 中从第 start 个字节开始并延伸 count 个字节的子字符串。如果省略 count，则默认为 newsubstring 的长度。 overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3) → \x12020390
position ( substring bytea IN bytes bytea ) → integer 返回指定的 substring 在 bytes 中的第一个起始索引，如果不存在，则返回零。 position('\x5678'::bytea in '\x1234567890'::bytea) → 3
rtrim ( bytes bytea, bytesremoved bytea ) → bytea 从 bytes 的末尾移除一个最长的字符串，该字符串仅包含出现在 bytesremoved 中的字节。 rtrim('\x1234567890'::bytea, '\x9012'::bytea) → \x12345678
substring ( bytes bytea [ FROM start integer ] [ FOR count integer ] ) → bytea 提取 bytes 的子字符串，如果指定了 start，则从第 start 个字节开始，如果指定了 count，则在 count 个字节后停止。至少提供一个 start 和 count。 substring('\x1234567890'::bytea from 3 for 2) → \x5678
trim ( [ LEADING | TRAILING | BOTH ] bytesremoved bytea FROM bytes bytea ) → bytea 从 bytes 的开头、结尾或两端（默认 BOTH）移除一个最长的字符串，该字符串仅包含出现在 bytesremoved 中的字节。 trim('\x9012'::bytea from '\x1234567890'::bytea) → \x345678
trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] bytes bytea, bytesremoved bytea ) → bytea 这是一个 trim() 的非标准语法。 trim(both from '\x1234567890'::bytea, '\x9012'::bytea) → \x345678

其他二进制字符串操作函数可用，并在表 9.12中列出。其中一些函数在内部用于实现表 9.11中列出的SQL标准字符串函数。

表 9.12 其他二进制字符串函数

函数 说明 示例
bit_count ( bytes bytea ) → bigint 返回二进制字符串中设置的位数（也称为 “popcount”）。 bit_count('\x1234567890'::bytea) → 15
get_bit ( bytes bytea, n bigint ) → integer 从二进制字符串中提取第 n 位。 get_bit('\x1234567890'::bytea, 30) → 1
get_byte ( bytes bytea, n integer ) → integer 从二进制字符串中提取第 n 个字节。 get_byte('\x1234567890'::bytea, 4) → 144
length ( bytea ) → integer 返回二进制字符串中的字节数。 length('\x1234567890'::bytea) → 5
length ( bytes bytea, encoding name ) → integer 返回二进制字符串中的字符数，假设它是给定 encoding 中的文本。 length('jose'::bytea, 'UTF8') → 4
md5 ( bytea ) → text 计算二进制字符串的 MD5 哈希，结果以十六进制形式写入。 md5('Th\000omas'::bytea) → 8ab2d3c9689aaf18​b4958c334c82d8b1
set_bit ( bytes bytea, n bigint, newvalue integer ) → bytea 将二进制字符串中的第 n 位设置为 newvalue。 set_bit('\x1234567890'::bytea, 30, 0) → \x1234563890
set_byte ( bytes bytea, n integer, newvalue integer ) → bytea 将二进制字符串中的第 n 个字节设置为 newvalue。 set_byte('\x1234567890'::bytea, 4, 64) → \x1234567840
sha224 ( bytea ) → bytea 计算二进制字符串的 SHA-224 哈希。 sha224('abc'::bytea) → \x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7
sha256 ( bytea ) → bytea 计算二进制字符串的 SHA-256 哈希。 sha256('abc'::bytea) → \xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad
sha384 ( bytea ) → bytea 计算二进制字符串的 SHA-384 哈希。 sha384('abc'::bytea) → \xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7
sha512 ( bytea ) → bytea 计算二进制字符串的 SHA-512 哈希。 sha512('abc'::bytea) → \xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f
substr ( bytes bytea, start integer [, count integer ] ) → bytea 从 bytes 的第 start 个字节开始提取子字符串，如果指定了 count，则扩展 count 个字节。（与 substring(bytes from start for count) 相同。） substr('\x1234567890'::bytea, 3, 2) → \x5678

函数get_byte和set_byte将二进制字符串的第一个字节编号为字节 0。函数get_bit和set_bit从每个字节内的右侧对位进行编号；例如，位 0 是第一个字节的最低有效位，位 15 是第二个字节的最高有效位。

出于历史原因，函数md5返回类型为text的十六进制编码值，而 SHA-2 函数返回类型为bytea。使用函数encode和decode在两者之间进行转换。例如，编写encode(sha256('abc'), 'hex')以获取十六进制编码的文本表示形式，或decode(md5('abc'), 'hex')以获取bytea值。

用于在不同字符集（编码）之间转换字符串以及以文本形式表示任意二进制数据的函数显示在表 9.13中。对于这些函数，类型为text的参数或结果以数据库的默认编码表示，而类型为bytea的参数或结果以另一个参数命名的编码表示。

表 9.13. 文本/二进制字符串转换函数

函数 说明 示例
convert ( bytes bytea, src_encoding name, dest_encoding name ) → bytea 将表示编码为 src_encoding 的文本的二进制字符串转换为编码为 dest_encoding 的二进制字符串（有关可用的转换，请参见 第 24.3.4 节）。 convert('text_in_utf8', 'UTF8', 'LATIN1') → \x746578745f696e5f75746638
convert_from ( bytes bytea, src_encoding name ) → text 将表示编码为 src_encoding 的文本的二进制字符串转换为数据库编码中的 text（有关可用的转换，请参见 第 24.3.4 节）。 convert_from('text_in_utf8', 'UTF8') → text_in_utf8
convert_to ( string text, dest_encoding name ) → bytea 将 text 字符串（在数据库编码中）转换为编码为 dest_encoding 的二进制字符串（有关可用转换，请参见 第 24.3.4 节）。 convert_to('some_text', 'UTF8') → \x736f6d655f74657874
encode ( bytes bytea, format text ) → text 将二进制数据编码为文本表示形式；受支持的 format 值为：base64、escape、hex。 encode('123\000\001', 'base64') → MTIzAAE=
decode ( string text, format text ) → bytea 从文本表示形式解码二进制数据；受支持的 format 值与 encode 相同。 decode('MTIzAAE=', 'base64') → \x3132330001

encode和decode函数支持以下文本格式

base64 #

base64 格式为 RFC 2045 第 6.8 节 中的格式。根据 ，编码行在 76 个字符处断开。但是，仅使用换行符作为行尾，而不是 MIME CRLF 行尾标记。decode 函数忽略回车符、换行符、空格符和制表符。否则，当 decode 提供无效的 base64 数据（包括尾部填充不正确时）时，会引发错误。

escape #

escape 格式将零字节和高位设置的字节转换为八进制转义序列 (\nnn)，并将反斜杠加倍。其他字节值将按字面值表示。如果反斜杠后面没有第二个反斜杠或三个八进制数字，decode 函数将引发错误；它接受其他字节值保持不变。

hex #

hex 格式将每 4 位数据表示为一个十六进制数字，0 到 f，先写入每个字节的高阶数字。 encode 函数以小写输出 a-f 十六进制数字。由于数据最小单位是 8 位，因此 encode 始终返回偶数个字符。 decode 函数接受大写或小写的 a-f 字符。当 decode 给出无效的十六进制数据（包括给出奇数个字符时），将引发错误。

另请参见第 9.21 节中的聚合函数string_agg和第 35.4 节中的大对象函数。