感谢@tere拧ko。在CONTINUE HANDLER FOR sqlSTATE '23000'抓获“不是唯一的”错误和正确的+1 CNT回来。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_rands$$
CREATE PROCEDURE get_rands(IN cnt INT)
BEGIN
DECLARE CONTINUE HANDLER FOR sqlSTATE '23000' SET cnt = cnt + 1;
DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( topicid INT UNIQUE,topic VARCHAR(128) );
loop_me: LOOP
IF cnt < 1 THEN
LEAVE loop_me;
END IF;
INSERT INTO rands
SELECT topictable.topicid,topictable.topic
FROM topictable
JOIN (SELECT (RAND()*(SELECT MAX(topictable.topicid) FROM topictable)) AS id) AS choices
WHERE topictable.topicid >= choices.id
LIMIT 1;
SET cnt = cnt - 1;
END LOOP loop_me;
END$$
DELIMITER ;
602392714
清零编程群