PostgreSQL

Appearance

PostgreSQL 位串函数和操作符 ​

PostgreSQL 提供了丰富的位串（bit string）操作功能，支持对 bit 和 bit varying 类型的数据进行各种位操作。位串在系统编程、标志位管理、权限控制等场景中非常有用。

位串数据类型概述 ​

PostgreSQL 支持两种位串类型：

  • bit(n)：固定长度的位串，长度为 n 位
  • bit varying(n) 或 varbit(n)：可变长度的位串，最大长度为 n 位

INFO

虽然文档中主要提到 bit 类型，但 bit varying 类型的值可以与 bit 类型互换使用。

位串操作符 ​

基本操作符表 ​

操作符描述输入要求示例结果
||连接无限制B'10001' || B'011'B'10001011'
&按位与长度相等B'10001' & B'01101'B'00001'
|按位或长度相等B'10001' | B'01101'B'11101'
#按位异或长度相等B'10001' # B'01101'B'11100'
~按位非单个操作数~ B'10001'B'01110'
<<左移位串 + 整数B'10001' << 3B'01000'
>>右移位串 + 整数B'10001' >> 2B'00100'

详细操作说明 ​

1. 连接操作 (||) ​

连接操作将两个位串首尾相接，是最灵活的操作，对输入长度没有限制。

sql
-- 基本连接示例
SELECT B'1010' || B'0011' AS result;
-- 结果: 10100011

-- 连接不同长度的位串
SELECT B'11' || B'000' || B'1' AS result;
-- 结果: 110001

-- 实际应用：构建权限标志位
SELECT
    B'1000' AS admin_flag ||     -- 管理员权限
    B'0100' AS read_flag ||      -- 读取权限
    B'0010' AS write_flag ||     -- 写入权限
    B'0001' AS delete_flag       -- 删除权限
    AS full_permissions;
-- 结果: 1000010000100001

2. 按位逻辑操作 ​

按位逻辑操作要求两个操作数长度相等，用于实现复杂的位操作逻辑。

sql
-- 按位与操作 - 权限检查示例
SELECT
    B'1101' AS user_permissions,
    B'1010' AS required_permissions,
    B'1101' & B'1010' AS granted_permissions;
-- 结果: user_permissions=1101, required_permissions=1010, granted_permissions=1000

-- 按位或操作 - 权限合并示例
SELECT
    B'1001' AS role1_permissions,
    B'0110' AS role2_permissions,
    B'1001' | B'0110' AS combined_permissions;
-- 结果: role1_permissions=1001, role2_permissions=0110, combined_permissions=1111

-- 按位异或操作 - 权限差异检测
SELECT
    B'1101' AS current_permissions,
    B'1011' AS target_permissions,
    B'1101' # B'1011' AS permission_changes;
-- 结果: current_permissions=1101, target_permissions=1011, permission_changes=0110

3. 位移操作 ​

位移操作在密码学、哈希算法和数据压缩中经常用到。

sql
-- 左移操作示例
SELECT
    B'10110' AS original,
    B'10110' << 2 AS left_shift_2,
    B'10110' << 4 AS left_shift_4;
-- 结果: original=10110, left_shift_2=11000, left_shift_4=00000

-- 右移操作示例
SELECT
    B'10110' AS original,
    B'10110' >> 1 AS right_shift_1,
    B'10110' >> 3 AS right_shift_3;
-- 结果: original=10110, right_shift_1=01011, right_shift_3=00010

-- 实际应用：简单的位掩码操作
SELECT
    B'111100001111' AS data,
    B'111100001111' >> 4 AS extract_high_bits,
    (B'111100001111' << 4) >> 4 AS extract_low_bits;
-- 结果: data=111100001111, extract_high_bits=000011110000, extract_low_bits=000000001111

WARNING

位移操作会保持字符串长度不变，移出的位会丢失，移入的位用 0 填充。

位串函数 ​

统计和测量函数 ​

函数描述示例结果
bit_count(bit)统计设置位数量（popcount）bit_count(B'10111')4
bit_length(bit)返回位串长度bit_length(B'10111')5
length(bit)返回位串长度length(B'10111')5
octet_length(bit)返回字节数octet_length(B'1011111011')2

实际应用示例 ​

sql
-- 分析用户权限分布
CREATE TABLE user_permissions (
    user_id INT,
    permissions BIT(16)
);

