MySQL使用存储过程创建百万级别测试数据

建表

CREATE TABLE `usertb` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) DEFAULT NULL,
  `ucreatetime` datetime DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=76601101 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

 

创建存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`( )
BEGIN 
declare v_cnt decimal (10)  default 0 ;

DECLARE uname VARCHAR(20);
DECLARE uname2 VARCHAR(20);
DECLARE uname3 VARCHAR(20);
DECLARE uname4 VARCHAR(20);
DECLARE uname5 VARCHAR(20);
  -- 随机姓名 可根据需要增加/减少样本
set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
 
set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
 
dd:loop
        set v_cnt = v_cnt+1 ;
				
				-- length(@surname)/3 是因为中文字符占用3个长度
				set uname = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
				set uname2 = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
				set uname3 = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
				set uname4 = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
			  set uname5 = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));

				
						insert  into usertb values
									(null,uname,concat(floor(2010+rand()*10),'-',floor(1+rand()*11),'-',floor(1+rand()*26),' ', floor(10+rand()*10),':',floor(10+rand()*49),':',floor(10+rand()*49)),FLOOR(1 + (RAND() * 99))),
									(null,uname2,concat(floor(2010+rand()*10),'-',floor(1+rand()*11),'-',floor(1+rand()*26),' ', floor(10+rand()*10),':',floor(10+rand()*49),':',floor(10+rand()*49)),FLOOR(1 + (RAND() * 99))),
									(null,uname3,concat(floor(2010+rand()*10),'-',floor(1+rand()*11),'-',floor(1+rand()*26),' ', floor(10+rand()*10),':',floor(10+rand()*49),':',floor(10+rand()*49)),FLOOR(1 + (RAND() * 99))),
									(null,uname4,concat(floor(2010+rand()*10),'-',floor(1+rand()*11),'-',floor(1+rand()*26),' ', floor(10+rand()*10),':',floor(10+rand()*49),':',floor(10+rand()*49)),FLOOR(1 + (RAND() * 99))),
									(null,uname5,concat(floor(2010+rand()*10),'-',floor(1+rand()*11),'-',floor(1+rand()*26),' ', floor(10+rand()*10),':',floor(10+rand()*49),':',floor(10+rand()*49)),FLOOR(1 + (RAND() * 99)));
								
            if  v_cnt = 1000000 then leave dd;
            end if;
        end loop dd ;
	
END;

　　

经过测试创建5百万数据花了大约100秒