Mysql 批量数据插入- 插入100万条数据

方式1：java生成SQL

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.Random;

public class SQLGenApplication {
    public static void main(String[] args) throws IOException {
        
        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 = new Random();
        
        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 < i ) {
                i2 = r.nextInt(100);
            }
            insertBuf.append(String.format(values, id, line, i, i2));
            if(cnt < perCnt) {
                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 < perCnt) {

            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;