INSERT INTO user_permissions VALUES
    (1, B'1111000011110000'),
    (2, B'1010101010101010'),
    (3, B'1111111111111111');

-- 统计每个用户的权限数量
SELECT
    user_id,
    permissions,
    bit_count(permissions) AS permission_count,
    bit_length(permissions) AS total_bits,
    ROUND(bit_count(permissions)::NUMERIC / bit_length(permissions) * 100, 2) AS permission_ratio
FROM user_permissions;

-- 预期结果:
-- user_id | permissions      | permission_count | total_bits | permission_ratio
-- --------|------------------|------------------|------------|------------------
-- 1       | 1111000011110000 | 8               | 16         | 50.00
-- 2       | 1010101010101010 | 8               | 16         | 50.00
-- 3       | 1111111111111111 | 16              | 16         | 100.00

字符串操作函数 ​

1. overlay 函数 - 位串替换 ​

sql
-- 基本语法
-- overlay(bits PLACING newsubstring FROM start [FOR count])

-- 基本替换示例
SELECT overlay(B'01010101010101010' PLACING B'11111' FROM 2 FOR 3) AS result;
-- 结果: 0111110101010101010

-- 不指定长度，使用新子串的完整长度
SELECT overlay(B'00000000' PLACING B'1111' FROM 3) AS result;
-- 结果: 00111100

-- 实际应用：更新特定位置的标志位
SELECT
    B'0000000000000000' AS original_flags,
    overlay(B'0000000000000000' PLACING B'1010' FROM 5 FOR 4) AS updated_flags;
-- 结果: original_flags=0000000000000000, updated_flags=0000101000000000

2. position 函数 - 子串查找 ​

sql
-- 基本查找示例
SELECT position(B'010' IN B'000001101011') AS position;
-- 结果: 8

-- 查找不存在的模式
SELECT position(B'111' IN B'000001101011') AS position;
-- 结果: 0

-- 实际应用：查找特定的位模式
CREATE TABLE bit_patterns (
    id INT,
    data BIT(20)
);

INSERT INTO bit_patterns VALUES
    (1, B'11110000111100001111'),
    (2, B'10101010101010101010'),
    (3, B'11111111000000000000');

-- 查找特定模式的位置
SELECT
    id,
    data,
    position(B'1111' IN data) AS pattern_position,
    CASE
        WHEN position(B'1111' IN data) > 0
        THEN '找到模式'
        ELSE '未找到模式'
    END AS result_status
FROM bit_patterns;

-- 预期结果:
-- id | data                 | pattern_position | result_status
-- ---|---------------------|------------------|---------------
-- 1  | 11110000111100001111 | 1               | 找到模式
-- 2  | 10101010101010101010 | 0               | 未找到模式
-- 3  | 11111111000000000000 | 1               | 找到模式

3. substring 函数 - 位串截取 ​

sql
-- 基本语法
-- substring(bits [FROM start] [FOR count])

-- 基本截取示例
SELECT substring(B'110010111111' FROM 3 FOR 2) AS result;
-- 结果: 00

-- 从指定位置截取到末尾
SELECT substring(B'110010111111' FROM 5) AS result;
-- 结果: 10111111

-- 实际应用：提取位串中的特定字段
SELECT
    B'1111000011110000' AS full_data,
    substring(B'1111000011110000' FROM 1 FOR 4) AS field1,
    substring(B'1111000011110000' FROM 5 FOR 4) AS field2,
    substring(B'1111000011110000' FROM 9 FOR 4) AS field3,
    substring(B'1111000011110000' FROM 13 FOR 4) AS field4;
-- 结果:
-- full_data=1111000011110000, field1=1111, field2=0000, field3=1111, field4=0000

单个位操作函数 ​

1. get_bit 函数 - 获取单个位 ​

sql
-- 基本用法（注意：位索引从0开始）
SELECT get_bit(B'101010101010101010', 6) AS bit_value;
-- 结果: 1

-- 实际应用：检查特定权限位
CREATE TABLE user_flags (
    user_id INT,
    flags BIT(8)
);

INSERT INTO user_flags VALUES
    (1, B'10110100'),
    (2, B'01001011'),
    (3, B'11111111');

-- 检查不同用户的特定权限（假设位0-7分别代表不同权限）
SELECT
    user_id,
    flags,
    get_bit(flags, 0) AS admin_permission,
    get_bit(flags, 1) AS read_permission,
    get_bit(flags, 2) AS write_permission,
    get_bit(flags, 3) AS delete_permission
