批量插入数据（MySql从一张表中选择字段批量插入另一张表）

75 阅读 0 评论 50 点赞

我是靠谱客的博主朴实冬瓜，最近开发中收集的这篇文章主要介绍批量插入数据（MySql从一张表中选择字段批量插入另一张表），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

/**
创建存储过程（将s_process表中的数据插入到s_process2指定的字段(后缀加2的字段)中）
每次插入多条数据(四个字段)
游标(从第一条数据一直循环至最后一条)
insert into t values(null,'name','address''),(null,'name2','address2')...
 */
DELIMITER $$
CREATE PROCEDURE insertData()
begin
  #定义变量（与s_process里面的字段对应）
  declare SDP_ID2 varchar(200);
  declare CO_ID2 varchar(200);
  declare SD_ID2 varchar(200);
  declare SDP_SHOPID2 varchar(200);
  DECLARE done INT DEFAULT 0;

  #创建游标，并存储数据
  declare pro_test CURSOR for
    select SDP_ID as SDP_ID2, CO_ID as CO_ID2, SD_ID as SD_ID2, SDP_SHOPID as SDP_SHOPID2 from s_process;
  #游标执行完，即遍历结束，查询的结果的最后一条记录也用完后，设置done的值为1。
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  #打开游标
  open pro_test;
  #执行循环
  #记录一次循环的次数，判断是否需要提交sql
  set @num = 0;
  #@value将每条记录的各个字段拼成(575384,120,265353,0),格式
  set @value = '';
  #@sqlValue将每个@value拼成(586766,33,270641,0),(575382,120,265352,0),(575383,120,265352,0)格式
  set @sqlValue = '';
  #执行循环
  posLoop:
    LOOP
      #取游标中的值（在IF done = 1 THEN之前，否则最后一条记录会重复插入）
      FETCH pro_test into SDP_ID2,CO_ID2,SD_ID2,SDP_SHOPID2;
      #判断是否结束循环
      IF done = 1 THEN
        LEAVE posLoop;
      END IF;
      set @num = @num + 1;
      #拼接sql
      #执行插入操作
      set @value = concat('(', SDP_ID2, ',', CO_ID2, ',', SD_ID2, ',', SDP_SHOPID2, ')', ',');
      set @sqlValue = concat(@sqlValue, @value);
      #每次达到5万条时，做一次插入操作
      if @num = 50000 THEN
        #去掉@sqlValue的最后一个","字符
        set @sqlValue = substr(@sqlValue, 1, length(@sqlValue) - 1);
        # 拼接SQL语句并执行
        SET @exeSql =
            concat(
                'insert into s_process2(SDP_ID2,CO_ID2,SD_ID2,SDP_SHOPID2) values ',
                @sqlValue, ';');
        #预定义好sql
        PREPARE stmt FROM @exeSql;
        #执行sql
        EXECUTE stmt;
        #释放掉数据库连接
        DEALLOCATE PREPARE stmt;
        set @num = 0;
        set @value = '';
        set @sqlValue = '';
      end if;
    END LOOP posLoop;
  # 拼接SQL语句并执行
  set @sqlValue = substr(@sqlValue, 1, length(@sqlValue) - 1);
  SET @lastExeSql =
      concat(
          'insert into s_process2(SDP_ID2,CO_ID2,SD_ID2,SDP_SHOPID2) values ',
          @sqlValue, ';');
  #预定义好sql
  PREPARE lastStmt FROM @lastExeSql;
  #执行sql
  EXECUTE lastStmt;
  #释放掉数据库连接
  DEALLOCATE PREPARE lastStmt;
  #释放游标
  CLOSE pro_test;
  #结束查看一共多少条记录
  select count(*) from s_process2;
end $$

call insertData();

插入7千万条数据，一条数据四个字段，用时1 h 43 m 57 s 798 ms