如何在SQL中生成可复现且互不重复的多组唯一患者随机标识符

这个需求非常典型——既要保护患者原始ID的隐私，又要满足多研究的唯一性和可复现性，我给你一套经过实践验证的解决方案，完全不需要复杂的WHILE循环，核心是用确定性哈希函数结合研究专属密钥来实现。

核心原理 #

我们的目标是：

1. 同一患者+同一研究 → 始终生成相同的标识符（可复现）
2. 同一患者+不同研究 → 生成完全不同的标识符（跨研究唯一）
3. 所有标识符全局唯一（避免碰撞）

用哈希函数就能完美满足：

• 哈希函数是确定性的：相同的输入（原始patientID + 研究密钥）一定会得到相同的输出，天然支持复现。
• 不同的输入（哪怕只有研究密钥不同）会得到完全不同的哈希值，保证跨研究唯一。
• 选用SHA-256这类强哈希算法，碰撞概率可以忽略不计，足以保证标识符的唯一性。

SQL具体实现 #

第一步：定义研究专属密钥 #

首先给每个研究分配一个固定的、唯一的StudyKey（可以是整数或字符串），并存储起来（比如建一个Studies表）：

CREATE TABLE Studies (
    StudyID VARCHAR(50) PRIMARY KEY, -- 研究的唯一标识，比如 'CardioStudy_2024'
    StudyKey VARCHAR(100) UNIQUE NOT NULL -- 研究专属密钥，比如 'Cardio_Secret_123'
);

-- 插入研究数据（密钥一旦设定就不要修改！）
INSERT INTO Studies (StudyID, StudyKey)
VALUES ('CardioStudy_2024', 'Cardio_Secret_123'),
       ('NeuroStudy_2024', 'Neuro_Secret_456');

第二步：生成隐私标识符 #

用HASHBYTES函数（SQL Server支持，其他数据库有类似函数）结合原始patientID和研究密钥生成标识符：

-- 为指定研究生成隐私标识符
SELECT
    p.patientID,
    -- 将哈希结果转为十六进制字符串，长度固定为64位
    CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', CONCAT(s.StudyKey, '_', p.patientID)), 2) AS StudyPatientID
FROM Patients p
CROSS JOIN Studies s
WHERE s.StudyID = 'CardioStudy_2024'; -- 指定要生成的研究

可选：存储映射关系（避免重复计算） #

如果需要频繁使用这些标识符，建议提前把映射关系存储到一个表中，后续直接查询即可：

CREATE TABLE PatientStudyMapping (
    patientID INT NOT NULL, -- 原始患者ID
    StudyID VARCHAR(50) NOT NULL, -- 研究ID
    StudyPatientID VARCHAR(64) NOT NULL, -- 隐私标识符
    PRIMARY KEY (patientID, StudyID) -- 联合主键保证同一患者在同一研究中只有一个标识符
);

-- 初始化映射表
INSERT INTO PatientStudyMapping (patientID, StudyID, StudyPatientID)
SELECT
    p.patientID,
    s.StudyID,
    CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', CONCAT(s.StudyKey, '_', p.patientID)), 2)
FROM Patients p
CROSS JOIN Studies s
WHERE s.StudyID = 'CardioStudy_2024';

关键注意事项 #

• 绝对不要修改StudyKey：密钥是复现标识符的核心，一旦修改，之前生成的所有标识符都会失效，无法对应回原始patientID。
• 缩短标识符（可选）：如果64位的十六进制字符串太长，可以截取前16-24位（比如SUBSTRING(..., 1, 16)），碰撞概率仍然极低，完全满足绝大多数场景。
• 跨数据库适配：如果用MySQL，用SHA2(CONCAT(s.StudyKey, '_', p.patientID), 256)；用PostgreSQL，用ENCODE(SHA256(CONCAT(s.StudyKey, '_', p.patientID)::BYTEA), 'hex')，原理完全一致。
• 碰撞校验（可选）：如果担心极端情况的碰撞，可以在生成后加一步校验：检查StudyPatientID是否重复，若有则调整密钥或增加额外的盐值。

为什么不推荐RAND()/NEWID()？ #

• RAND()带种子的话，在批量生成时容易出现重复值，而且循环实现复杂，性能差。
• NEWID()是随机生成的，完全不可复现，不符合你"重新运行生成相同列表"的需求。

哈希的方法既简洁又可靠，完全满足你的所有要求。

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