用perl做数据库迁移，从MSSQL到MYSQL（二）自动建表

话不多说，直接上代码。

?

#!/usr/bin/perl

use DBI;

use Switch;

 

#申请连接资源，因为Perl连接SQL Server只能用ODBC，如果还没部署好环境的，请参见小弟另一篇博文  【PERL连MSSQL】 http://www.cnblogs.com/davyfamer/archive/2012/05/31/2528818.html

$dbh=DBI->connect('dbi:ODBC:##隐去的ODBC数据库连接##','##隐去的MSSQL用户名##','##隐去的MSSQL数据库密码##');

#获取所有需要导入到MySQL的表

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();

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

{

    #@data=$sth->fetchrow_array();   #测试时禁用循环，只看一张表

    #获取列

    get_columns($data[0],$data[1]);

}

 

#$dbh ->disconnect;   #添加了断开语句后，会报错，难道ODBC不需要断开？有点头晕。

##获取所有的列

sub get_columns

{

    $dbh2=DBI->connect('dbi:ODBC:##隐去的ODBC数据库连接##','##隐去的MSSQL的用户名##','##隐去的MSSQL数据库的密码##');

    my $sql="select col.name,tp.name,col.max_length,col.[precision],col.[scale],col.[is_nullable],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 $cols=$dbh2 -> prepare($sql);

    $cols->execute();

    my $cols_str = "";

     

    while(@col= $cols->fetchrow_array())

    {

        ($col_name,$type_name,$max_length,$precision,$scale,$is_nullable,$is_identity)=@col;

        #做类型转换。。。

        switch ( $type_name)

        {

             case "nchar"  { $type_name="char"; }

             case "bit"  { $type_name="boolean"; }

             case "ntext"  { $type_name="text"; }

             case "nvarchar"  { $type_name="varchar"; }

             else { $type_name=$type_name;}

        }

        if($cols_str ne "")

        {

            $cols_str = "$cols_str, \n";

        }

        #对hierarchyid 做特殊处理

        if($type_name eq "hierarchyid")

        {

            if($cols_str eq "")

            {

#               $cols_str = "$cols_str `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`parent_id` INT";

                $cols_str = "$cols_str `$col_name` varchar(31)";

            }

            else

            {

                $cols_str = "$cols_str `$col_name`varchar(31)";

            }

        }

        else

        {

            $cols_str = "$cols_str `$col_name` $type_name";

            switch($type_name)

            {

                case "varchar"

                {  

                    #sql server中的VARCHAR(MAX)

                    if($max_length == -1)

                    {

                        $cols_str = "$cols_str(65535)";

                    }

                    else

                    {

                        $cols_str = "$cols_str($max_length)";

                    }

                }

                case "numeric"

                {

                    $cols_str = "$cols_str($precision,$scale)";

                }

            }

            #是否能为空

            if($is_nullable == 0)

            {

                $cols_str="$cols_str NOT NULL ";

            }

            #是否为标识符

            if($is_identity == 1)

            {

                $cols_str="$cols_str AUTO_INCREMENT PRIMARY KEY";

            }

        }

    }

    #print "create table IF NOT EXISTS `$_[0]`($cols_str);";

    exec_mysql( "create table IF NOT EXISTS `$_[0]`($cols_str);");

    #$dbh2 ->disconnect;  #这一句加了会报错

     

}

#在MySQL中建表

sub exec_mysql

{

    my $db_name="##隐去的MySQL目标数据库名##";

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

    my $data_base = "DBI:mysql:$db_name:$location:$port";

     

    my $db_user="##隐去的MySQL数据库用户名##";

    my $db_pass="##隐去的MySQL数据库密码##";

    my $dbh3=DBI -> connect($data_base,$db_user,$db_pass);

    my $sth=$dbh3->prepare($_[0]);

     

    $sth->execute() or die "ERROR::$_[0]::$dbh3->errstr";

    $dbh3->disconnect;

}