在PostgreSQL中用URL友好型唯一字符串替代BIGSERIAL的方案

针对你的需求，这里提供几种PostgreSQL中生成简洁、无猜解性的唯一字符串ID的方案：

1. 基于自增ID的Base64编码（推荐入门方案） #

保留自增整数ID作为内部主键，同时生成一个对外暴露的短字符串ID。通过Base64编码自增ID，替换掉URL不友好的字符，得到类似Google Drive的简洁格式。

实现步骤： #

• 创建转换整数ID为短字符串的函数：

CREATE OR REPLACE FUNCTION int_to_short_id(p_id INT) RETURNS VARCHAR AS $$
BEGIN
  -- 替换Base64的+/为-_，去掉末尾的=填充符
  RETURN REPLACE(REPLACE(ENCODE(p_id::BYTEA, 'base64'), '+', '-'), '/', '_')::TEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

• 在表中添加生成列自动生成短ID：

ALTER TABLE your_table ADD COLUMN short_id VARCHAR GENERATED ALWAYS AS (int_to_short_id(id)) STORED;
ALTER TABLE your_table ADD CONSTRAINT unique_short_id UNIQUE(short_id);

优点：完全无冲突，实现简单，性能高；缺点：如果短ID和自增ID的对应关系被发现，仍存在一定可推测性（但远优于纯数字ID）。

2. 使用pgcrypto生成随机字母数字字符串 #

利用PostgreSQL的pgcrypto扩展生成加密安全的随机字符串，确保ID无规律、不可猜解。

实现步骤： #

• 先启用pgcrypto扩展：

CREATE EXTENSION IF NOT EXISTS pgcrypto;

• 创建生成指定长度随机字符串的函数：

CREATE OR REPLACE FUNCTION generate_short_id(p_length INT DEFAULT 24) RETURNS VARCHAR AS $$
BEGIN
  -- 生成随机字节，编码为十六进制后截断到指定长度
  RETURN SUBSTRING(ENCODE(gen_random_bytes(p_length/2), 'hex') FROM 1 FOR p_length);
  -- 若要包含大小写字母，可改用base64处理：
  -- RETURN SUBSTRING(REPLACE(REPLACE(ENCODE(gen_random_bytes(p_length*3/4), 'base64'), '+', '-'), '/', '_') FROM 1 FOR p_length);
END;
$$ LANGUAGE plpgsql VOLATILE;

• 插入数据时调用函数生成ID，同时添加唯一约束：

ALTER TABLE your_table ADD COLUMN short_id VARCHAR(24) UNIQUE;
-- 插入示例
INSERT INTO your_table (type, name, short_id) VALUES ('folder', 'photos', generate_short_id());

优点：完全随机，不可猜解；缺点：存在极小碰撞概率（可通过增加长度降低），插入时可能需要处理唯一约束冲突（重试插入）。

3. 简化UUID（去掉短横线） #

PostgreSQL原生支持UUID类型，只需将标准UUID的短横线去掉，就能得到32位的字母数字字符串，格式类似MongoDB的_id。

实现步骤： #

• 添加UUID列并设置默认值：

ALTER TABLE your_table ADD COLUMN uuid_id UUID DEFAULT gen_random_uuid();
ALTER TABLE your_table ADD CONSTRAINT unique_uuid_id UNIQUE(uuid_id);

• 查询时转换为无短横线的字符串，或创建生成列自动存储：

-- 查询转换
SELECT REPLACE(uuid_id::TEXT, '-', '') AS short_uuid FROM your_table WHERE id = 2;

-- 添加生成列
ALTER TABLE your_table ADD COLUMN short_uuid VARCHAR(32) GENERATED ALWAYS AS (REPLACE(uuid_id::TEXT, '-', '')) STORED;
ALTER TABLE your_table ADD CONSTRAINT unique_short_uuid UNIQUE(short_uuid);

优点：原生支持，唯一性有保障，无需额外依赖；缺点：ID长度稍长（32位）。

4. 模拟MongoDB ObjectId格式 #

MongoDB的ObjectId由时间戳（4字节）、机器标识（3字节）、进程ID（2字节）、计数器（3字节）组成，PostgreSQL可以模拟这种结构生成有序且唯一的短ID。

实现步骤： #

CREATE OR REPLACE FUNCTION generate_object_id() RETURNS VARCHAR AS $$
DECLARE
  timestamp BYTEA := E'\\x' || TO_CHAR(EXTRACT(EPOCH FROM NOW())::BIGINT, 'FMXXXXXXXX');
  machine_id BYTEA := E'\\x000000'; -- 替换为你的机器标识，比如MAC地址后3字节
  process_id BYTEA := E'\\x' || TO_CHAR(pg_backend_pid()::INT, 'FMXXXX');
  counter BYTEA := E'\\x' || TO_CHAR(nextval('object_id_counter')::INT, 'FMXXXXXX');
BEGIN
  RETURN ENCODE(timestamp || machine_id || process_id || counter, 'hex');
END;
$$ LANGUAGE plpgsql VOLATILE;

-- 创建计数器序列
CREATE SEQUENCE IF NOT EXISTS object_id_counter MINVALUE 0 MAXVALUE 16777215 CYCLE;

优点：ID包含时间信息，方便按创建时间排序；唯一性高；缺点：实现相对复杂，需要维护计数器序列。

内容的提问来源于stack exchange，提问作者brainf___enthusiast