基于MYSQL存储过程生成1-10万,不重复随机数.

DELIMITER ///*创建生成随机数存储过程*/DROP PROCEDURE IF EXISTS RD1 //CREATE PROCEDURE RD1()BEGINDECLARE NM int;#定义变量DECLARE RW int;#定义变量DECLARE MN int;#定义变量/*创建需要用到临时表1*/DROP TABLE IF EXISTS TMP_TEST1;CREATE TEMPORARY TABLE TMP_TEST1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,NUM INT NOT NULL)ENGINE=MEMORY,CHARSET=utf8;/*创建需要用到临时表2*/DROP TABLE if exists TMP_TEST2;CREATE TEMPORARY TABLE TMP_TEST2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,NUM INT NOT NULL)ENGINE=MEMORY,CHARSET=utf8;SET RW = 100000;#给变量赋予初始值10万7SET MN = (SELECT CAST(REPLACE(LEFT(CURDATE(),7),'-','') AS UNSIGNED INTEGER));#获得当前时间如:201702 这样的格式WHILE RW > 0 DO#循环开始SET NM = (SELECT ROUND(RAND()*100000));#随机数种子INSERT INTO TMP_TEST1(`NUM`) VALUES(NM);#写入临时表SET RW = RW - 1;END WHILE;INSERT INTO TMP_TEST2(`NUM`) SELECT ID FROM TMP_TEST1 ORDER BY NUM ASC;#从临时表1转移数据到临时表2通过order by来生成随机数INSERT INTO RECORD_RM (`ID_RM`,`NUM`,`THEMONTH`) SELECT ID,NUM,(select MN) FROM TMP_TEST2;#从临时表2把数据导入到正式表END //#存储过程结束CALL RD1() //#调用存储过程DROP PROCEDURE RD1//#删除存储过程DELIMITER ;

附上RECORD_RM表建表SQL:

DROP TABLE IF EXISTS RECORD_RM;CREATE TABLE RECORD_RM(ID_RM INT UNSIGNED NOT NULL,THEMONTH INT UNSIGNED NOT NULL,NUM INT UNSIGNED NOT NULL,PRIMARY KEY(ID_RM,THEMONTH))ENGINE=MYISAM,CHARSET=utf8;

总体执行速度10S以内,算是相对较快了.如果有好的办法,希望能在下面评论告诉我下,谢谢!