1.安装相关模块 # perl -MCPAN -e shell cpan> install Module::Build cpan> install PHP::Strings 2.迁移数据 [codesyntax lang="perl"] #!/usr/bin/perl ################################### ### author: www.ttlsa.com ### ### QQ群：39514058 ### ### E-mail：service@ttlsa.com ### ################################### use DBI; use PHP::Strings qw( :str_pad ); my $driver="DBI:mysql"; my $from_database="ttlsa_com"; my $from_user="root"; my $from_password=""; my $from_host="localhost"; $from_dbh=DBI->connect ("$driver:$from_database:$from_host;user=$from_user;password=$from_password") or die "cannot connect: ". DBI->errstr; $from_dbh->do("set names 'utf8'"); my $max=1000000; my $step=10000; my $count=0; my $st=time; for ($i=1;$i$step_i=$step + $i; $query_st=time; $query="select user_id,email,passwd,user_name from ttlsa_users where user_id >= $i and user_id < $step_i"; $rows_sql="select count(user_id) from ttlsa_users where user_id >= $i and user_id < $step_i"; $rows_sth=$from_dbh->prepare($rows_sql); $query_sth=$from_dbh->prepare($query) or die "cannot prepare sql statement "; $query_sth->execute() or die "cannot prepare sql statement"; $rows_sth->execute(); my @row=(); while (@row=$rows_sth->fetchrow_array()) { $rows_num=$row[0]; } $query_et=time; $query_t=$query_et - $query_st; print "查询语句：$query\n"; print "查询耗时：$query_t\n"; print "查询量：$rows_num\n"; #分100个表00-99，键为表名，值为相对应的数据 my %value=('00'=>[],'01'=>[],'02'=>[],'03'=>[],'04'=>[],'05'=>[],'06'=>[],'07'=>[],'08'=>[],'09'=>[],'10'=>[],'11'=>[],'12'=>[],'13'=>[],'14'=>[],'15'=>[],'16'=>[],'17'=>[],'18'=>[],'19'=>[],'20'=>[],'21'=>[],'22'=>[],'23'=>[],'24'=>[],'25'=>[],'26'=>[],'27'=>[],'28'=>[],'29'=>[],'30'=>[],'31'=>[],'32'=>[],'33'=>[],'34'=>[],'35'=>[],'36'=>[],'37'=>[],'38'=>[],'39'=>[],'40'=>[],'41'=>[],'42'=>[],'43'=>[],'44'=>[],'45'=>[],'46'=>[],'47'=>[],'48'=>[],'49'=>[],'50'=>[],'51'=>[],'52'=>[],'53'=>[],'54'=>[],'55'=>[],'56'=>[],'57'=>[],'58'=>[],'59'=>[],'60'=>[],'61'=>[],'62'=>[],'63'=>[],'64'=>[],'65'=>[],'66'=>[],'67'=>[],'68'=>[],'69'=>[],'70'=>[],'71'=>[],'72'=>[],'73'=>[],'74'=>[],'75'=>[],'76'=>[],'77'=>[],'78'=>[],'79'=>[],'80'=>[],'81'=>[],'82'=>[],'83'=>[],'84'=>[],'85'=>[],'86'=>[],'87'=>[],'88'=>[],'89'=>[],'90'=>[],'91'=>[],'92'=>[],'93'=>[],'94'=>[],'95'=>[],'96'=>[],'97'=>[],'98'=>[],'99'=>[]); while (my $row=$query_sth->fetchrow_hashref()) { $user_id=$row->{user_id}; $email=$row->{email}; $passwd=$row->{passwd}; $user_name=$row->{user_name}; $key=str_pad(substr($user_id,-2),2,"0",STR_PAD_LEFT); push @{$value{$key}},"('$user_id','$email','$passwd','$user_name')"; } foreach my $key (sort keys %value) { if (@{$value{$key}}) { $insert_sql="insert into ttlsa_user_$key (user_id,email,passwd,user_name) values " . join (',',@{$value{$key}}); $num=$from_dbh->do($insert_sql); $count+=$num; } } $move_t=time - $query_et; print "转移时间: $move_t\n"; print "------"x10,"\n"; $count_rows+=$rows_num; } $sum_t=time - $st; print "-"x20,"\n"; print "总耗时：$sum_t\n"; print "总数据量: $count_rows\n"; print "总迁移量: $count\n"; [/codesyntax]
转载于:https://my.oschina.net/766/blog/211365
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
