Mysql生成大量测试数据

代码语言：javascript复制

set max_heap_table_size=400000000; # 默认16777216
set global innodb_flush_log_at_trx_commit=0;

创建测试表

代码语言：javascript复制

CREATE TABLE `__test_t1` (
    `id` BIGINT (20) NOT NULL AUTO_INCREMENT,
    `c1` VARCHAR (20) NOT NULL,
    `c2` INT (11) NOT NULL,
    `c3` datetime NOT NULL,
    `c4` INT (11) NOT NULL,
    `c5` VARCHAR (20) NOT NULL,
    `c6` VARCHAR (20) NOT NULL,
    PRIMARY KEY (`id`),
    KEY(`c1`),
    KEY(`c2`,`c3`,`c6`),
    KEY(`c4`,`c5`)
);

生成随机字符串

代码语言：javascript复制

delimiter $$
CREATE FUNCTION rand_string(n int) RETURNS varchar(255) 
begin        
  declare chars_str varchar(100) 
  default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
  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()*62),1));
      set i= i 1;        
  end while;        
  return return_str;    
end $$
delimiter ;


mysql> select rand_string(8);
 ---------------- 
| rand_string(8) |
 ---------------- 
| p7eBc0jl       |
 ---------------- 

创建存储过程灌数据

代码语言：javascript复制

delimiter $$
CREATE  PROCEDURE `insert_data`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      insert into __test_t1(c1,c2,c3,c4,c5,c6) values
      (rand_string(6),FLOOR(RAND() * 100) ,now(),FLOOR(RAND() * 100),rand_string(4),rand_string(8));
            set i=i 1;
    END WHILE;
END $$
delimiter ;

call insert_data(100);

测试 存储过程 字符串

0 人点赞