mysql hierarchyid_用perl做数据库迁移，从MSSQL到MYSQL（三）自动导数据，基本思想，拼SQL语句...

#!/usr/bin/perl

useDBI;useSwitch;$dbh=DBI->connect('dbi:ODBC:##隐去的MSSQL访问ODBC连接##','##隐去的MSSQL访问用户名##','##隐去的MSSQL访问密码##');#获取所有的用户表，当然不想导的表，表名放到not in里边

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

('sysdiagrams')");$sth->execute();my $false_Table;#遍历所有表，呵呵，这里啊，其实用另外一个方法更快。详细请参考：http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html

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

{##测试时用

# @data=$sth->fetchrow_array();

##这个是perl的异常处理，也是后边再加的，因为此次移植的时候，发现很多问题，开始在调试的时候，也没想过加异常处理，但后来发现这个调试的过程，

##实在太痛苦了，才查了资料加的，当然对异常处理，perl好像也有try catch~~但，粗粗翻了下资料，好像是python里边的(当然，这个好像是十分好像，)

##因为我觉得所有的计算机语言都是为了解决某一类问题的，先解决问题了，再去想解决~~因此没有深究。(哎！又啰嗦了)

##哦，资料地址如下：http://perldoc.perl.org/functions/eval.html ，有志深入学习Perl的同学，可以把这个网站收藏了，基本(基本)相当于了MSDN。

eval{#获取某个表的列，并构建 查询，插入，列总数，列类型

##输入参数如下：

###data[0]:表名，data[1]:对像ID

##返回参数描述如下：

###$select_columns：构建SELECT的时候，列字符串

###$insert_columns:构建insert的时候，列字符串。之所以要把这两分开，因为有些类型在select的时候，会用到列属性方法，例如geometry.STAsText()

###$column_count:列数，其实可以从@$column_types得到，但@$columns_types是后边加的，此参数也就没有去掉

###$sort_column:用来排序的字段，因为总结了一下，一般第一个字段都是标识字段，主键，因此，这里只取的第一个字段

###$columns_types:列的类型列表，一个数组。因为sql server里边的某些类型的值，在进mysql的时候，需要做处理，例如geometry

#######另外再啰嗦一句，很少见有能同时返回多个值的东东(当然，可能是我把C#忘得差不多了)

($select_columns,$insert_columns,$column_count,$sort_column,$column_types)=get_columns($data[0],$data[1]);#查询结果。如果是导入失败，会返回False，否则为空

##传入参数？说明请参照上边的输出参数

my $relt = export_data_in ($select_columns,$insert_columns,$column_count,$sort_column,$data[0],$column_types);if( $relt eq "False")

{#记录所有未导入成功的表

$false_Table = "$false_Table,$relt";

}

};##如果捕获异常，记录下表名与错误。在本次数据迁移的过程中，有一种错误类型是捕获不到的“out of memory”，而这个异常又是本次数据库迁移中，遇到过最多的

##异常。因为表的列，很多都是nvarchar(max),其中有一张表最夸张有32列都是nvarchar(max)。最初遇到这个异常的时候，小弟历经磨难，找了很多方法，最后都不太

##凑效，于是，于是，我就有个邪恶的想法：找到现在内容最长的(max_length)，然后把nvarchar(max)类型改为nvarchar(max_length+300),反正我们数据库用了差不多3年，

##我相信再过30年，这类型长度也就最多不过这样，永远不可能4096KB~~~(说这到里，想起DBA总在对字段长度锱铢必较，是很有道理的)

if($@)

{$false_Table = "\n$false_Table,$relt ERROR:".$@;

}

}if($false_Table)

{print "有下列表，数据未导入：$false_Table";

}#插入数据的实体

subexport_data_in