FROM user_flags;

-- 预期结果:
-- user_id | flags    | admin_permission | read_permission | write_permission | delete_permission
-- --------|----------|------------------|-----------------|------------------|-------------------
-- 1       | 10110100 | 1               | 0               | 1               | 1
-- 2       | 01001011 | 0               | 1               | 0               | 0
-- 3       | 11111111 | 1               | 1               | 1               | 1

2. set_bit 函数 - 设置单个位 ​

sql
-- 基本用法
SELECT set_bit(B'101010101010101010', 6, 0) AS result;
-- 结果: 101010001010101010

-- 实际应用：动态权限管理
SELECT
    B'00000000' AS original_permissions,
    set_bit(B'00000000', 0, 1) AS grant_admin,
    set_bit(set_bit(B'00000000', 0, 1), 2, 1) AS grant_admin_and_write,
    set_bit(set_bit(set_bit(B'00000000', 0, 1), 2, 1), 7, 1) AS full_setup;
-- 结果展示权限逐步授予的过程

整数与位串的转换 ​

PostgreSQL 支持整数和位串之间的相互转换，这在数据编码和解码场景中非常有用。

整数转位串 ​

sql
-- 基本转换示例
SELECT
    44 AS original_number,
    44::bit(10) AS ten_bits,
    44::bit(3) AS three_bits,
    (-44)::bit(12) AS negative_with_sign_extension;

-- 结果:
-- original_number=44, ten_bits=0000101100, three_bits=100, negative_with_sign_extension=111111010100

-- 转换为 bit(1) 只保留最低位
SELECT
    44::bit AS lowest_bit,
    45::bit AS lowest_bit_odd;
-- 结果: lowest_bit=0, lowest_bit_odd=1

位串转整数 ​

sql
-- 基本转换示例
SELECT
    B'1110'::integer AS binary_to_int,
    B'11111111'::integer AS full_byte,
    B'10000000'::integer AS high_bit_set;

-- 结果: binary_to_int=14, full_byte=255, high_bit_set=128

-- 实际应用：解析位编码的数据
CREATE TABLE encoded_data (
    id INT,
    bit_data BIT(8),
    description TEXT
);

INSERT INTO encoded_data VALUES
    (1, B'00001111', '低4位设置'),
    (2, B'11110000', '高4位设置'),
    (3, B'10101010', '交替模式');

-- 将位串转换为整数进行数值分析
SELECT
    id,
    bit_data,
    bit_data::integer AS numeric_value,
    description,
    CASE
        WHEN bit_data::integer < 16 THEN '低值范围'
        WHEN bit_data::integer < 128 THEN '中值范围'
        ELSE '高值范围'
    END AS value_category
FROM encoded_data;

-- 预期结果:
-- id | bit_data | numeric_value | description | value_category
-- ---|----------|---------------|-------------|----------------
-- 1  | 00001111 | 15           | 低4位设置    | 低值范围
-- 2  | 11110000 | 240          | 高4位设置    | 高值范围
-- 3  | 10101010 | 170          | 交替模式     | 高值范围

实际应用场景 ​

1. 用户权限管理系统 ​

