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';