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

来源：互联网发布：.cc是什么邮箱域名编辑：程序博客网时间：2024/05/01 16:54

创建表

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);

阅读全文

0 0