欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

使用perl实现拆分数据表(mysql)并迁移数据实例

程序员文章站 2022-10-08 09:36:28
随着业务量的增长,可能需要对表进行拆分来提高性能。 下面这个例子是将www.jb51.net的users表拆分成10个表ttlsa_user_0-ttlsa_user_9...

随着业务量的增长,可能需要对表进行拆分来提高性能。

下面这个例子是将www.jb51.net的users表拆分成10个表ttlsa_user_0-ttlsa_user_9。

拆分迁移数据程序如下所示:

1.创建ttlsa_user_0-ttlsa_user_9表


#!/usr/bin/perl
###################################
### author: www.jb51.net ###
### qq群:232608061  ###
### e-mail:service@jb51.net ###
###################################

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();

2.迁移数据


#!/usr/bin/perl
###################################
### author: www.jb51.net ###
### qq群:232608061  ###
### e-mail:service@jb51.net ###
###################################

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();

3.附录

如果没有那么多数据量,可以随意插入一些数据进行测试。下面提供一个插入数据的脚本。


#!/usr/bin/perl
###################################
### author: www.jb51.net ###
### qq群:232608061  ###
### e-mail:service@jb51.net ###
###################################

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-$_\@jb51.net”;
$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();