mysql20数据_mysql 插入20万条数据

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

#include

void pro_ID(char * str,inti){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';