用perl做数据库迁移，从MSSQL到MYSQL（-）大概思路
最近手上有一项目，需要把数据库从SQL SERVER切换到MySQL,其业务原因暂且不提。小弟拿到这一项目之后，头痛不已，因为项目中用到了SQL Server2008的一些新的数据类型，包括Geometry,包括hierarchyid（级联ID）这类SQL Server新的数据类型。当然，MySQL自V5之后，支持Geometry但还不支持基于Geometry的运算，因此在做数据迁移的时候，此方面必须要有所考虑。当然，这是后话，先不管三七二十一，先把数据全部导到MySQL之中再说。
在做前期技术选型的时候，我想过用ETL工具，而且手上也有好几个用得比较熟悉的ETL工具，但，一想到要拖拖拉拉建模，而且
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
那么多（本次初步估了一下，涉及到差不多500多张表...），像hierarchyid这种字段，http://chang.fuhao2.com 在转换的时候，还得要在源数据上做转换，头昏，脑涨，奶酸，蛋痛等一切症状全上来了。前不久在某坛子上看到对Perl的介绍，外加掌握Perl会简化Linx的运维工作。于是摩拳擦掌准备用Perl来写一个导数据脚本。由于上弟在之前从没用Perl写过任何脚本，哪怕一行都木有，所以，迅速找到一本Perl的电子本【Perl语言入门 第五版】，人称“小骆驼”。
废话不多说，先说一下，我对程序的大概的思路：
先建数据表，在建表的时候，暂时不做太大的数据字段上边的改多。（之前我还在想，直接把级联ID自动拆分为2个字段，ID与ParentID，但在第一轮测试的过程中，发现因为级联ID被用作了外键，在做转换的时候，会出现字段重复啊，冲突啊神马的东西，而且，这种外键也不是很好自动关联，只好放弃。）
再导数据，用最简单的
方法
快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载
先导出来，从SQL SERVER中查出来，插入到MYSQL就完事。但在这里我做了一个测试，如果把一整张表放到内存中，服务器基本不用干其它事了。于是我在这里把一张数据表拆分了，每次导2000条。。。
最后再查出使用过级联ID（hierarchyid）的字段，再写脚本处理这些表的字段。
用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的表http://www.xinxingfuhao.com
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;
}
用perl做数据库迁移，从MSSQL到MYSQL（三）自动导数据，基本思想，拼SQL语句
哎！这周末过得比上班还累，代码周五基本写完，一直没时间整理，今天晚上整理了发上来。
哦，再次提醒一下哈，本代码是本人第一次写perl很多它的优良特性没用到，因此没能写出perl本应该有的优秀的，可爱的，外行看不大懂的代码。如有真心喜欢Perl的人，请不要见骂，请多多指教。
#!/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)
}
代码上完，稍后有一个总结，谢谢大家听小弟啰嗦。。。。
用perl做数据库迁移，从MSSQL到MYSQL（四）总结
历经三天，终于把这项目搞定了，做下总结吧，以史为鉴。
在确定用Perl来做这件事的时候，公司一老鸟看到了，对我说：“你为什么不用c#来写？第一：你熟练，不用去现查语法神马的；第二：你这不是从21世纪，直接回到原始社会了吗？没有语法智联，全用记事本，这不是有病吗？”我当时笑笑，没太在意，因为我之前在选的时候，就想过C#，但它最大的缺点是，我不能在服务器上边一直跑起，因为服务器是LINUX操作系统，而且最重要的是想给自己一个挑战，为以后在LINUX上做系统管理打一下基础（哎！小弟就真不是一个安份的人。）。
但后来在用Perl的时候，语法真不是问题，因为它和我们的自然语言很相似，而且用到最后，我发现它的简洁真很让人惊奇（当然，这个东西，谁用谁知道）。最大的问题在做数据迁移的时候，遇到了perl垃圾回收上的问题，第一次调试的时候，一张表还好，哪怕一次2000条，都能执行个两三次，很快就过了，但后来，第二次调试，我把数据量变小，每张表只导20条数据，结果导一两张表，就会报“Out of memory!”这真把我整得个。。。哎！
不过后来还好，发现竟然是SQL SERVER建表的时候，nvarchar全用MAX~~~
最后，经测试，此方案基本能导90%左右的表，仍有10%左右的表不能实现自动迁移，问题不详，反正就是导不了数据。
再最后，此乃小弟之愚见，如有不正确的地方，欢迎拍砖。。。
浙公网安备 33021202002483