MySQL使用存储过程随机获取若干条记录的ID

DROP PROCEDURE `get_rand_result`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_rand_result`(IN `i` INT UNSIGNED) NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN

DECLARE mx INT;
DECLARE mi INT;
DECLARE var TEXT DEFAULT '';
DECLARE result TEXT DEFAULT '';

SELECT MAX(`id`) INTO @mx FROM `users_profile`;
SELECT MIN(`id`) INTO @mi FROM `users_profile`;

WHILE i>0 DO
SELECT FLOOR(@mi + RAND() * (@mx-@mi)) INTO @var;
SET i=i-1;
SELECT CONCAT_WS(',',@result,@var) INTO @result;
END WHILE;

SELECT @result;

END

输出结果示例:123,456,789

发表评论

电子邮件地址不会被公开。 必填项已用*标注