sqlserver数据导入mysql六:对上一轮导数据时出错的sql再执行一遍
来源：互联网 发布：蚂蚁分类信息系统 源码 编辑：程序博客网 时间：2024/05/29 00:31
#!/usr/bin/perluse Encode;use Encode::CN;use DBI;use Switch;use strict;use Net::HandlerSocket;use threads;use Time::HiRes 'time';my $aim_ip="192.168.0.208";my $aim_db_name = "mysqldb";my $hs_port = 9999;my $source_name = "sqldb";my $source_user_name = "sa";my $source_user_psd = "123";my $db_name="mysqldb";my $location="192.168.0.208";my $port="3306";my $db_user="zoe";my $db_pass="123";my @a;print "请输入数字确认修复第几份表的入库"; my $var=0;$var=<STDIN>; chop ($var); my $readtxtname="alltablename_repair_"."$var"."\.txt";my $okouttxtname="alltablename_repairok_"."$var"."\.txt";my $errorouttxtname="alltablename_repairerror_"."$var"."\.txt";my $logouttxtname="alltablename_repairlog_"."$var"."\.txt";my $insertouttxtname="alltablename_inserterror_"."$var"."\.txt";my $string="";open(FILE,"$readtxtname"); while (<FILE>) { chomp; if(defined($_ )) { push @a,$_; } }close(FILE); my $i=0;while($i<=@a){$string=$string.$a[$i];$i++;}#print "$string";my @b=split(/::/,$string);my $count=@b;my $index=0;my @data;my $sql_select;my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);my $format_time;while($index+1<=$count){$sql_select=@b[$index+1];my $dbh=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd,{RaiseError =>1});my $sth=$dbh->prepare("select name,object_id from sys.all_objects where name=\'@b[$index]\'"); $sth->execute(); while (@data=$sth->fetchrow_array()) { #print $data[0].$data[1]; my ($select_columns,$insert_columns,$column_count,$sort_column,$column_types); ($select_columns,$insert_columns,$column_count,$sort_column,$column_types)=get_columns($data[0],$data[1]); my $relt = export_data_in ($select_columns,$insert_columns,$column_count,$sort_column,$data[0],$column_types);print "ok\n"; } $sth->finish; $dbh->disconnect; $index++;} print '所有表的修复完毕！！！！'."\n"; sub export_data_in { #构建SQL my($select_columns,$insert_columns,$columns_count,$sort_column,$table_name,$column_types) = @_; my $res0=export_data($table_name,$sql_select,$insert_columns,$columns_count,$column_types); } sub export_data { my ($table_name,$sql_select,$insert_columns,$columns_count,$column_types); my $data_str=""; my $insert_sql; eval{ my $startTime=time; ($table_name,$sql_select,$insert_columns,$columns_count,$column_types)=@_; my $dbh_mssql=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd,{RaiseError =>1}); $dbh_mssql->{LongTruncOk}=1; $dbh_mssql->{LongReadLen}=1048576; my $sth_select=$dbh_mssql->prepare($sql_select); $sth_select->execute() or die "Cannot execute: ". $sth_select->errstr(); ##生成标识ID my $gid=rand(3200); my @select_col; my $select_data; #while($select_data=$sth_select->fetchrow_arrayref()) #{ # if($data_str ne "") # { # $data_str="$data_str,"; # } # $data_str=$data_str."[$gid,'+',['".join("','",@{$select_data})."']]"; # } my $nn=0; while($select_data=$sth_select->fetchrow_arrayref()) { $select_col[$nn]=[@$select_data]; $nn++; } printf("读出时间%.1f seconds.\n",time-$startTime); my $startTime=time; my @col_data; foreach my $aref (@select_col) { @col_data=@{$aref}; #print @col_data; if($data_str ne '') { $data_str.=' ,'; } my $data_col=""; for(my $nnn=0;$nnn<@col_data;$nnn++) { if ($data_col ne "") { $data_col.=" ,"; } #对geometry值的特殊处理，如果有其它类型需要特殊处理，请在else后加eleif.... if(@$column_types[$nnn] eq "geometry") { $data_col.=" GeomFromText('@col_data[$nnn]',4326)"; }elsif(@$column_types[$nnn] eq "int"){ if(@col_data[$nnn]>4200000000) { my $value=4294967295-@col_data[$nnn]+1; $data_col.='-'."$value"; } else { $data_col.="'@col_data[$nnn]'"; }}elsif(@$column_types[$nnn] eq "date"){ if(@col_data[$nnn] eq '1900-01-01') { $data_col.="'1000-01-01'"; } else { $data_col.="'@col_data[$nnn]'"; }}elsif(@$column_types[$nnn] eq "datetime"){ if(@col_data[$nnn] eq '1900-01-01 00:00:00') { $data_col.="'1000-01-01 00:00:00'"; } else { $data_col.="'@col_data[$nnn]'"; }} else { $data_col.="'@col_data[$nnn]'"; } } #构建插入的时候的值字符串 $data_str.="($data_col)\n"; } #open(FILE,">>all_export_data.txt"); #syswrite(FILE,"$data_str\n"); #close(FILE); printf("组合字符串时间%.1f seconds.\n",time-$startTime);$sth_select->finish;$dbh_mssql->disconnect; # open(FILE,">>all_export_data_fre.txt"); # syswrite(FILE,"$data_str\n"); # close(FILE); $data_str=encode("utf8",decode("gbk",$data_str)); ##测试的时候，查看数据的语句。 # print "\n",$data_str,"\n"; $startTime=time; if($data_str ne "") { my $data_base = "DBI:mysql:$aim_db_name:$aim_ip:$port"; my $dbh_mysql=DBI -> connect($data_base,$db_user,$db_pass);$dbh_mysql->do("SET character_set_client = 'utf8'"); $dbh_mysql->do("SET character_set_connection = 'utf8'"); $data_str=~s/\\//g; #插入的SQL语句 $insert_sql = 'INSERT '.$table_name.'('.$insert_columns.') values '.$data_str .';'; # open(FILE,">>all_export_data_insert.txt"); # syswrite(FILE,"$insert_sql\n"); # close(FILE); my $sth_mysql=$dbh_mysql->prepare($insert_sql); $sth_mysql->execute() or die $dbh_mysql->errstr()."\n" ; $sth_mysql->finish(); $dbh_mysql->disconnect; } undef $data_str; printf("写入时间%.1f seconds.\n",time-$startTime); }; print "An error occurred: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!" if $@;if($@){ open(FILE,">>$logouttxtname"); syswrite(FILE,"$table_name\n"); syswrite(FILE,"$sql_select\n"); syswrite(FILE,"$insert_sql\n"); syswrite(FILE,"$@\n"); ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time()); $format_time=sprintf("%d-%d-%d %d:%d:%d",$year+1990,$mon+1,$mday,$hour,$min,$sec); syswrite(FILE,$format_time."\n"); close(FILE); open(FILE,">>$errorouttxtname"); syswrite(FILE,"$table_name\:\:$sql_select\:\:"); close(FILE); open(FILE,">>$insertouttxtname"); syswrite(FILE,"$table_name\n"); syswrite(FILE,"$data_str\n"); syswrite(FILE,"$@\n"); ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time()); $format_time=sprintf("%d-%d-%d %d:%d:%d",$year+1990,$mon+1,$mday,$hour,$min,$sec); syswrite(FILE,$format_time."\n"); close(FILE); }else{ open(FILE,">>$okouttxtname"); syswrite(FILE,"$table_name\n"); syswrite(FILE,"$sql_select\n"); ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time()); $format_time=sprintf("%d-%d-%d %d:%d:%d",$year+1990,$mon+1,$mday,$hour,$min,$sec); syswrite(FILE,$format_time."\n"); close(FILE); }undef $data_str;print "An error occurred: $@" if $@;} sub get_columns { print "loading columns of $_[0] \n"; my $sql="select col.name,tp.name,col.[is_identity] from sys.all_columns col inner join sys.types tp on col.system_type_id=tp.system_type_id and col.user_type_id=tp.user_type_id where object_id=$_[1]"; my $dbh2=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd); my $cols=$dbh2 -> prepare($sql); $cols->execute(); my $cols_insert = ""; my $cols_select = ""; my $cols_count = 0; my $sort_column=""; my @cols_types; my @col; while(@col= $cols->fetchrow_array()) { my ($col_name,$type_name,$is_identity)=@col; @cols_types[$cols_count]=$type_name; if($cols_count>0) { $cols_insert="$cols_insert,"; $cols_select="$cols_select ,"; } #else #{ #$sort_column="[$col_name]"; #} if($type_name eq "hierarchyid") { $cols_select = "$cols_select [$col_name].ToString() as [$col_name]"; $cols_insert = "$cols_insert$col_name"; } elsif($type_name eq "nvarchar") { $cols_select.="CAST((ISNULL(replace(replace(replace(replace([$col_name],'',''),' ',''),char(10),''),char(13),''),'')) as TEXT) as [$col_name]"; $cols_insert = "$cols_insert$col_name"; } elsif($type_name eq "varchar") { # $cols_select.="CAST(ISNULL(replace(replace(replace(replace([$col_name],'',''),' ',''),'۽',''),'',''),'') as TEXT) as [$col_name]"; $cols_select.="CAST((ISNULL(replace(replace(replace(replace([$col_name],'',''),' ',''),char(10),''),char(13),''),'')) as TEXT) as [$col_name]"; $cols_insert = "$cols_insert$col_name"; } elsif($type_name eq "nchar") { $cols_select.="CAST((ISNULL(replace(replace(replace(replace([$col_name],'',''),' ',''),char(10),''),char(13),''),'')) as TEXT) as [$col_name]"; $cols_insert = "$cols_insert$col_name"; } elsif ($type_name eq "int") { $cols_select="$cols_select ISNULL([$col_name],0) as [$col_name]"; $cols_insert = "$cols_insert$col_name"; } elsif ($type_name eq "numeric") { $cols_select="$cols_select ISNULL([$col_name],0) as [$col_name]"; $cols_insert = "$cols_insert$col_name"; } elsif($type_name eq "geometry") { $cols_select = "$cols_select isnull([$col_name],'POINT (0 0)').STAsText() as $col_name"; $cols_insert = "$cols_insert `$col_name`"; }else { $cols_select="$cols_select [$col_name]"; $cols_insert = "$cols_insert$col_name"; }if($is_identity == 1){$sort_column="[$col_name]";} $cols_count++; } open(FILE,">>$logouttxtname"); syswrite(FILE,"$cols_select\n"); close(FILE); $cols->finish; $dbh2->disconnect; ($cols_select,$cols_insert,$cols_count,$sort_column,\@cols_types); } 0 0
- sqlserver数据导入mysql六:对上一轮导数据时出错的sql再执行一遍
- sqlserver数据导入mysql五:多线程导数据脚本(读取前面拆分的表名进行数据导入)
- MySql折腾小记一：导数据出错，查询超慢
- mysql导入数据出错
- sqlserver数据导入mysql
- Mysql的sql文件过大导入出错
- MySQL 执行.sql文件导入数据和执行sql语句
- mysql导入excel表格数据时出错的解决
- 将sqlserver的数据导入Mysql中
- sqlserver数据导入mysql一: 创建mysql数据库 perl脚本
- 关于MySQL数据库数据通过.sql文件导入数据出错的问题
- sql server 数据导入到mysql数据库 出错？？
- 导数据时用到的SQl语句
- MySQL导入sql文件出错
- SQL Server2005 导入数据出错
- mysql导入数据出错可能性
- mysql执行.sql文件出错
- Mysql 数据 导入到 SqlServer
- Android平台下的JNI开发
- log4j 详细配置
- 腾讯2013实习生笔试题+答案
- 为修补地图而来：亚马逊高管跳槽苹果
- John(hdu 1907 2509)
- sqlserver数据导入mysql六:对上一轮导数据时出错的sql再执行一遍
- STM32——C语言数据类型
- strstr函数的实现
- 什么是Platform SDK
- js获取鼠标位置的各种方法
- 移动硬盘格式化后数据如何恢复
- uidrawcall
- GitHub开源项目，欢迎fork
- item13: 以对象管理资源
