分拆迁移表数据

àì夳堔傛蜴生んèń 2022-01-16 23:03 103阅读 0赞

[为什么80%的码农都做不了架构师？>>> ][80_]  ![hot3.png][]

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

[80_]: https://my.oschina.net/u/2663968/blog/3051541
[hot3.png]: /images/20220117/05e0078700194e6bb5f7a9e2d4efc16d.png