mysql批量新增20000数据_Mysql 批量数据插入- 插入100万条数据

方式1：java生成SQL

importjava.io.BufferedReader;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.FileReader;importjava.io.FileWriter;importjava.io.IOException;importjava.util.Random;public classSQLGenApplication {public static void main(String[] args) throwsIOException {

StringBuilder insertBuf= new StringBuilder("INSERT INTO `table1` (`id`, `uid`, `a`, `b`) VALUES");

String values= "('%s', '%s', '%s.00', '%s.00'),";

BufferedReader fis= new BufferedReader(new FileReader("fileParam.txt"));

String line= null;

Random r= newRandom();int cnt = 0;int batch = 0;int perCnt = 2500;while((line = fis.readLine()) != null) {long id = Long.parseLong(line.substring(8));int i = r.nextInt(100);while(i <= 0) {

i= r.nextInt(100);

}int i2 = r.nextInt(100);while(i2

i2= r.nextInt(100);

}

insertBuf.append(String.format(values, id, line, i, i2));if(cnt

cnt++;

}else{

insertBuf.deleteCharAt(insertBuf.length()-1);

insertBuf.append(";");

FileWriter fw= new FileWriter("fileSQL" + batch + ".sql");

fw.write(insertBuf.toString());

fw.flush();

fw.close();

cnt= 0;

batch++;

insertBuf= new StringBuilder("INSERT INTO `table1` (`id`, `uid`, `a`, `b`) VALUES");

}

}if(cnt != 0 && cnt

insertBuf.deleteCharAt(insertBuf.length()-1);

insertBuf.append(";");

FileWriter fw= new FileWriter("fileSQL" + batch + ".sql");

fw.write(insertBuf.toString());

fw.flush();

fw.close();

cnt= 0;

}

fis.close();

}

}

方式2：存储过程式

------------------------------------------- 性能表现

支持100万+数据导入，导入速度15分钟/100w条，

支持调整分批批量数据数量(batchSize 参数)，默认25000条一次入库

支持进度显示，每次入库后，显示当前入库数量

支持断点重新导入，只需要调整(startIdx ，endIdx)参数

------------------------------------------- 存储过程SQL

use `数据库名称`;

DELIMITER $$

drop procedure if exists data_100w_gen$$

create procedure data_100w_gen(IN startIdx int, IN endIdx int, IN prefix varchar(1000), IN surfix varchar(1000), out ex_sql_out longtext)

begin

declare ex_sql longtext default prefix;

-- 超时设置

set global delayed_insert_timeout=20000;

set global connect_timeout = 20000;

set global net_read_timeout = 20000;

set global net_write_timeout = 20000;

while startIdx<=endIdx-1 DO

set ex_sql = concat(ex_sql, "(", CAST(startIdx AS CHAR), surfix, ",");

set startIdx=startIdx+1;

end while;

set ex_sql_out = concat(ex_sql, "(", CAST(endIdx AS CHAR), surfix, ";");

-- select ex_sql_out from dual;

end$$

drop procedure if exists data_batch_gen$$

create procedure data_batch_gen(IN startIdx int, IN endIdx int, IN prefix varchar(1000), IN surfix varchar(1000))

begin

declare batchSize int default 25000;

declare batchSize_1 int default batchSize-1;

declare endIdxGen int default 0;

set @ex_sql_out = "";

if(endIdx < batchSize) then

call data_100w_gen(startIdx, endIdx, prefix, surfix, @ex_sql_out);

PREPARE data_gen_prep FROM @ex_sql_out;

EXECUTE data_gen_prep;

DEALLOCATE PREPARE data_gen_prep;

else

while startIdx<=endIdx DO -- 循环开始

set endIdxGen = startIdx + batchSize_1;

if(endIdxGen > endIdx) then

set endIdxGen = endIdx;

end if;

call data_100w_gen(startIdx, endIdxGen, prefix, surfix, @ex_sql_out);

PREPARE data_gen_prep FROM @ex_sql_out;

EXECUTE data_gen_prep;

DEALLOCATE PREPARE data_gen_prep;

set startIdx = endIdxGen + 1;

select endIdxGen from dual; -- 打印每次生成数量

end while; -- 循环结束

end if;

-- select @ex_sql_out from dual;

end$$

delimiter ;

------------------------------------------- 使用样例：

-- 调用存储过程

call data_batch_gen(1, 1000000, "insert into `table_name` values", ", 'xxx100000000', '1', 'daily', '1,2,3,4,5,6,7', '1', '1', '2020-11-18 13:57:34', NULL, NULL, NULL, '0')");

-- 恢复超时设置

set global delayed_insert_timeout=300;

set global connect_timeout = 10;

set global net_read_timeout = 30;

set global net_write_timeout = 60;

drop procedure if exists data_100w_gen;

drop procedure if exists data_batch_gen;