MySQL添加测试数据
测试环境需要批量添加测试数据,以下方法通过存储过程执行sql,添加大量数据表记录。 sql脚本: 即sql语句、命令的组合,属于未命名的,每次执行前需要编译。 存储过程: 是命名的sql脚本,经过预编译;执行时不需要再次编译,速度更快。
创建数据表
CREATE TABLE usertb(
id serial,
uname varchar(20) ,
ucreatetime datetime ,
age int(11))
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT;
添加存储过程,v_count为添加记录数
##delimiter将结束命令符合;修改为其他字符$$
delimiter $$
SET AUTOCOMMIT = 0$$
create procedure add_record()
begin
declare v_count decimal (10) default 0 ;
dd:loop
insert into usertb values
(null,concat('用户', v_count),'2010-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+1),'2010-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+2),'2010-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+3),'2010-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+4),'2011-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+5),'2011-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+6),'2011-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+7),'2012-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+8),'2012-01-01 00:00:00',ceiling(rand()*100)),
(null,concat('用户', v_count+9),'2012-01-01 00:00:00',ceiling(rand()*100))
;
commit;
set v_count = v_count+10 ;
if v_count = 100 then leave dd;
end if;
end loop dd ;
end;$$
delimiter ;
执行存储过程
call add_record;
删除存储过程
delimiter $$$
drop procedure if exists add_record$$$
delimiter ;
查询记录数
select count(id) from usertb;
删除记录数
delete from usertb;
注意:本文归作者所有,未经作者允许,不得转载