MYSQL生成百万条测试数据

在公司测试中难免使用几百万条数据测试，这就需要向自己的数据库中短时间插入百万数据，在这就有脚本可以现成使用，本案例用的是员工和部门的关系，可以举一反三。

1、建表

# 新建库

    create database bigData;
    use bigData;

#1 建表dept

    CREATE TABLE dept(  
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
    dname VARCHAR(20) NOT NULL DEFAULT "",  
    loc VARCHAR(13) NOT NULL DEFAULT ""  
    ) ENGINE=INNODB DEFAULT CHARSET=GBK ;  

#2 建表emp

    CREATE TABLE emp  
    (  
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
    ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
    job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
    hiredate DATE NOT NULL,/*入职时间*/  
    sal DECIMAL(7,2) NOT NULL,/*薪水*/  
    comm DECIMAL(7,2) NOT NULL,/*红利*/  
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
    )ENGINE=INNODB DEFAULT CHARSET=GBK ;

2、设置参数

为什么？当开启二进制日志后(可以执行show variables like 'log_bin'查看是否开启)，如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报“ERROR 1418 (HY000): 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)”这样的错误。

    show variables like 'log_bin_trust_function_creators';
    set global log_bin_trust_function_creators=1;

3、创建函数

随机产生字符串

    DELIMITER $$
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
     DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
     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 $$

随机产生数字做部门编号

    #用于随机产生部门编号
    DELIMITER $$
    CREATE FUNCTION rand_num( )
    RETURNS INT(5)  
    BEGIN   
     DECLARE i INT DEFAULT 0;  
     SET i = FLOOR(100+RAND()*10);  
    RETURN i;  
     END $$
     
    #假如要删除
    #drop function rand_num;

4、创建存储过程

1) 向部门中插入数据过程

    #执行存储过程，往dept表添加随机数据
    DELIMITER $$
    CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  
    BEGIN  
    DECLARE i INT DEFAULT 0;   
     SET autocommit = 0;    
     REPEAT  
     SET i = i + 1;  
     INSERT INTO dept (deptno ,dname,loc ) VALUES ((START+i) ,rand_string(10),rand_string(8));  
     UNTIL i = max_num  
     END REPEAT;  
     COMMIT;  
     END $$

2）向员工表中插入数据过程

    DELIMITER $$
    CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))  
    BEGIN  
    DECLARE i INT DEFAULT 0;   
    #set autocommit =0 把autocommit设置成0  
     SET autocommit = 0;    
     REPEAT  
     SET i = i + 1;  
     INSERT INTO emp (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());  
     UNTIL i = max_num  
     END REPEAT;  
     COMMIT;  
     END $$
     
    #删除
    # DELIMITER ;
    # drop PROCEDURE insert_emp;

5、调用存储过程进行插入数据

    DELIMITER ;
    #执行存储过程，往dept中插入10条数据
    CALL insert_dept(100,10);
     
     
    #执行存储过程，往emp表添加100万条数据
    DELIMITER ;
    CALL insert_emp(100001,1000000);