perl高效向mysql插入海量数据有什么好的方法么？

只是一个红薯

perl高效向mysql插入海量数据有什么好的方法么？除了存储过程

kernel69

不懂，帮顶，

laputa73

http://news.ccidnet.com/art/32849/20100420/2039433_1.html
perl生成文本,然后用LOAD DATA

只是一个红薯

多谢啦 回复 3# laputa73


   

grshrd49

自己写个...看....
100000条数据一次插入5000条
用时34秒

1. 
   
2. use DBI;
   
3. use strict;
   
4. 
   
5. #my @uhomedb_test = ("dbname","ip","post","用户名","密码");
   
6. my @uhomedb_test = ("uhomedb_test","xx.xx.xx.xx","3306","uhomer","uhome");
   
7. my $dbh;
   
8. $dbh = &connection(@uhomedb_test);
   
9. 
   
10. my ($datatotal,$d);
    
11. $d = $datatotal = 5000;                #设置每次插入的记录总数
    
12. my $total = 100000;                        #循环最大值
    
13. my $datastring;
    
14. for(1..$total){
    
15.         my $uname = "\'yangxm".$_."\'";
    
16.         my $passwd = "\'11111111\'";
    
17.         #my $xx = xx;
    
18.         #这里可以添加字段,添加后要放到@dataele数组中,并在$sql1的sql语句中添加字段名称
    
19.        
    
20.         my @dataele = ($uname,$passwd);
    
21.         $datatotal = 1 if($_ == $total);
    
22.         ($datastring, $datatotal) = &insertdata(\@dataele,$datastring,$datatotal);
    
23.         if(!$datatotal || ($_ == $total)){
    
24.                 my $sql1 = "INSERT INTO uhomedb_test.yxm_test (username,password) VALUES $datastring;";
    
25.                 print "$_\n";
    
26.                 #print "$sql1\n";
    
27.                 &operate_mysql($dbh,"$sql1","insert");
    
28.         }
    
29.         next if($datatotal);
    
30.        
    
31.         $datatotal = $d;        #还原数据总量
    
32.         $datastring = "";        #清空数据串
    
33. 
    
34. }
    
35. 
    
36. 
    
37. 
    
38. #以下是工具函数，连接数据库、等...
    
39. 
    
40. sub insertdata
    
41. {
    
42.         my ($datatemp,$datastring,$datatotal) = @_;
    
43.         my @dataele = @$datatemp;
    
44.         $datastring = $datastring . "(";
    
45.         foreach(my $i=0; $i<=$#dataele; $i++){
    
46.                 $datastring = $datastring . $dataele[$i];
    
47.                 $datastring = $datastring . "," if($i < $#dataele);
    
48.         }
    
49.         $datastring = $datastring . ")";
    
50.         $datatotal--;
    
51.         #$datastring = $datastring . ",";
    
52.         $datastring = $datastring . "," if($datatotal);
    
53.        
    
54.         return ($datastring , $datatotal);
    
55. }
    
56. 
    
57. sub connection
    
58. {       
    
59.         #@uhomedb_test = ("uhomedb_test","116.228.70.232","3306","uhomer","uhome");
    
60.         my ($dbname,$location,$port,$db_user,$db_pass);
    
61.         my ($database,$dbh);
    
62.         ($dbname,$location,$port,$db_user,$db_pass) = @_;
    
63.         $database = "DBI:mysql:$dbname:$location:$port";
    
64.         return $dbh = DBI->connect($database,$db_user,$db_pass);
    
65. }
    
66. 
    
67. sub operate_mysql
    
68. {
    
69.         #&operate_mysql($dbh,"$sql_word","select|insert");
    
70.         my ($dbh,$sql_word,$control);
    
71.         ($dbh,$sql_word,$control) = @_;
    
72. 
    
73. 
    
74.         if ( $control eq "select" ){
    
75.                 my $sth = $dbh -> prepare($sql_word);
    
76.                 $sth -> execute() or print $dbh->errstr;
    
77.                 my @result ;
    
78.                 while (my @value = $sth -> fetchrow_array){
    
79.                         push @result, @value;
    
80.                         #print "value : @value\n";
    
81.                 }
    
82.                 return @result;
    
83.         }
    
84.         if ( $control eq "insert" ){
    
85.                 my $sth = $dbh -> prepare($sql_word);
    
86.                 $sth -> execute() or print $dbh->errstr;
    
87.         }
    
88. }
    

复制代码

这是表结构

只是一个红薯

多谢啦 回复 5# grshrd49