如何简化person_attribute表值的随机化更新SQL脚本？

核心思路 #

通过表关联+随机排序逻辑，自动匹配每条person_attribute记录对应的合法answer_concept列表，并随机选取一个值更新value字段，全程无需手动定义变量。

MySQL 实现代码 #

UPDATE person_attribute pa
JOIN (
    SELECT 
        pat.person_attribute_type_id,
        ca.answer_concept,
        ROW_NUMBER() OVER (PARTITION BY pat.person_attribute_type_id ORDER BY RAND()) AS rn
    FROM person_attribute_type pat
    JOIN concept_answer ca ON pat.foreign_key = ca.concept_id
) AS random_answers 
ON pa.person_attribute_type_id = random_answers.person_attribute_type_id
AND random_answers.rn = 1
SET pa.value = random_answers.answer_concept;

PostgreSQL 实现代码 #

UPDATE person_attribute pa
SET value = sub.answer_concept
FROM (
    SELECT 
        pa_inner.person_attribute_id,
        (SELECT ca.answer_concept
         FROM concept_answer ca
         JOIN person_attribute_type pat ON pat.foreign_key = ca.concept_id
         WHERE pat.person_attribute_type_id = pa_inner.person_attribute_type_id
         ORDER BY RANDOM()
         LIMIT 1) AS answer_concept
    FROM person_attribute pa_inner
) AS sub
WHERE pa.person_attribute_id = sub.person_attribute_id;

关键说明 #

• 利用person_attribute_type_id关联三张表，自动筛选当前属性类型的所有合法选项
• 借助数据库内置随机函数（MySQL用RAND()，PostgreSQL用RANDOM()）实现随机选值
• 逻辑完全适配属性类型的动态变化，无需手动维护合法选项列表

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