Secret Santa抽签SQL存储过程存在自分配问题，求解决方案

我开发了一个用于群体自动执行Secret Santa抽签的SQL存储过程，要求随机为每个人分配他人作为送礼对象，不允许自赠，但目前存在部分人员被分配给自己的问题，不符合规则。以下是当前使用的代码：

CREATE DATABASE IF NOT EXISTS AMIGOINVISIBLE;

USE AMIGOINVISIBLE;

CREATE TABLE IF NOT EXISTS CaseA(
     id INT PRIMARY KEY AUTO_INCREMENT,
     people VARCHAR(50),
     peopleToGift VARCHAR(50)  );

INSERT INTO CaseA (people, peopleToGift) 
VALUES  ('a.tomas', NULL), ('g.rubio', NULL), ('a.pulido', NULL), ('m.fabrega.1', NULL), ('d.lazaro', NULL), ('c.albinya', NULL), ('b.gomez', NULL),
('j.espinoza', NULL), ('j.aguilera', NULL), ('j.da.silva.1', NULL), ('a.chamorro', NULL);

DELIMITER //

CREATE OR REPLACE PROCEDURE randomizePeople()
 BEGIN
     DECLARE times INT;
     DECLARE Counter INT;
     DECLARE random1 INT;
     DECLARE random2 INT;
     DECLARE aux INT;
     DECLARE position INT;
     DECLARE peopleToGift VARCHAR(50);

     SELECT COUNT(*) INTO times FROM CaseA;
     DROP TABLE IF EXISTS order;
     CREATE TEMPORARY TABLE IF NOT EXISTS order (
         id INT PRIMARY KEY AUTO_INCREMENT,
         Number INT
     );
     SET Counter = 1;
     WHILE Counter <= times DO
         INSERT INTO order (Number) VALUES (Counter);
         SET Counter = Counter + 1;
     END WHILE;

     SET Counter = 1;

     WHILE Counter <= times DO
         SET random1 = FLOOR(RAND() * times) + 1;
         SET random2 = FLOOR(RAND() * times) + 1;

         SELECT Number INTO aux FROM order WHERE id = random1;
         UPDATE order SET Number = (SELECT Number FROM order WHERE id = random2) WHERE id = random1;
         UPDATE order SET Number = aux WHERE id = random2;

         SET Counter = Counter + 1;
     END WHILE;

     SET Counter = 1;

     WHILE Counter <= times DO
         SELECT Number INTO position FROM order WHERE id = Counter;
         SELECT people INTO peopleToGift FROM CaseA WHERE id = position;
         SELECT position, peopleToGift;

         UPDATE CaseA SET peopleToGift = peopleToGift WHERE id = Counter;
         SET Counter = Counter + 1;
     END WHILE;
END
//  DELIMITER;
DELIMITER ;

问题分析 #

原代码通过随机交换临时表order中的Number值生成排列，但这种随机交换方式无法保证最终得到的是错位排列（即没有元素出现在原位置的排列），因此会出现CaseA.id = position的情况，导致自赠。

修正方案：确保生成合法错位排列 #

针对你的小规模人群场景（11人），我们可以通过「生成排列后合法性检查」的方式解决：每次生成随机排列后，检查是否存在自赠情况，若存在则重新生成，直到得到完全合法的错位排列，再执行分配。同时改用Fisher-Yates洗牌算法，提升排列的随机性和生成效率。

修改后的存储过程代码如下：

CREATE DATABASE IF NOT EXISTS AMIGOINVISIBLE;

USE AMIGOINVISIBLE;

CREATE TABLE IF NOT EXISTS CaseA(
     id INT PRIMARY KEY AUTO_INCREMENT,
     people VARCHAR(50),
     peopleToGift VARCHAR(50)  );

INSERT INTO CaseA (people, peopleToGift) 
VALUES  ('a.tomas', NULL), ('g.rubio', NULL), ('a.pulido', NULL), ('m.fabrega.1', NULL), ('d.lazaro', NULL), ('c.albinya', NULL), ('b.gomez', NULL),
('j.espinoza', NULL), ('j.aguilera', NULL), ('j.da.silva.1', NULL), ('a.chamorro', NULL);

DELIMITER //

CREATE OR REPLACE PROCEDURE randomizePeople()
 BEGIN
     DECLARE times INT;
     DECLARE Counter INT;
     DECLARE hasFixedPoint BOOLEAN;
     DECLARE position INT;
     DECLARE peopleToGift VARCHAR(50);

     SELECT COUNT(*) INTO times FROM CaseA;

     -- 循环生成排列，直到得到无不动点的错位排列
     REPEAT
         DROP TABLE IF EXISTS `order`;
         CREATE TEMPORARY TABLE IF NOT EXISTS `order` (
             id INT PRIMARY KEY AUTO_INCREMENT,
             Number INT
         );
         -- 初始化临时表为顺序排列
         SET Counter = 1;
         WHILE Counter <= times DO
             INSERT INTO `order` (Number) VALUES (Counter);
             SET Counter = Counter + 1;
         END WHILE;

         -- 使用Fisher-Yates洗牌算法打乱顺序，随机性更均匀
         SET Counter = times;
         WHILE Counter > 1 DO
             SET @random = FLOOR(RAND() * Counter) + 1;
             -- 交换当前位置和随机位置的Number值
             SELECT Number INTO @aux FROM `order` WHERE id = Counter;
             UPDATE `order` SET Number = (SELECT Number FROM `order` WHERE id = @random) WHERE id = Counter;
             UPDATE `order` SET Number = @aux WHERE id = @random;
             SET Counter = Counter - 1;
         END WHILE;

         -- 检查是否存在自赠情况（id与Number相等）
         SELECT EXISTS(SELECT 1 FROM `order` WHERE id = Number) INTO hasFixedPoint;
     UNTIL hasFixedPoint = FALSE END REPEAT;

     -- 执行最终分配
     SET Counter = 1;
     WHILE Counter <= times DO
         SELECT Number INTO position FROM `order` WHERE id = Counter;
         SELECT people INTO peopleToGift FROM CaseA WHERE id = position;
         UPDATE CaseA SET peopleToGift = peopleToGift WHERE id = Counter;
         SET Counter = Counter + 1;
     END WHILE;
END
// DELIMITER ;

修改说明 #

• 替换原随机交换逻辑为Fisher-Yates洗牌算法，生成的随机排列更均匀高效；
• 添加REPEAT...UNTIL循环，确保只有当生成的排列完全无自赠情况时，才执行分配；
• 彻底规避自赠问题，完全符合Secret Santa抽签规则。

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