| 标题 | perl访问mssql并迁移到mysql数据库脚本实例 |
| 内容 | 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; push @fields, $field; } $ddl .= join(,\n, @fields); $ddl .= \n) engine = myisam;\n\n; $target->do($ddl) or die cannot create table $table\n; #创建索引 my $indices = $table_indices{$table}; if($indices) { for(@$indices) { $target->do(create index `$_` on `$table`(`$_`);\n) or die cannot create index on $db.$table$.$_\n; } } #转移数据 my @placeholders = map {'?'} @fields; my $insert_sql = insert delayed into $table values( .(join ', ', @placeholders) . );\n; my $insert = $target->prepare($insert_sql); my $select = $src->prepare(select * from $table;); $select->execute; $select->{'longreadlen'} = 1000; $select->{'longtruncok'} = 1; $target->do(set autocommit = 0;); $target->do(start transaction;); my $rows = 0; while(my @row = $select->fetchrow_array) { $insert->execute(@row); $rows++; } $target->do(commit;); #结束，输出任务信息 my $elapsed = time - $start; print child process $$ for table $db.$table done, $rows records, $elapsed seconds.\n; exit(0); } } print waiting for child processes\n; #等待所有子进程结束 while (wait() != -1) {} my $total_elapsed = time - $total_start; print all tasks from $db finished, $total_elapsed seconds.\n; 这个脚本会根据每一个表fork出一个子进程和相应的数据库连接，因此做这种迁移之前得确保目标mysql数据库配置的最大连接数能承受。 然后在bash下执行 代码如下: for x in {1..11};do ./qq.pl quninfo$x; done for x in {1..11};do ./qq.pl groupdata$x; done 就不用管了，脚本会根据mssql这边表结构来在mysql那边创建一样的结构并配置索引。 |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。