mysql大批量插入数据

Mysql大批量插入数据

1.创建表
2.创建存储过程
3.插入数据

--创建表
CREATE TABLE `sys_person` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `pn_id_card` int(20) NOT NULL COMMENT '身份证号',
  `pn_bank_card` int(20) NOT NULL COMMENT '银行卡号',
  `pn_name` varchar(30) DEFAULT '' COMMENT '姓名',
  `pn_sex` varchar(10) DEFAULT '' COMMENT '性别',
  `pn_level` varchar(100) DEFAULT '' COMMENT '所属档级阶段',
  `pn_staus` varchar(10) DEFAULT '' COMMENT '状态',
  `pn_join_money` varchar(10) DEFAULT '' COMMENT '参保金额',
  `pn_phone` varchar(50) DEFAULT '' COMMENT '联系电话',
  `pn_relocate_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '征迁日期',
  `pn_join_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '参保日期',
  `pn_office_id` int(20) DEFAULT NULL COMMENT '所属办事处',
  `pn_group_id` int(20) DEFAULT NULL COMMENT '征迁村组',
  `pn_community_id` int(20) DEFAULT NULL COMMENT '所属社区',
  `pn_up_user` varchar(50) DEFAULT '' COMMENT '修改者',
  `pn_give_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '发证日期',
  `pn_create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建日期',
  `pn_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
  `pn_join_card` int(20) NOT NULL COMMENT '参保证件号码',
  `pn_address` varchar(2000) DEFAULT '' COMMENT '家庭住址',
  `pn_remark` varchar(2000) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`)
) 
ENGINE = MyISAM ROW_FORMAT = DEFAULT CHARSET=utf8 COMMENT='用户表' 
partition BY RANGE (id) (     

    partition p0 VALUES LESS THAN (100000),     

    partition p1 VALUES LESS THAN (500000),     

    partition p2 VALUES LESS THAN (1000000),     

    partition p3 VALUES LESS THAN (1500000),

    partition p4 VALUES LESS THAN (2000000),

    Partition p5 VALUES LESS THAN MAXVALUE     

);

--存储过程
 delimiter //

 CREATE PROCEDURE BatchInsertData(IN start INT,IN loop_time INT)

BEGIN

 DECLARE Var INT;

 DECLARE ID INT;

 SET Var = 0;

 SET ID= start;

      WHILE Var < loop_time 

        DO



insert into sys_person
    (id,pn_id_card,pn_bank_card,pn_name,pn_sex,pn_level,pn_staus,pn_join_money,pn_phone,
        pn_relocate_date,pn_join_date,pn_office_id,pn_group_id,pn_community_id,pn_up_user,pn_give_date,pn_create_time,
        pn_update_time,pn_join_card,pn_address,pn_remark) 
values
    (id,id,id,CONCAT(ID,'JiaHao Sun'),'男',1,'正常','222','199******',
        adddate('1995-01-01',(rand(ID)*36520) mod 3652),adddate('1995-01-01',(rand(ID)*36520) mod 3652),11,1,1,'Jayla',
        adddate('1995-01-01',(rand(ID)*36520) mod 3652),adddate('1995-01-01',(rand(ID)*36520) mod 3652),
        adddate('1995-01-01',(rand(ID)*36520) mod 3652),6555,'AAA','1111111');

      SET Var = Var + 1;

      SET ID= ID + 1;

      END WHILE;

END;

//

delimiter ; 

--插入数据
CALL BatchInsertData(1,2000000);