分拆迁移表数据

1.安装相关模块
# perl -MCPAN -e shell
cpan> install Module::Build
cpan> install PHP::Strings
2.迁移数据
[codesyntax lang="perl"]

#!/usr/bin/perl文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/

###################################
### author: www.ttlsa.com ###
### QQ群：39514058 ###
### E-mail：service@ttlsa.com ###
###################################文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/

use DBI;
use PHP::Strings qw( :str_pad );文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/

my $driver="DBI:mysql";
my $from_database="ttlsa_com";
my $from_user="root";
my $from_password="";
my $from_host="localhost";文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/

$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'");文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/

my $max=1000000;
my $step=10000;
my $count=0;
my $st=time;文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/

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";文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/

[/codesyntax]文章源自运维生存时间-https://www.ttlsa.com/perl/qian-yi-shu-ju/