Perl訪問MSSQL並遷移到MySQL資料庫指令碼執行個體_perl

Linux下沒有專門為MSSQL設計的訪問庫，不過介於MSSQL本是從sybase派生出來的，因此用來訪問Sybase的庫自然也能訪問MSSQL，FreeTDS就是這麼一個實現。
Perl中通常使用DBI來訪問資料庫，因此在系統安裝了FreeTDS之後，可以使用DBI來通過FreeTDS來訪問MSSQL資料庫，例子：

複製代碼 代碼如下:

using DBI;
my $cs = "DRIVER={FreeTDS};SERVER=主機;PORT=1433;DATABASE=資料庫;UID=sa;PWD=密碼;TDS_VERSION=7.1;charset=gb2312";
my $dbh = DBI->connect("dbi:ODBC:$cs") or die $@;

因為本人不怎麼用windows，為了研究QQ群資料庫，需要將資料從MSSQL中遷移到MySQL中，特地為了QQ群資料庫安裝了一個Windows Server 2008和SQL Server 2008r2,不過過幾天評估就到期了，研究過MySQL的Workbench有從MS SQL Server遷移資料的能力，不過對於QQ群這種巨大資料而且分表分庫的資料來說顯得太麻煩，因此寫了一個通用的perl指令碼，用來將資料庫從MSSQL到MySQL遷移，結合bash，很方便的將這二十多個庫上百張表給轉移過去了，Perl代碼如下：

複製代碼 代碼如下:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

die "Usage: qq db\n" if @ARGV != 1;
my $db = $ARGV[0];

print "Connectin to databases $db...\n";
my $cs = "DRIVER={FreeTDS};SERVER=MSSQL的伺服器;PORT=1433;DATABASE=$db;UID=sa;PWD=MSSQL密碼;TDS_VERSION=7.1;charset=gb2312";

sub db_connect
{
    my $src = DBI->connect("dbi:ODBC:$cs") or die $@;
    my $target = DBI->connect("dbi:mysql:host=MySQL伺服器", "MySQL使用者名稱", "MySQL密碼") or die $@;
    return ($src, $target);
}
my ($src, $target) = db_connect;

print "Reading table schemas....\n";

my $q_tables = $src->prepare("SELECT name FROM sysobjects WHERE xtype = 'U' AND name != 'dtproperties';");#擷取所有表名
my $q_key_usage = $src->prepare("SELECT TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;");#擷取表的主鍵
$q_tables->execute;
my @tables = ();
my %keys = ();
push @tables, @_ while @_ = $q_tables->fetchrow_array;

$q_tables->finish;

$q_key_usage->execute();
$keys{$_[0]} = $_[1] while @_ = $q_key_usage->fetchrow_array;
$q_key_usage->finish;

#擷取表的索引資訊
my $q_index = $src->prepare(qq(
    SELECT T.name, C.name
    FROM sys.index_columns I
    INNER JOIN sys.tables T ON T.object_id = I.object_id
    INNER JOIN sys.columns C ON C.column_id = I.column_id AND I.object_id = C.object_id;
));
$q_index->execute;
my %table_indices = ();
while(my @row = $q_index->fetchrow_array)
{
    my ($table, $column) = @row;
    my $columns = $table_indices{$table};
    $columns = $table_indices{$table} = [] if not $columns;
    push @$columns, $column;
}
$q_index->finish;

#在目標MySQL上建立對應的資料庫
$target->do("DROP DATABASE IF EXISTS `$db`;") or die "Cannot drop old database $db\n";
$target->do("CREATE DATABASE `$db` DEFAULT CHARSET = utf8 COLLATE utf8_general_ci;") or die "Cannot create database $db\n";
$target->disconnect;
$src->disconnect;

my $total_start = time;
for my $table(@tables)
{
    my $pid = fork;
    unless($pid)
    {
        ($src, $target) = db_connect;
        my $start = time;
        $src->do("USE $db;");
        #擷取表結構，用來產生MySQL用的DDL
        my $q_schema = $src->prepare("SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ? ORDER BY ORDINAL_POSITION;");
        $target->do("USE `$db`;");
        $target->do("SET NAMES utf8;");
        my $key_column = $keys{$table};
        my $ddl = "CREATE TABLE `$table` ( \n";
        $q_schema->execute($table);
        my @fields = ();
        while(my @row = $q_schema->fetchrow_array)
        {
            my ($column, $nullable, $datatype, $length) = @row;
            my $field = "`$column` $datatype";
            $field .= "($length)" if $length;
            $field .= " PRIMARY KEY" if $key_column eq $column;
            push @fields, $field;
        }
        $ddl .= join(",\n", @fields);
        $ddl .= "\n) ENGINE = MyISAM;\n\n";
        $target->do($ddl) or die "Cannot create table $table\n";
        #建立索引
        my $indices = $table_indices{$table};
        if($indices)
        {
            for(@$indices)
            {
                $target->do("CREATE INDEX `$_` ON `$table`(`$_`);\n") or die "Cannot create index on $db.$table$.$_\n";
            }
        }
        #轉移資料
        my @placeholders = map {'?'} @fields;
        my $insert_sql = "INSERT DELAYED INTO $table VALUES(" .(join ', ', @placeholders) . ");\n";
        my $insert = $target->prepare($insert_sql);
        my $select = $src->prepare("SELECT * FROM $table;");
        $select->execute;
        $select->{'LongReadLen'} = 1000;
        $select->{'LongTruncOk'} = 1;
        $target->do("SET AUTOCOMMIT = 0;");
        $target->do("START TRANSACTION;");
        my $rows = 0;
        while(my @row = $select->fetchrow_array)
        {
            $insert->execute(@row);
            $rows++;
        }
        $target->do("COMMIT;");
        #結束，輸出任務資訊
        my $elapsed = time - $start;
        print "Child process $$ for table $db.$table done, $rows records, $elapsed seconds.\n";
        exit(0);
    }
}
print "Waiting for child processes\n";
#等待所有子進程結束
while (wait() != -1) {}
my $total_elapsed = time - $total_start;
print "All tasks from $db finished, $total_elapsed seconds.\n";

這個指令碼會根據每一個表fork出一個子進程和相應的資料庫連接，因此做這種遷移之前得確保目標MySQL資料庫配置的最大串連數能承受。
然後在bash下執行

複製代碼 代碼如下:

for x in {1..11};do ./qq.pl QunInfo$x; done
for x in {1..11};do ./qq.pl GroupData$x; done

就不用管了，指令碼會根據MSSQL這邊表結構來在MySQL那邊建立一樣的結構並配置索引。