perl访问mssql并迁移到mysql数据库脚本实例

2016-06-17来源：易贤网

linux下没有专门为mssql设计的访问库，不过介于mssql本是从sybase派生出来的，因此用来访问sybase的库自然也能访问mssql，freetds就是这么一个实现。

perl中通常使用dbi来访问数据库，因此在系统安装了freetds之后，可以使用dbi来通过freetds来访问mssql数据库，例子：

代码如下:

using dbi;

my $cs = driver={freetds};server=主机;port=1433;database=数据库;uid=sa;pwd=密码;tds_version=7.1;charset=gb2312;

my $dbh = dbi->connect(dbi:odbc:$cs) or die $@;

因为本人不怎么用windows，为了研究qq群数据库，需要将数据从mssql中迁移到mysql中，特地为了qq群数据库安装了一个windows server 2008和sql server 2008r2,不过过几天评估就到期了，研究过mysql的workbench有从ms sql server迁移数据的能力，不过对于qq群这种巨大数据而且分表分库的数据来说显得太麻烦，因此写了一个通用的perl脚本，用来将数据库从mssql到mysql迁移，结合bash，很方便的将这二十多个库上百张表给转移过去了，perl代码如下：

代码如下:

#!/usr/bin/perl

use strict;

use warnings;

use dbi;

die usage: qq db\n if @argv != 1;

my $db = $argv[0];

print connectin to databases $db...\n;

my $cs = driver={freetds};server=mssql的服务器;port=1433;database=$db;uid=sa;pwd=mssql密码;tds_version=7.1;charset=gb2312;

sub db_connect

{

my $src = dbi->connect(dbi:odbc:$cs) or die $@;

my $target = dbi->connect(dbi:mysql:host=mysql服务器, mysql用户名, mysql密码) or die $@;

return ($src, $target);

}

my ($src, $target) = db_connect;

print reading table schemas....\n;

my $q_tables = $src->prepare(select name from sysobjects where xtype = 'u' and name != 'dtproperties';);#获取所有表名

my $q_key_usage = $src->prepare(select table_name, column_name from information_schema.key_column_usage;);#获取表的主键

$q_tables->execute;

my @tables = ();

my %keys = ();

push @tables, @_ while @_ = $q_tables->fetchrow_array;

$q_tables->finish;

$q_key_usage->execute();

$keys{$_[0]} = $_[1] while @_ = $q_key_usage->fetchrow_array;

$q_key_usage->finish;

#获取表的索引信息

my $q_index = $src->prepare(qq(

select t.name, c.name

from sys.index_columns i

inner join sys.tables t on t.object_id = i.object_id

inner join sys.columns c on c.column_id = i.column_id and i.object_id = c.object_id;

));

$q_index->execute;

my %table_indices = ();

while(my @row = $q_index->fetchrow_array)

{

my ($table, $column) = @row;

my $columns = $table_indices{$table};

$columns = $table_indices{$table} = [] if not $columns;

push @$columns, $column;

}

$q_index->finish;

#在目标mysql上创建对应的数据库

$target->do(drop database if exists `$db`;) or die cannot drop old database $db\n;

$target->do(create database `$db` default charset = utf8 collate utf8_general_ci;) or die cannot create database $db\n;

$target->disconnect;

$src->disconnect;

my $total_start = time;

for my $table(@tables)

{

my $pid = fork;

unless($pid)

{

($src, $target) = db_connect;

my $start = time;

$src->do(use $db;);

#获取表结构，用来生成mysql用的ddl

my $q_schema = $src->prepare(select column_name, is_nullable, data_type, character_maximum_length from information_schema.columns where table_name = ? order by ordinal_position;);

$target->do(use `$db`;);

$target->do(set names utf8;);

my $key_column = $keys{$table};

my $ddl = create table `$table` ( \n;

$q_schema->execute($table);

my @fields = ();

while(my @row = $q_schema->fetchrow_array)

{

my ($column, $nullable, $datatype, $length) = @row;

my $field = `$column` $datatype;

$field .= ($length) if $length;

$field .= primary key if $key_column eq $column;