Linux下沒有專門為MSSQL設(shè)計的訪問庫，不過介于MSSQL本是從sybase派生出來的，因此用來訪問Sybase的庫自然也能訪問MSSQL，F(xiàn)reeTDS就是這么一個實現(xiàn)。

Perl中通常使用DBI來訪問數(shù)據(jù)庫，因此在系統(tǒng)安裝了FreeTDS之后，可以使用DBI來通過FreeTDS來訪問MSSQL數(shù)據(jù)庫，例子：

代碼如下:

using DBI;

my $cs = "DRIVER={FreeTDS};SERVER=主機;PORT=1433;DATABASE=數(shù)據(jù)庫;UID=sa;PWD=密碼;TDS_VERSION=7.1;charset=gb2312";

my $dbh = DBI->connect("dbi:ODBC:$cs") or die $@;

因為本人不怎么用windows，為了研究QQ群數(shù)據(jù)庫，需要將數(shù)據(jù)從MSSQL中遷移到MySQL中，特地為了QQ群數(shù)據(jù)庫安裝了一個Windows Server 2008和SQL Server 2008r2,不過過幾天評估就到期了，研究過MySQL的Workbench有從MS SQL Server遷移數(shù)據(jù)的能力，不過對于QQ群這種巨大數(shù)據(jù)而且分表分庫的數(shù)據(jù)來說顯得太麻煩，因此寫了一個通用的perl腳本，用來將數(shù)據(jù)庫從MSSQL到MySQL遷移，結(jié)合bash，很方便的將這二十多個庫上百張表給轉(zhuǎn)移過去了，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的服務(wù)器;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服務(wù)器", "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上創(chuàng)建對應(yīng)的數(shù)據(jù)庫

$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;");

#獲取表結(jié)構(gòu)，用來生成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";

#創(chuàng)建索引

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";

}

}

#轉(zhuǎn)移數(shù)據(jù)

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

#結(jié)束，輸出任務(wù)信息

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";

#等待所有子進程結(jié)束

while (wait() != -1) {}

my $total_elapsed = time - $total_start;

print "All tasks from $db finished, $total_elapsed seconds.\n";

這個腳本會根據(jù)每一個表fork出一個子進程和相應(yīng)的數(shù)據(jù)庫連接，因此做這種遷移之前得確保目標MySQL數(shù)據(jù)庫配置的最大連接數(shù)能承受。

然后在bash下執(zhí)行

代碼如下:

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

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

就不用管了，腳本會根據(jù)MSSQL這邊表結(jié)構(gòu)來在MySQL那邊創(chuàng)建一樣的結(jié)構(gòu)并配置索引。

更多信息請查看IT技術(shù)專欄