有时候为了测试mysql性能,需要生产一张上万,百万,千万的表,有如下方法可实现
SET GLOBAL log_bin_trust_function_creators = 1;
create function rs(n int)
returns varchar(1024)
begin
declare chars char(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare res varchar(1024) default '';
declare i int default 0;
repeat
set i = i + 1;
set res = concat(res,substring(chars,floor(1+rand()*52),1));
until i=n end repeat;
return res;
end
create procedure inst(n int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into jc_user_kw (user_id,kw_name,kw_price) values(10,rs(10),rn()); -----jc_user_kw 为表名,其他为列名
until i=n end repeat;
commit;
set autocommit = 1;
end
call inst(10000)
create function rn()
returns smallint
begin
declare res smallint default 0;
set res=floor(1+rand()*3000) ; #这里1和3000的位置可以更改为你需要的范围。
return res;
end
create function rs(n int)
returns varchar(1024)
begin
declare chars char(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare res varchar(1024) default '';
declare i int default 0;
repeat
set i = i + 1;
set res = concat(res,substring(chars,floor(1+rand()*52),1));
until i=n end repeat;
return res;
end
create procedure inst(n int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into jc_user_kw (user_id,kw_name,kw_price) values(10,rs(10),rn()); -----jc_user_kw 为表名,其他为列名
until i=n end repeat;
commit;
set autocommit = 1;
end
call inst(10000)