卸数mysql_mysql 增量卸数脚本

#!/usr/bin/perl

use DBI;

$db_name='test';

$user="test";

$passwd="123456";

$dbh="";

$dbh = DBI->connect("dbi:mysql:database=plmsdb;host=10.10.17.5;port=3306",$user,$passwd) or die "can't connect to database ". DBI-errstr;

$UNLOAD_SRC_DBCONN = DBI->connect("dbi:mysql:database=plmsdb;host=10.10.17.5;port=3306",$user,$passwd) or die "can't connect to database ". DBI-errstr;

my $table_name= "$ARGV[0]";

my $str= "$ARGV[1]";

my $hostSql = qq{SELECT column_name from information_schema.columns where table_schema='test' and table_name='$table_name'};

my $DW_DATA_DT ="";

my $datafile="$table_name.txt";

use HTTP::Date qw(time2iso str2time time2iso time2isoz);

my @lstRlst1;

my @lstRlst;

my ($COLUMN_NAME);

my $selStmt = $dbh->prepare($hostSql);

$selStmt->execute();

$selStmt->bind_col(1, \$COLUMN_NAME);;

$selStmt->execute();

while( $selStmt->fetch() ){

print "$COLUMN_NAME\n";

push (@lstRlst1 ,$COLUMN_NAME);

}

$selStmt->finish;

$dbh->disconnect;

my @lstRlst = (@lstRlst1);

##########################################

#=================全局变量区==========================#

if ($#ARGV <0){

print "请输入一个表名参数";

exit(-1);

}

sub printlog

{

my ($LogInfo)= @_;

my $CurrTime = time2iso(time()); # 当前时间

if(!defined($LogInfo) ){$LogInfo="";}

my $StrLog="【${CurrTime}】 \t ${LogInfo} \n";

print $StrLog;

#print LOGFILE $StrLog;

}

my $exportOracleSql="SELECT "; #数据导出的sql

for (my $m=0;$m

if ($m != @lstRlst + 0 - 1){

$exportOracleSql = "$exportOracleSql trim($lstRlst[$m])".", "

}

else{

$exportOracleSql = "$exportOracleSql trim($lstRlst[$m])"}

print "$exportOracleSql\n";

}

my $exportOracleSql="$exportOracleSql from $db_name.$table_name where $str";

print "$exportOracleSql\n";

sub Exportdata{

printlog "开始导出数据!";

my $exportsql=$exportOracleSql;

if($exportsql eq "error"){

return -1;

}

$stmt=$UNLOAD_SRC_DBCONN->prepare($exportsql);

unless ($stmt){

printlog "\n执行prepare SQL语句出错:\n";

printlog $DBI::errstr;

return -1;

}

$stmt->execute;

if ($UNLOAD_SRC_DBCONN->err) {

printlog "\n执行SQL语句出错:\n";

printlog $DBI::errstr;

return -1;

}

my $row=0;

my $size=0;

my $curtime;

my $writeflagsql;

my $tmpstr="";

$row=0;

my $m=0;

open(DATAFILE,">", $datafile) || die (print "Open DATA file failed!!!\n");

while(my $Rows = $stmt->fetchrow_arrayref){

$m=0;

$tmpstr="";

foreach(@$Rows){

$tmpstr=$tmpstr.$Rows->[$m]."|";

$m++;

}

print DATAFILE $tmpstr.$DW_DATA_DT."\n";

$row++;

if(($row%10000) == 0){

printlog "已导出数据$row条！";

}

}

$stmt->finish;

#print FLAGFILE $datafile,"\n";

#print FLAGFILE $row,"\n";

close(DATAFILE);

# close(FLAGFILE);

$curtime=time2iso(time());

printlog "数据已成功导出!";

printlog "一共导出数据${row}条";

return 1;

}

Exportdata