MYSQL用存储过程创建百万级测试数据表

1.创建随机字符串函数，便于创建名称

-- delimiter $$  
-- 执行出现“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled  
-- (you *might* want to use the less safe log_bin_trust_function_creators variable)” 错误时需要先设置log_bin_trust_function_creators  
-- set global log_bin_trust_function_creators=1;  
  
DROP function if EXISTS rand_string;  
#创建一个指定字符个数的函数  
create function rand_string(n INT)  
#返回字符串，注意：此处关键字是returns 而不是return  
returns varchar(255)  
BEGIN  
    #定义一个临时变量，给变量赋值"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"  
    DECLARE chars_str varchar(100) DEFAULT "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";  
    # 定义返回结果字符串  
    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  

2.创建随机编号生成函数

drop function if exists rand_num;  
CREATE function rand_num()  
returns int(5)  
BEGIN  
    DECLARE i int default 0;  
    set i = FLOOR(10+RAND()*500);  
    return i;  
END  

3.创建数据表 -- 部门表 dept

drop table if EXISTS dept;  
create table dept (  
    deptno MEDIUMINT UNSIGNED not null DEFAULT 0,  
  dname varchar(20) default "",  
    ioc varchar(13) default""  
) ENGINE = myisam default CHARSET ="utf8";  
ALTER table dept add PRIMARY key(deptno);  

4.创建数据表 -- 员工表 emp

drop table if EXISTS emp;  
CREATE TABLE `emp` (  
  `empno` mediumint(8) unsigned NOT NULL DEFAULT "0",  
  `ename` varchar(20) NOT NULL DEFAULT "",  
  `job` varchar(9) NOT NULL DEFAULT "",  
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT "0" COMMENT "上级编号",  
  `hiredate` date NOT NULL COMMENT "入职日期",  
  `salary` decimal(7,2) NOT NULL COMMENT "薪水",  
  `comm` decimal(7,2) NOT NULL COMMENT "红利",  
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT "0" COMMENT "部门编号"  
) ENGINE=MYISAM DEFAULT CHARSET=utf8;  

5.插入测试数据

drop PROCEDURE IF EXISTS insert_emp ;  
  
CREATE PROCEDURE insert_emp (in start_no int(10),in max_num int(10))  
  
BEGIN  
    DECLARE i int default 0;  
    # 设置自动提交为false  
    set autocommit =0;  
    # 开启循环  
    REPEAT  
        set i = i+1;  
        insert into emp values((start_no+i),rand_string(6),"SALESMAN",0001,CURDATE(),rand_num(),400,0002);  
      
    UNTIL i=max_num  
    END REPEAT;  
END  

6.调用存储过程，生成百万数据

call insert_emp(10000,10000000);