分拆迁移表数据

  • A+
所属分类:Perl

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]

weinxin
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: