mysql 插入20万条数据

1. 用c语言写20万条数据

　　

#include<stdio.h>
void pro_ID(char * str,int i){
    if(i%17576==0){
        ++*(str+1);
        *(str+2)=97;
        *(str+3)=97;
        *(str+4)=97;
        return ;
    }

    if(i%676==0){
        ++*(str+2);
        *(str+3)=97;
        *(str+4)=97;
        return ;
    }
    if(i%26==0){
        ++*(str+3);
        *(str+4)=97;
        return ;
    }
    ++*(str+4);
    return ;

}

int main(void){

    FILE * fp;
    fp = fopen("C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/data.txt","a+");
    if(fp == NULL )
        return 0;

    char str[9]="\"aaaa\",\"";
    char str1[30]="\",\"TNT(1stik)\",\"";
    char str2[30]="\",\"TNTredsingle\"";
    int i=0,i1 =0 , i2 = 0;

    for(i = 1 ; i < 2000 ; i++){
        pro_ID(str,i);
        fprintf(fp,"%s%d%s%.2f%s\n",str,1001,str1,5.99,str2);
    }


    return 0 ;
}

2.　　mysql语句

load data infile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/data.txt" into table products fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

实验：直接用这个语句插入时候发现，2000条用了三秒的时间。需要改善

3. 20万用了40秒

SET SESSION BULK_INSERT_BUFFER_SIZE = 256217728 ;
SET SESSION MYISAM_SORT_BUFFER_SIZE = 256217728 ;
load data infile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/data.txt" into table products fields terminated by ',' enclosed by '"' lines terminated by '\r\n';