sql
-- 创建权限管理表
CREATE TABLE user_permissions_advanced (
    user_id INT PRIMARY KEY,
    role_name VARCHAR(50),
    permissions BIT(16),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 定义权限位映射（位0-15代表不同权限）
-- 位0: 系统管理, 位1: 用户管理, 位2: 数据读取, 位3: 数据写入
-- 位4: 数据删除, 位5: 报表查看, 位6: 报表生成, 位7: 配置修改
-- 位8-15: 预留扩展

INSERT INTO user_permissions_advanced VALUES
    (1, '超级管理员', B'1111111111111111'),  -- 所有权限
    (2, '数据分析师', B'0000000000110100'),  -- 数据读取+报表权限
    (3, '普通用户',   B'0000000000000100');  -- 仅数据读取

-- 复杂权限查询和分析
SELECT
    user_id,
    role_name,
    permissions,
    bit_count(permissions) AS total_permissions,
    get_bit(permissions, 0) AS system_admin,
    get_bit(permissions, 1) AS user_admin,
    get_bit(permissions, 2) AS data_read,
    get_bit(permissions, 3) AS data_write,
    get_bit(permissions, 4) AS data_delete,
    CASE
        WHEN get_bit(permissions, 0) = 1 THEN 'ADMIN'
        WHEN bit_count(permissions) >= 5 THEN 'POWER_USER'
        WHEN bit_count(permissions) >= 2 THEN 'REGULAR_USER'
        ELSE 'LIMITED_USER'
    END AS user_category
FROM user_permissions_advanced;

2. 系统状态监控 ​

sql
-- 系统组件状态监控
CREATE TABLE system_status (
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    component_status BIT(32),  -- 32个组件的状态
    description TEXT
);

-- 插入状态数据
INSERT INTO system_status (component_status, description) VALUES
    (B'11111111111111111111111111111111', '所有组件正常'),
    (B'11111111111111111111111111111110', '组件31故障'),
    (B'11111111111111110111111111111111', '组件15故障');

-- 分析系统健康状态
SELECT
    timestamp,
    component_status,
    bit_count(component_status) AS healthy_components,
    32 - bit_count(component_status) AS failed_components,
    ROUND(bit_count(component_status)::NUMERIC / 32 * 100, 2) AS health_percentage,
    description
FROM system_status
ORDER BY timestamp DESC;

3. 特征标志管理 ​

sql
-- 应用功能特征标志
CREATE TABLE feature_flags (
    version VARCHAR(10),
    features BIT(64),
    release_date DATE
);

INSERT INTO feature_flags VALUES
    ('v1.0', B'0000000000000000000000000000000000000000000000000000000000001111', '2024-01-01'),
    ('v1.1', B'0000000000000000000000000000000000000000000000000000000000011111', '2024-02-01'),
    ('v2.0', B'0000000000000000000000000000000000000000000000000000001111111111', '2024-03-01');

-- 分析功能演进
SELECT
    version,
    features,
    bit_count(features) AS enabled_features,
    release_date,
    LAG(features) OVER (ORDER BY release_date) AS prev_features,
    CASE
        WHEN LAG(features) OVER (ORDER BY release_date) IS NOT NULL
        THEN bit_count(features # LAG(features) OVER (ORDER BY release_date))
        ELSE 0
    END AS changed_features
FROM feature_flags
ORDER BY release_date;

性能考虑和最佳实践 ​

1. 索引策略 ​

sql
-- 对于经常查询的位串列，可以创建表达式索引
CREATE INDEX idx_permission_count ON user_permissions_advanced(bit_count(permissions));
CREATE INDEX idx_admin_users ON user_permissions_advanced(get_bit(permissions, 0))
    WHERE get_bit(permissions, 0) = 1;

2. 存储优化 ​

TIP

  • 使用 bit(n) 而不是 bit varying 当长度固定时，可以获得更好的存储效率
  • 考虑使用 bigint 类型替代长度不超过 64 位的位串，在某些操作中性能更好
  • 位串长度建议是 8 的倍数，以获得最佳的存储对齐

3. 查询优化技巧 ​

sql
-- 使用位操作进行高效的集合运算
-- 检查用户是否具有任一管理权限（位0或位1）
SELECT user_id
FROM user_permissions_advanced
WHERE (permissions & B'0000000000000011'::bit(16)) != B'0000000000000000'::bit(16);

-- 查找具有特定权限组合的用户
SELECT user_id
FROM user_permissions_advanced
WHERE (permissions & B'0000000000001100'::bit(16)) = B'0000000000001100'::bit(16);

注意事项和限制 ​

WARNING

  1. 长度限制：按位逻辑操作要求两个操作数长度完全相等
  2. 位移边界：位移操作不会改变位串长度，移出的位会永久丢失
  3. 索引从 0 开始：get_bit 和 set_bit 函数中，位索引从 0 开始计算
  4. 类型转换：整数转换为 bit 时默认为 bit(1)，只保留最低位

DANGER

在进行位串比较时，PostgreSQL 严格按照位模式匹配。不同长度的位串即使在逻辑上表示相同的值，也被认为是不相等的：

sql
SELECT B'01' = B'001';  -- 返回 false
SELECT B'01'::bit(3) = B'001';  -- 返回 true

通过掌握这些位串函数和操作符，你可以在 PostgreSQL 中高效地处理二进制数据、实现复杂的标志位逻辑，以及构建高性能的权限管理系统。位串操作在系统级编程和优化场景中具有重要价值，合理使用可以显著提升应用性能。