mysql-批量数据脚本

建表dept

CREATE TABLE `dept` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
    `deptName` VARCHAR ( 30 ) DEFAULT NULL,
    `address` VARCHAR ( 40 ) DEFAULT NULL,
    ceo INT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

建表emp

CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `empno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETENMINISTIC......
由于开启过慢查询日志，因此我们开启了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.conf下my.conf[mysqld]加上log_bin_trust_function_creators=1

创建函数，保证每条数据都不同

1、随机产生字符串

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 $$

如果要删除函数，则执行：drop function rand_string
2、随机产生部门编号

DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$

如果要删除函数：drop function rand_num

创建存储过程

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

DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把 autocommit 设置成 0
SET autocommit = 0;
REPEAT
SET i=i+1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

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

DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

如果要删除：drop PROCEDURE insert_dep

调用存储过程

添加数据到部门表

#执行存储过程，往 dept 表添加 1 万条数据
DELIMITER ;
CALL insert_dept(10000);

添加数据到员工表

#执行存储过程，往 emp 表添加 50 万条数据
DELIMITER ;
CALL insert_emp(100000,500000);