sqlserver 和mysql互导数据_sqlserver数据导入mysql七: 对mysql和sqlsever同一张表中的数据进行统计对比...

#!/usr/bin/perl

use Encode;

use Encode::CN;

use DBI;

use Switch;

use strict;

use Net::HandlerSocket;

use threads;

use Time::HiRes 'time';

my $source_name = "sqldb";

my $source_user_name = "sa";

my $source_user_psd = "123";

my $aim_ip="192.168.0.208";

my $aim_db_name = "mysqldb";

my $port="3306";

my $db_user="zoe";

my $db_pass="123";

my $dbh=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd);

# my $sth=$dbh->prepare("select name,object_id from sys.all_objects ao where type='U' and not exists(

# select 1 from sys.all_columns col where col.object_id=ao.object_id and system_type_id=240)");

my $sth=$dbh->prepare("select name,object_id from sys.all_objects where type='U' and is_ms_shipped=0 and name <>'sysdiagrams'");

$sth->execute();

print "请输入数字确认查找第几份表的数量";

my $var=0;

$var=;

chop ($var);

my $readtxtname="alltablename_ok_"."$var"."\.txt";

my $outtxtname="alltablename_count_"."$var"."\.txt";

my $data_str;

my @a;

open IN,"

while () {

chomp;

if(defined($_ ))

{

push @a,$_;

}

}

close IN;

my @data;

my %pos;

my $index= 0;

map {$pos{$_}=$index++}@a;

while (@data=$sth->fetchrow_array())

{

print "$data[0]\n";

if(exists$pos{$data[0]}){

print "yes\n";

my $count=@a[$pos{$data[0]}+1];

print "索引:$pos{$data[0]}\n";

print "表名:@a[$pos{$data[0]}]\n";

print "sql原数量:$count\n";

my $countrelt = count($data[0],$count,$data[1]);

}

else

{

print "no\n";

}

}

sub count

{

my ($table_name,$table_count_fre,$table_id)=@_;

my $dbh2=DBI->connect("dbi:ODBC:$source_name",$source_user_psd);

my $sth_sc=$dbh2->prepare("select count(1) from $table_name");

$sth_sc->execute();

my @data_count=$sth_sc->fetchrow_array();

my $sqlcount=@data_count[0];

$sth_sc->finish;

$dbh2->disconnect;

print "sql数量:$sqlcount\n";

my $data_base = "DBI:mysql:$aim_db_name:$aim_ip:$port";

my $dbhmysql=DBI -> connect($data_base,$db_user,$db_pass);

$dbhmysql->do("SET character_set_client = 'utf8'");

$dbhmysql->do("SET character_set_connection = 'utf8'");

my $mysql="select count(*) from $aim_db_name\.$table_name";

#print "执行语句$mysql\n";

my $mysqlsth=$dbhmysql->prepare($mysql);

$mysqlsth->execute() or die "ERROR::$_[0]::$mysqlsth->errstr";

my @data_count1=$mysqlsth->fetchrow_array();

my$mysqlcount=@data_count1[0];

$mysqlsth->finish;

$dbhmysql->disconnect;

print "mysql数量:$mysqlcount\n";

$data_str="$table_name\t $table_id\tmysql数量:$mysqlcount\tsql原数量:$table_count_fre\tsql现数量：$sqlcount\t\n";

open(FILE,">>$outtxtname");

syswrite(FILE,"$data_str");

close(FILE);

print $data_str;

}