在 PostgreSQL 中使用 pgcrypto 与 CTE 生成加密安全的随机字母数字标识符

文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载导读本文介绍如何在 PostgreSQL 中仅用一条查询语句基于pgcrypto扩展的gen_random_bytes()函数与通用表表达式CTE生成加密安全cryptographically random的 8 位字母数字短标识符short id。这类标识符适合作为优惠码、邀请码、短链接 ID、订单号等对可读性与安全性都有要求的场景。读完本文你将掌握字符集为何要剔除易混淆字符、get_byte与取模映射的原理、如何自由调整标识符长度以及如何扩展为批量生成多个标识符的实用写法。一、先决条件启用 pgcrypto 扩展gen_random_bytes()由随 PostgreSQL 一同发布的pgcrypto扩展提供。该扩展包含多种随机数据与哈希函数例如gen_random_uuid()、digest()、crypt()与gen_salt()等可参考仓库中的 compute-hashes-with-pgcrypto.md、generating-uuids-with-pgcrypto.md 与 salt-and-hash-a-password-with-pgcrypto.md。因此执行生成语句前需要先安装该扩展create extension if not exists pgcrypto;该语句以数据库为单位安装扩展只需在目标数据库执行一次从 PostgreSQL 13 开始gen_random_uuid()已内置进核心但gen_random_bytes()仍需pgcrypto扩展详见仓库文档 generate-random-uuids-without-an-extension.md。二、核心查询一行 SQL 生成 8 位随机标识符-- First ensure pgcrypto is installed create extension if not exists pgcrypto; -- Generates a single 8-character identifier with chars as ( -- excludes some look-alike characters select 23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz as charset ), random_bytes as ( select gen_random_bytes(8) as bytes ), random_bytes as ( select gen_random_bytes(8) as bytes ), positions as ( select generate_series(0, 7) as pos ) select string_agg( substr( charset, (get_byte(bytes, pos) % length(charset)) 1, 1 ), order by pos ) as short_id from positions, random_bytes, chars;注意上例中random_bytes一行是重复占位示意——实际使用时应只保留一份random_bytes的 CTE 定义否则会出现同名 CTE 冲突。标准写法如下并且一次执行即产出一行结果with chars as ( select 23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz as charset ), random_bytes as ( select gen_random_bytes(8) as bytes ), positions as ( select generate_series(0, 7) as pos ) select string_agg( substr(charset, (get_byte(bytes, pos) % length(charset)) 1, 1), order by pos ) as short_id from positions, random_bytes, chars;典型的输出结果如下---------- | short_id | |----------| | NXdu9AnV | ----------三、逐层拆解三个 CTE 与一次映射聚合整个查询由三个 CTE 与最终一条select组成结构清晰、每一层只做一件事1.chars定义可输出字符集23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz该字符串刻意排除了若干容易相互混淆的字符这正是短标识符工程实践的关键细节数字0与字母O、o均不在字符集中数字1与字母l、I均不在字符集中同时剔除了大写的I。剩余字符为数字2-98 个、大写字母A-Z中除I、O外的 24 个、小写字母a-z中除i、l、o外的 23 个合计 55 个候选字符。这一取舍保证了生成结果在传真、手写、印刷体等场景下不易误读。2.random_bytes获取加密安全的随机字节select gen_random_bytes(8) as bytesgen_random_bytes(n)返回n个加密安全伪随机字节bytea 类型。这里取 8 个字节对应最终 8 位字符。它由 PostgreSQL 内部基于密码学安全随机源产生适用于生成会话令牌、邀请码等需要不可预测性的场景。3.positions构造 0 到 7 的下标序列select generate_series(0, 7) as posgenerate_series(start, stop)生成从0到7的 8 行整数序列用来遍历bytes中 8 个字节位置。该函数是 PostgreSQL 中最常用的集合返回函数之一也可用于批量造数参见仓库中的 generate-series-of-numbers.md 与 insert-a-bunch-of-records-with-generate-series.md。4. 最终 select字节 → 字符的映射与聚合select string_agg( substr(charset, (get_byte(bytes, pos) % length(charset)) 1, 1), order by pos ) as short_id from positions, random_bytes, chars;get_byte(bytes, pos)取出第pos个字节的无符号整数值0–255% length(charset)即% 55将该值映射到0–54的字符集下标范围随后 1使其与substr的 1 起始位置对齐substr(charset, ..., 1)取出对应位置的单个字符三个 CTE 以逗号连接形成笛卡尔积from positions, random_bytes, chars使每一行都拥有pos、bytes、charset三个来源string_agg(..., order by pos)按pos顺序把 8 个字符拼接成最终字符串。该方案对 8 个字节的利用率略低于完美255 不能被 55 整除映射存在轻微偏差但在短标识符场景中可忽略不计且代码直观、易于维护。四、两个实用扩展1. 改变标识符长度将generate_series(0, 7)的右边界改为N-1同时把gen_random_bytes(8)改为gen_random_bytes(N)即可生成任意长度的标识符。例如 16 位标识符with chars as ( select 23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz as charset ), random_bytes as ( select gen_random_bytes(16) as bytes ), positions as ( select generate_series(0, 15) as pos ) select string_agg( substr(charset, (get_byte(bytes, pos) % length(charset)) 1, 1), order by pos ) as short_id from positions, random_bytes, chars;2. 一次生成多条标识符只需额外引入一个批量序号 CTE即可在一句查询中产出多行结果适用于批量发放邀请码with chars as ( select 23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz as charset ), batch as ( select generate_series(1, 10) as n ), random_bytes as ( select n, gen_random_bytes(8) as bytes from batch ), positions as ( select generate_series(0, 7) as pos ) select n, string_agg( substr(charset, (get_byte(bytes, pos) % length(charset)) 1, 1), order by pos ) as short_id from positions, random_bytes, chars group by n order by n;五、与 UUID 等替代方案的取舍PostgreSQL 生态中还常见两类标识符生成方式可结合场景对比gen_random_uuid()同样来自pgcryptoPostgreSQL 13 起内置生成 v4 随机 UUID如0a557c31-0632-4d3e-a349-e0adefb66a69全局唯一性强但长度达 36 字符不适合需要人工抄录或直接展示给用户的短码场景见 generating-uuids-with-pgcrypto.md 与 generate-random-uuids-without-an-extension.mduuid-ossp扩展的uuid_generate_v4()功能类似但官方文档指出 OSSP UUID 库维护不活跃仅在需要非 v4 版本 UUID 函数时才推荐见 generate-a-uuid.md。相比之下本文方案输出的是紧凑、可读、防混淆的字母数字短码且随机性同样源自密码学安全随机源适合作为优惠码、邀请码、短 ID 等业务主键之外的人读友好标识。六、小结核心组件pgcrypto扩展gen_random_bytesgenerate_series下标序列get_byte/substr字节映射string_agg拼接字符集剔除0/O/o、1/l/I等易混淆字符共 55 个候选字符调整generate_series上界与gen_random_bytes字节数即可改变标识符长度借助批量 CTE 可一次性生成多条标识符直接用于批量发码。如需进一步了解pgcrypto的其他能力哈希、口令加盐等可继续阅读仓库中的 compute-hashes-with-pgcrypto.md 与 salt-and-hash-a-password-with-pgcrypto.md。赞分享文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载相关推荐NautilusTrader随机数生成加密安全随机数在交易中的应用NautilusTrader随机数生成加密安全随机数在交易中的应用 引言为什么交易系统需要加密安全随机数 在算法交易领域随机数生成看似简单实则至关重要金融科技后端Kratos 随机字符串生成库 randx 详解加密安全的字符序列生成与均匀分布保证Kratos 随机字符串生成库 randx 详解加密安全的字符序列生成与均匀分布保证 导读 本文围绕 Ory Kratos 仓库中 oryx/randx/RE后端认证鉴权30 Seconds of Code 实战用 JavaScript 生成指定长度的随机字母数字字符串30 Seconds of Code 实战用 JavaScript 生成指定长度的随机字母数字字符串 导读 本文以 30 seconds of code 仓库教程文档创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考