270_DBA_造数_造表

测试表

create table single_table (

id int not null auto_increment,
key1 varchar(100),
key2 varchar(100),
key3 VARCHAR(100),
key_part1 varchar(100),
key_part2 varchar(100),
key_part3 varchar(100),
commen_field varchar(100),

PRIMARY KEY(id), key key1(key1), UNIQUE key uk_key2(key2), key idx_key3(key3), key idx_key_part(key_part1, key_part2, key_part3)
)

造数 

delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i<n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end 
$$

select rand_string(20)

delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end 
$$

select rand_num()
SELECT UUID()

插入

delimiter // 
drop function if exists insert_single_table//
create function insert_single_table(in_len int(11)) returns int(11)
begin  
  declare cur_len int(11) default 0;
   
  while cur_len < in_len do
     insert into single_table(key1,key2,key3,key_part1,key_part2,key_part3,commen_field) values(rand_string(10),uuid(),rand_string(15),rand_string(10),rand_string(10),rand_string(10),rand_string(20));
  set cur_len = cur_len + 1;

  end while; 
  return cur_len;
end  
//
delimiter ;

select insert_single_table(100000);