{#构建SQL

($select_columns,$insert_columns,$columns_count,$sort_column,$table_name,$column_types) = @_;$rows_count=0;$dbh2=DBI->connect('dbi:ODBC:##隐去的MSSQL访问ODBC连接##','##隐去的MSSQL访问用户名##','##隐去的MSSQL访问密码##');my $sth_sc=$dbh2->prepare("select count(1) from $table_name");$sth_sc->execute();@data_count=$sth_sc->fetchrow_array();#测试语句，先不让其大于2000

if(@data_count[0]>0)

{@data_count[0]=20;

}my $relt;if(@data_count[0]>2000)

{$begin_cnt = 0;$end_cnt = 2000;while($begin_cnt <= @data_count[0])

{##先测试所有的表，加的测试SQL

$sql_select="select top 10 $insert_columns from $table_name";##正式运行时SQL

# $sql_select="select $insert_columns

# FROM

# (

# SELECT $select_columns,ROW_NUMBER() OVER (ORDER BY $sort_column) AS RowNum

# FROM $table_name

# ) as t

# where t.RowNum BETWEEN $begin_cnt and $end_cnt";

##提示信息

print "exporting data $table_name;total:@data_count[0];now:$begin_cnt \n";##导数据，输入参数？请按上边的

$relt = export_data($table_name,$sql_select,$insert_columns,$columns_count,$column_types);$begin_cnt = $begin_cnt + 2001;$end_cnt = $end_cnt + 2001;if($relt eq "False")

{return $relt;

}

}

}elsif(@data_count[0]>0)

{##测试SQL

$sql_select = "select top 10 $select_columns from $table_name";##正式SQL

# $sql_select = "select $select_columns from $table_name";

print "exporting data $table_name;total:@data_count[0];now:$begin_cnt \n";##导数据，输入参数？同上

$relt = export_data($table_name,$sql_select,$insert_columns,$columns_count,$column_types);

}$relt;

}#其实，这个才是真正导数据的方法

subexport_data

{#($table_name,$sql_select,$insert_columns,$columns_count,@column_types)=($_[0],$_[1],$_[2],$_[3]);

($table_name,$sql_select,$insert_columns,$columns_count,$column_types)=@_;$dbh_mssql=DBI->connect('dbi:ODBC:##隐去的MSSQL访问ODBC连接##','##隐去的MSSQL访问用户名##','##隐去的MSSQL访问密码##');##mysql连接信息

##mysql数据库

my $db_name="##隐去的mysql导入数据库##";##mysql数据库地址

my $location="##隐去的mysql地址##";##mysql数据库端口

my $port="##隐去的MySQL数据库端口##";my $data_base = "DBI:mysql:$db_name:$location:$port";my $sth_select=$dbh_mssql->prepare($sql_select);$sth_select->execute();my $data_string = "";##再次提示，fetchrow_arrayref()要快点~~~而且，不只快一点~~

while(@select_data=$sth_select->fetchrow_array())

{if($data_string ne '')

{$data_string="$data_string ,";

}my $data_col="";my $i=0;while($i

{if ($data_col ne "")

{$data_col="$data_col ,";

}#对geometry值的特殊处理，如果有其它类型需要特殊处理，请在else后加eleif....

if(@$column_types[$i] eq "geometry")

{$data_col="$data_col GeomFromText('@select_data[$i]',4326)"}else{$data_col="$data_col '@select_data[$i]'";

}$i=$i+1;

}#构建插入的时候的值字符串

$data_string="$data_string($data_col)\n";

}if($data_string ne "")

{#mySQL用户名

my $db_user="##隐去的MYSQL用户名##";#mysql密码

my $db_pass="##隐去的MYSQL密码##";my $dbh_mysql=DBI -> connect($data_base,$db_user,$db_pass);#插入的SQL语句

my $insert_sql = "INSERT INTO $table_name($insert_columns)values $data_string ;";my $sth_mysql=$dbh_mysql->prepare($insert_sql);$sth_mysql->execute() or die "插入到MYSQL报错：$dbh_mysql->errstr; SQL语句如下：\n $insert_sql \n";$dbh_mysql->disconnect;

}else{return "False";

}#底下的东西，全是释放变量

undef $sth_select;undef $data_base;undef $dbh_mssql;undef $sth_mysql;undef $dbh_mysql;undef $data_string;undef @select_data;

}#获得表的列

subget_columns

{print "loading columns of $_[0] \n";$dbh2=DBI->connect('dbi:ODBC:##隐去的MSSQL访问ODBC连接##','##隐去的MSSQL访问用户名##','##隐去的MSSQL访问密码##');my $sql="select col.name,tp.name 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 $cols=$dbh2 -> prepare($sql);$cols->execute();my $cols_insert = "";my $cols_select = "";my $cols_count = 0;my $sort_column="";my @cols_types;while(@col= $cols->fetchrow_array())

{

($col_name,$type_name)=@col;@cols_types[$cols_count]=$type_name;if($cols_count>0)

{$cols_insert="$cols_insert ,";$cols_select="$cols_select ,";

}else{$sort_column="[$col_name]";

}#对某些类型的特殊处理：hierarchyid

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 "geometry")

{$cols_select = "$cols_select [$col_name].STAsText() as $col_name";$cols_insert = "$cols_insert `$col_name`";

}else{$cols_select="$cols_select [$col_name]";$cols_insert = "$cols_insert `$col_name`";

}$cols_count++;

}#perl函数的返回很有意思。。。

($cols_select,$cols_insert,$cols_count,$sort_column,\@cols_types)

}