从上述结果中可以看出，利用上面的存储过程添加100万条随机用户数据是非常耗时的，用了几乎一个小时的时间。下面是优化后的存储过程：
1 DROP PROCEDURE IF EXISTS add_user_optimizition; 2 DELIMITER // 3 create PROCEDURE add_user_optimizition(in num INT) 4 BEGIN 5 DECLARE rowid INT DEFAULT 0; 6 DECLARE firstname CHAR(1); 7 DECLARE name1 CHAR(1); 8 DECLARE name2 CHAR(1); 9 DECLARE lastname VARCHAR(3) DEFAULT ''; 10 DECLARE sex CHAR(1); 11 DECLARE score CHAR(2); 12 SET @exedata = ""; 13 WHILE rowid < num DO 14 SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); 15 SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 16 SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 17 SET sex=FLOOR(0 + (RAND() * 2)); 18 SET score= FLOOR(40 + (RAND() *60)); 19 SET rowid = rowid + 1; 20 IF ROUND(RAND())=0 THEN 21 SET lastname =name1; 22 END IF; 23 IF ROUND(RAND())=1 THEN 24 SET lastname = CONCAT(name1,name2); 25 END IF; 26 IF length(@exedata)>0 THEN 27 SET @exedata = CONCAT(@exedata,','); 28 END IF; 29 SET @exedata=concat(@exedata,"('",firstname,"','",lastname,"','",sex,"','",score,"','",rowid,"')"); 30 IF rowid%1000=0 31 THEN 32 SET @exesql =concat("insert into user_test(first_name,last_name,sex,score,copy_id) values ", @exedata); 33 prepare stmt from @exesql; 34 execute stmt; 35 DEALLOCATE prepare stmt; 36 SET @exedata = ""; 37 END IF; 38 END WHILE; 39 IF length(@exedata)>0 40 THEN 41 SET @exesql =concat("insert into user_test(first_name,last_name,sex,score,copy_id) values ", @exedata); 42 prepare stmt from @exesql; 43 execute stmt; 44 DEALLOCATE prepare stmt; 45 END IF; 46 END // 47 DELIMITER ;
创建一个与上述用户表同样的表结构如下：
1 CREATE TABLE user_test( 2 id INT NOT NULL AUTO_INCREMENT, 3 first_name VARCHAR(10) NOT NULL, 4 last_name VARCHAR(10) NOT NULL, 5 sex VARCHAR(5) NOT NULL, 6 score INT NOT NULL, 7 copy_id INT NOT NULL, 8 PRIMARY KEY (`id`) 9 );
执行优化后的存储过程：
call add_user_optimizition(10000001);