CREATE TABLE `participantes` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`nome` VARCHAR(50) NULL DEFAULT ‘0’ COLLATE ‘utf8_bin’,
`id_sorteio` INT(11) NULL DEFAULT ‘0’,
PRIMARY KEY (`id`)
)
COLLATE=’utf8_bin’
ENGINE=InnoDB;

insert into participantes (nome) values
(‘Bill Gates’), (‘Amancio Ortega’),
(‘Warren Buffett’), (‘Carlos Slim Helu’),
(‘Jeff Bezos’), (‘Mark Zuckerberg’),
(‘Larry Ellison’), (‘Michael Bloomberg’),
(‘Charles Koch’), (‘David Koch’);

select * from participantes order by rand();

CREATE PROCEDURE `FazerSorteio`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ”
BEGIN

DECLARE Pid, Posicao, Restante int;
DECLARE existe_mais_linhas int DEFAULT 0;
DECLARE Sorteio CURSOR FOR select id from participantes order by rand();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET existe_mais_linhas=1;

set Posicao = 0;

OPEN Sorteio;
meu_loop: LOOP

fetch Sorteio into Pid;

if existe_mais_linhas = 1 then
LEAVE meu_loop;
END IF;

update participantes set id_sorteio = Posicao where id = Pid;

set Posicao = Pid;

END LOOP;

call FazerSorteio;

select
p.id,
p.nome,
p.id_sorteio, pp.nome
from participantes p
inner join participantes pp on pp.id_sorteio = p.id
order by p.id, pp.id_sorteio;