批量插入1000w条数据
1.创建部门表
CREATE TABLE dept
(id
int UNSIGNED PRIMARY KEY AUTO_INCREMENT ,deptno
mediumint UNSIGNED NOT NULL DEFAULT 0 ,dname
varchar(20) NOT NULL DEFAULT ‘’ ,Ioc
varchar(13) NOT NULL DEFAULT ‘’
)
ENGINE=InnoDB
DEFAULT CHARSET=GBK;
2.创建员工表
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;
3.创建函数,保证每条数据都不同
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(100+RAND()*10);
RETURN i;
END $$
#假如要删除
DROP FUNCTION 函数名;
4.创建存储过程
创建往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(),2000,400,rand_num());
UNTIL i=max_num
END REPEAT;
COMMIT;
END $$
创建往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, Ioc) VALUES((START+i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
5.调用存储过程
dept
插入十条数据
DELIMITER;
#把;设为结尾
CALL insert_dept(100,10);
#从100开始,插入10条数据
emp
#执行过程,往emp表添加50万条数据
CALL insert_emp(100001,500000);
推荐每次添加50w条数据,循环添加20次,就是1000w条数据哦!!!