mysql存储过程插入40W条数据

来源:转载

创建表

use test;drop table if exists enterprise;create table enterprise( id bigint(20) not null AUTO_INCREMENT, name varchar(36), status TINYINT DEFAULT '1', primary key (id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT; drop table if exists manager;create table manager( id bigint(20) not null AUTO_INCREMENT, name varchar(36), status TINYINT DEFAULT '1', primary key (id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;drop table if exists enterprise_manager;create table enterprise_manager( enterprise_id bigint(20) not null, manager_id bigint(20) not null, status TINYINT DEFAULT '1', primary key (enterprise_id, manager_id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT 

插入数据

drop procedure if exists addenterprise;delimiter // create procedure addenterprise(count INT)BEGINDECLARE i INT DEFAULT 1;start transaction;while i<=countDO insert into enterprise(name) values(CONCAT("xun",i)); set i = i+1;end while;commit;end //delimiter ;call addenterprise(400000);
drop procedure if exists addmanager;delimiter // create procedure addmanager(count INT)BEGINDECLARE i INT DEFAULT 1;start transaction;while i<=countDO insert into manager(name) values(CONCAT("haha",i)); set i = i+1;end while;commit;end //delimiter ;call addmanager(1000);
drop procedure if exists addenterprisemanagermap;delimiter // create procedure addenterprisemanagermap(count INT)BEGINDECLARE i INT DEFAULT 0;start transaction;while i<countDO insert into enterprise_manager(enterprise_id,manager_id) values(i,FLOOR(1+(Rand()*1000))); set i = i+1;end while;commit;end //delimiter ;call addenterprisemanagermap(400000);

分享给朋友:
您可能感兴趣的文章:
随机阅读: