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

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

2. 
   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
   

   创建随机编号生成函数

   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);

转载于:https://www.cnblogs.com/peterpoker/p/9758103.html