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

最新推荐文章于 2021-02-02 08:27:02 发布

张小凡vip

最新推荐文章于 2021-02-02 08:27:02 发布

阅读量1.7k

点赞数

分类专栏： myWork 文章标签： sqlserver perl mysql

本文链接：https://blog.csdn.net/zzq900503/article/details/23279097

版权

myWork 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

#!/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=<STDIN>;
 chop ($var); 
my $readtxtname="alltablename_ok_"."$var"."\.txt";
my $outtxtname="alltablename_count_"."$var"."\.txt";


my $data_str;

my @a;
open IN, "<", "$readtxtname" or die "IN: $!";
 while (<IN>) {  
  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_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;  
	 
	 }