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 $@;

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

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