使用MySQL可视化客户端，例如SQLyog，Navicat等，只编写SQL语句，使用2的N次方原理，快速初始化百万千万条数据

电玩女神 2022-09-09 09:52 11阅读 0赞

**目录**

1.思路

2.创建表

3.具体操作

4.其他快速插入百万条数据的方法

4.1Java代码批量插入

4.2存储过程批量插入

--------------------

# 1.思路 #

使用MySQL可视化客户端，例如SQLyog，Navicat for MySQL等，只编写SQL语句，使用2的N次方原理，快速初始化百万千万条数据。

点17下，2的N次方就是13万多条数据。

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eLOQ_size_20_color_FFFFFF_t_70_g_se_x_16][]

# 2.创建表 #

临时中间表

> CREATE TABLE \`insertdemo\` (  
>   \`id\` bigint(20) NOT NULL AUTO\_INCREMENT,  
>   \`idvalue\` bigint(20) DEFAULT NULL,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

百万数据表

> CREATE TABLE \`insert100\` (  
>   \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
>   \`field1\` varchar(25) DEFAULT NULL,  
>   \`field2\` varchar(25) DEFAULT NULL,  
>   \`field3\` varchar(25) DEFAULT NULL,  
>   \`field4\` varchar(25) DEFAULT NULL,  
>   \`field5\` varchar(25) DEFAULT NULL,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#       3.具体操作 #

第一步：初始化10万条种子数据，运行17下，2的N次方就是13万多条数据。

> INSERT INTO \`insertdemo\`(\`idvalue\`) SELECT \`idvalue\` FROM insertdemo;

第二步：快速插入100万条数据，快速执行10次。

INSERT INTO \`insert100\`(\`field1\` ,\`field2\`,\`field3\`,\`field4\`,\`field5\`)  
SELECT 'field1','field2','field3','field4','field5' FROM \`insertdemo\`;

# 4.其他快速插入百万条数据的方法 #

##    4.1Java代码批量插入 ##

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

##    4.2存储过程批量插入 ##

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 ;

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eLOQ_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/5d1e96f8c25e414d8d35c509eac4ef93.png