mysql批量数据脚本

骑猪看日落 2022-10-26 01:26 152阅读 0赞

一、建表

# 新建库
    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=UTF8 ;

#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=UTF8 ;

二、设置参数log\_bin\_trust\_function\_creators

当开启二进制日志后，如果变量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)**”这样的错误。因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，参数log\_bin\_trust\_function\_creators就会生效，限制存储函数的创建、修改、调用。

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

\#这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：  
windows下my.ini\[mysqld\]加上log\_bin\_trust\_function\_creators=1  
linux下 /etc/my.cnf下my.cnf\[mysqld\]加上log\_bin\_trust\_function\_creators=1

三、创建函数,保证每条数据都不同

随机产生字符串

DELIMITER $$
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN    ##方法开始
     DECLARE chars_str VARCHAR(100) DEFAULT   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
     ##声明一个 字符窜长度为 100 的变量 chars_str ,默认值 
     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));
    ##concat 连接函数 ，substring(a,index,length) 从index处开始截取
     SET i = i + 1;
     END WHILE;
     RETURN return_str;
    END $$

#假如要删除
    #drop function rand_string;

随机产生部门编号

#用于随机产生部门编号
    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;

四、创建存储过程

创建往emp表中插入数据的存储过程

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(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
     UNTIL i = max_num   ##直到 上面也是一个循环
     END REPEAT;  ##满足条件后结束循环
     COMMIT;   ##执行完成后一起提交
     END $$

#删除
    # DELIMITER ;
    # drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程

#执行存储过程，往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 $$

#删除
    # DELIMITER ;
    # drop PROCEDURE insert_dept;

五、调用存储过程

DELIMITER ;
    CALL insert_dept(100,10);

#执行存储过程，往emp表添加50万条数据
    DELIMITER ;    #将 结束标志换回 ;
    CALL insert_emp(100001,500000);

参考[https://www.cnblogs.com/runtimeexception/p/10374522.html][https_www.cnblogs.com_runtimeexception_p_10374522.html]

[https_www.cnblogs.com_runtimeexception_p_10374522.html]: https://www.cnblogs.com/runtimeexception/p/10374522.html