21.批量数据脚本

数据准备

CREATE DATABASE bigData;
use bigData;

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;


CREATE TABLE emp(
  `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `empno` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  `ename` VARCHAR(20) 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;

创建函数,假如报错:this function has none of DETERMINISTIC....

由于开启过慢查询日志,因为我们开启了bin-log,我们就必须为我们的function指定一个参数。

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下【mysqld】加上log_bin_trust_function_creators=1

创建函数

创建随机字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
  BEGIN
    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 $$

创建随机数字

DELIMITER $$
CREATE FUNCTION rand_num()
  RETURNS INT(5)
  BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = floor(1000 + rand() * 10);
    RETURN i;
  END $$

创建存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp(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 emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i), rand_string(6),'SALEMAN', 0001, curdate(),2000,400, rand_num());
    UNTIL  i = max_num
    END REPEAT;
    COMMIT;
  END $$

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),  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+1), rand_string(10),rand_string(8));
    UNTIL  i = max_num
      END REPEAT ;
    COMMIT ;
  END $$

恢复结束符

DELIMITER ;

调用存储过程

CALL insert_emp(100001, 500000);