使用perl拆分表并迁移数据

随着业务量的增长，可能需要对表进行拆分来提高性能。 下面这个例子是将www.ttlsa.com的users表拆分成10个表ttlsa_user_0-ttlsa_user_9。 拆分迁移数据程序如下所示： 1.创建ttlsa_user_0-ttlsa_user_9表 [codesyntax lang="perl"] #!/usr/bin/perl ################################### ### author: www.ttlsa.com ### ### QQ群：39514058 ### ### E-mail：service@ttlsa.com ### ################################### use DBI; my $driver="DBI:mysql"; my $from_database="ttlsa"; my $from_user="root"; my $from_password="123456"; 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; for (0..9) { $sql="CREATE TABLE `ttlsa_user_$_` ( `uid` int(10) NOT NULL AUTO_INCREMENT,`email` varchar(50) NOT NULL,`passwd` varchar(40) NOT NULL,`user_name` varchar(20) NOT NULL,PRIMARY KEY (`uid`),UNIQUE KEY `email` (`email`),UNIQUE KEY `user_name` (`user_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT"; $from_dbh->do($sql); } $from_dbh->disconnect(); [/codesyntax] 2.迁移数据 [codesyntax lang="perl"] #!/usr/bin/perl ################################### ### author: www.ttlsa.com ### ### QQ群：39514058 ### ### E-mail：service@ttlsa.com ### ################################### use DBI; my $driver="DBI:mysql"; my $from_database="ttlsa"; my $from_user="root"; my $from_password="123456"; 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'"); $from_dbh->do("set session autocommit=0"); $from_dbh->do("set session unique_checks=0"); $max=600000; $step=10000; for ($i=1;$i $step_i=$i+$step; $sql="select * from ttlsa_users where user_id>=$i and user_id $sth=$from_dbh->prepare($sql); $sth->execute; @value=(); while ($row=$sth->fetchrow_hashref()) { $uid=$row->{uid}; $email=$row->{email}; $passwd=$row->{passwd}; $user_name=$row->{user_name}; $key=substr($user_id,-1); $value[$key].="('$uid','$email','$passwd','$user_name'),"; } for (0..9) { chop($value[$_]); $sql="insert into ttlsa_user_$_ (`uid`,`email`,`passwd`,`user_name`) values" . $value[$_]; $from_dbh->do("$sql"); } } $from_dbh->do("set session autocommit=1"); $from_dbh->do("set session unique_checks=1"); $sth->finish(); $from_dbh->disconnect(); [/codesyntax] 3.附录 如果没有那么多数据量，可以随意插入一些数据进行测试。下面提供一个插入数据的脚本。 [codesyntax lang="perl"] #!/usr/bin/perl ################################### ### author: www.ttlsa.com ### ### QQ群：39514058 ### ### E-mail：service@ttlsa.com ### ################################### use DBI; use Digest::SHA qw(sha1_hex); my $driver="DBI:mysql"; my $from_database="ttlsa"; my $from_user="root"; my $from_password="123456"; 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'"); $from_dbh->do("set session autocommit=0"); $from_dbh->do("set session unique_checks=0"); for (1..100000) { $insert_sql=$from_dbh->prepare("insert into ttlsa_users (email,passwd,user_name) values (?,?,?)"); $email="auto-gre-$_@ttlsa.com"; $data="auto-gre-$_"; $passwd=sha1_hex($data); $user_name="auto-gre-$_"; $insert_sql->execute($email,$passwd,$user_name); } $from_dbh->do("set session autocommit=1"); $from_dbh->do("set session unique_checks=1"); $insert_sql->finish(); $from_dbh->disconnect(); [/codesyntax]