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

#!/usr/bin/perl
use DBI;
use Switch;

$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";
}

#插入数据的实体
sub export_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;
}
#其实，这个才是真正导数据的方法
sub export_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<$columns_count)
        {
            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;
}

#获得表的列
sub get_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)
}