主頁 > 知識庫 > 使用perl實現(xiàn)拆分數(shù)據(jù)表（mysql）并遷移數(shù)據(jù)實例

使用perl實現(xiàn)拆分數(shù)據(jù)表（mysql）并遷移數(shù)據(jù)實例

隨著業(yè)務(wù)量的增長，可能需要對表進行拆分來提高性能。

下面這個例子是將www.jb51.net的users表拆分成10個表ttlsa_user_0-ttlsa_user_9。

拆分遷移數(shù)據(jù)程序如下所示：

1.創(chuàng)建ttlsa_user_0-ttlsa_user_9表

復(fù)制代碼代碼如下:

#!/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.遷移數(shù)據(jù)

復(fù)制代碼代碼如下:

#!/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.附錄

如果沒有那么多數(shù)據(jù)量，可以隨意插入一些數(shù)據(jù)進行測試。下面提供一個插入數(shù)據(jù)的腳本。

復(fù)制代碼代碼如下:

#!/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”;

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

您可能感興趣的文章:

MYSQL數(shù)據(jù)庫數(shù)據(jù)拆分之分庫分表總結(jié)
MySQL按指定字符合并以及拆分實例教程
MySQL截取和拆分字符串函數(shù)用法示例
MySQL 常見數(shù)據(jù)拆分辦法
MySQL 表的垂直拆分和水平拆分

標(biāo)簽：安康海南濟南長沙山西山西崇左喀什

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《使用perl實現(xiàn)拆分數(shù)據(jù)表（mysql）并遷移數(shù)據(jù)實例》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。