#!/usr/bin/perl Use strict; Use warnings; Use DBI; Die "Usage:qq dbn" if @ARGV!= 1; My $db = $ARGV [0]; Print "Connectin to databases $db ... n"; My $cs = "Driver={freetds}; Server=mssql server; Port=1433;database= $db; Uid=sa; Pwd=mssql password; 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 server", "MySQL User name", "MySQL password") 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 & nbsp 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; &nbs p; 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 $dbn"; $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;"); #获取表结构, to generate the DDL used for MySQL 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;nn"; $target->do ($DDL) or die "Cannot create table $tablen"; #创建索引 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;"); #结束, output task information My $elapsed = time-$start; Print "Child process $$ a for table $db. $table done, $rows Records, $elapsed SECONDS.N"; Exit (0); } } Print "Waiting for child processesn"; #等待所有子进程结束 while (Wait ()!=-1) {} My $total _elapsed = time-$total _start; Print "All tasks from $db finished, $total _elapsed SECONDS.N"; |