读写文件
背景及木:现有数据1000w单表,为压力测试准备1亿条数据。
步骤:
1.将1000w条记录,除id外都导入到多个文件中:
i fileName ( i<span style=”color: #0000ff;”>while i<span style=”color: #808080;”><<span style=”color: #800000; font-weight: bold;”>251<span style=”color: #000000;”>
DO
<span style=”color: #0000ff;”>SET fileName<span style=”color: #808080;”>=CONCAT(<span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>flog<span style=”color: #ff0000;”>’,i,<span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>.txt<span style=”color: #ff0000;”>'<span style=”color: #000000;”>);
<span style=”color: #0000ff;”>SET <span style=”color: #008000;”>@STMT :<span style=”color: #808080;”>=CONCAT(“<span style=”color: #0000ff;”>select<span style=”color: #000000;”> xx
,xx
,…. <span style=”color: #0000ff;”>into outfile <span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>temp/”,fileName,”<span style=”color: #ff0000;”>’ lines terminated <span style=”color: #0000ff;”>by <span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>\r\n<span style=”color: #ff0000;”>’ <span style=”color: #0000ff;”>from f_log
<span style=”color: #0000ff;”>WHERE id<span style=”color: #808080;”>>= “,<span style=”color: #800000; font-weight: bold;”>40000<span style=”color: #808080;”>(i<span style=”color: #808080;”>-<span style=”color: #800000; font-weight: bold;”>1),” <span style=”color: #808080;”>AND id<span style=”color: #808080;”>< “,<span style=”color: #800000; font-weight: bold;”>40000<span style=”color: #808080;”><span style=”color: #000000;”>i);
<span style=”color: #0000ff;”>PREPARE STMT <span style=”color: #0000ff;”>FROM <span style=”color: #008000;”>@STMT<span style=”color: #000000;”>;
<span style=”color: #0000ff;”>EXECUTE<span style=”color: #000000;”> STMT;
<span style=”color: #0000ff;”>set i<span style=”color: #808080;”>=i<span style=”color: #808080;”>+<span style=”color: #800000; font-weight: bold;”>1<span style=”color: #000000;”>;
<span style=”color: #0000ff;”>end <span style=”color: #0000ff;”>while<span style=”color: #000000;”>;
<span style=”color: #0000ff;”>END<span style=”color: #000000;”>;
<span style=”color: #808080;”>//<span style=”color: #000000;”>DELIMITER
CALL createManyTable();
2. 将上述多个文件合并到同一个文件,并且在第一列加入id列:
main(String[] args) i=10000000 step=40000= File(“E:/data/f_log_data.txt”( k=1;k<251;k++= File("E:/data/temp/f_log_"+k+".txt"==readFile(file,i+step*}
</span><span style=”color: #0000ff;”>public</span> <span style=”color: #0000ff;”>static</span> StringBuffer readFile(File file,<span style=”color: #0000ff;”>int</span> start) <span style=”color: #0000ff;”>throws</span><span style=”color: #000000;”> IOException{
StringBuffer sb</span>=<span style=”color: #0000ff;”>new</span><span style=”color: #000000;”> StringBuffer();
BufferedReader reader</span>=<span style=”color: #0000ff;”>new</span> BufferedReader(<span style=”color: #0000ff;”>new</span><span style=”color: #000000;”> FileReader(file));
String line</span>=””<span style=”color: #000000;”>;
</span><span style=”color: #0000ff;”>while</span>(line != <span style=”color: #0000ff;”>null</span><span style=”color: #000000;”>){
line </span>=<span style=”color: #000000;”> reader.readLine();
</span><span style=”color: #0000ff;”>if</span>(line == <span style=”color: #0000ff;”>null</span><span style=”color: #000000;”>){
</span><span style=”color: #0000ff;”>break</span><span style=”color: #000000;”>;
}
</span><span style=”color: #0000ff;”>if</span>(line.trim().equalsIgnoreCase(“”<span style=”color: #000000;”>)){
</span><span style=”color: #0000ff;”>continue</span><span style=”color: #000000;”>;
}
start</span>++<span style=”color: #000000;”>;
sb.append(start</span>+”\t”+line.trim()+”\r\n”<span style=”color: #000000;”>);
}
reader.close();
</span><span style=”color: #0000ff;”>return</span><span style=”color: #000000;”> sb;
}
</span><span style=”color: #0000ff;”>public</span> <span style=”color: #0000ff;”>static</span> <span style=”color: #0000ff;”>void</span> writeFile(File file,StringBuffer sb) <span style=”color: #0000ff;”>throws</span><span style=”color: #000000;”> IOException{
BufferedWriter writer </span>= <span style=”color: #0000ff;”>new</span> BufferedWriter(<span style=”color: #0000ff;”>new</span> FileWriter(file,<span style=”color: #0000ff;”>true</span><span style=”color: #000000;”>));
writer.write(sb.toString());
writer.close();
}
</span><span style=”color: #0000ff;”>public</span> <span style=”color: #0000ff;”>void</span> writeFile11() <span style=”color: #0000ff;”>throws</span><span style=”color: #000000;”> IOException{
</span><span style=”color: #008000;”>//</span><span style=”color: #008000;”> TODO Auto-generated method stub</span>
BufferedWriter writer = <span style=”color: #0000ff;”>new</span> BufferedWriter(<span style=”color: #0000ff;”>new</span> FileWriter(<span style=”color: #0000ff;”>new</span> File(“D:/driver/data.txt”),<span style=”color: #0000ff;”>true</span><span style=”color: #000000;”>));
</span><span style=”color: #0000ff;”>for</span>(<span style=”color: #0000ff;”>int</span> i=0;i<1000000;i++<span style=”color: #000000;”>){
</span><span style=”color: #0000ff;”>if</span>(i%10==0<span style=”color: #000000;”>){
writer.write(</span>”赵”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}</span><span style=”color: #0000ff;”>if</span>(i%10==1<span style=”color: #000000;”>){
writer.write(</span>”钱”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}
</span><span style=”color: #0000ff;”>if</span>(i%10==2<span style=”color: #000000;”>){
writer.write(</span>”孙”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}</span><span style=”color: #0000ff;”>if</span>(i%10==3<span style=”color: #000000;”>){
writer.write(</span>”李”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}
</span><span style=”color: #0000ff;”>if</span>(i%10==4<span style=”color: #000000;”>){
writer.write(</span>”郑”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}</span><span style=”color: #0000ff;”>if</span>(i%10==5<span style=”color: #000000;”>){
writer.write(</span>”吴”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}
</span><span style=”color: #0000ff;”>if</span>(i%10==6<span style=”color: #000000;”>){
writer.write(</span>”周”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}</span><span style=”color: #0000ff;”>if</span>(i%10==7<span style=”color: #000000;”>){
writer.write(</span>”王”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}
</span><span style=”color: #0000ff;”>if</span>(i%10==8<span style=”color: #000000;”>){
writer.write(</span>”张”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}</span><span style=”color: #0000ff;”>if</span>(i%10==9<span style=”color: #000000;”>){
writer.write(</span>”刘”+(i/10)+”\t”+ (<span style=”color: #0000ff;”>int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”>);
}
}
writer.close();
}</span></pre>
3. 将合并后的文件导入到数据表中:
data local infile
`xx`,.........................
注意事项:开始考虑使用存储过程来逐步导入到数据表中,但load data命令不能在存储过程中使用。
另外,数据的合并也可以以shell脚本完成,但习惯使用java了,因此以java来完成,显得比较复杂。不过,可以随便复习一下java的读写文件,有算不错的经历。
Q&A
时间问题:生成1亿条数据(在有索引的情况下),用时3个小时。如果使用insert语句,估计会疯掉!