Use Perl to implement split data tables (MySQL) and migrate data instances _perl

As the volume of business grows, tables may need to be split to improve performance.

The following example splits the Www.jb51.net users table into 10 table ttlsa_user_0-ttlsa_user_9.

The split migration data program looks like this:

1. Create Ttlsa_user_0-ttlsa_user_9 Table

Copy Code code as follows:

#!/usr/bin/perl
###################################
### author:www.jb51.net ###
### QQ Group: 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 (a) not NULL auto_increment,
' Email ' varchar not NULL,
' passwd ' varchar not NULL,
' user_name ' varchar 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. Migrating data

Copy Code code as follows:

#!/usr/bin/perl
###################################
### author:www.jb51.net ###
### QQ Group: 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. Appendix

If you don't have that much data, you can easily insert some data for testing. A script that inserts data is provided below.

Copy Code code as follows:

#!/usr/bin/perl
###################################
### author:www.jb51.net ###
### QQ Group: 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 ();