茄子在线看片免费人成视频,午夜福利精品a在线观看,国产高清自产拍在线观看,久久综合久久狠狠综合

linux下沒(méi)有專門為mssql設(shè)計(jì)的訪問(wèn)庫(kù)，不過(guò)介于mssql本是從sybase派生出來(lái)的，因此用來(lái)訪問(wèn)sybase的庫(kù)自然也能訪問(wèn)mssql，freetds就是這么一個(gè)實(shí)現(xiàn)。

perl中通常使用dbi來(lái)訪問(wèn)數(shù)據(jù)庫(kù)，因此在系統(tǒng)安裝了freetds之后，可以使用dbi來(lái)通過(guò)freetds來(lái)訪問(wèn)mssql數(shù)據(jù)庫(kù)，例子：

代碼如下:

using dbi;

my $cs = driver={freetds};server=主機(jī);port=1433;database=數(shù)據(jù)庫(kù);uid=sa;pwd=密碼;tds_version=7.1;charset=gb2312;

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

因?yàn)楸救瞬辉趺从脀indows，為了研究qq群數(shù)據(jù)庫(kù)，需要將數(shù)據(jù)從mssql中遷移到mysql中，特地為了qq群數(shù)據(jù)庫(kù)安裝了一個(gè)windows server 2008和sql server 2008r2,不過(guò)過(guò)幾天評(píng)估就到期了，研究過(guò)mysql的workbench有從ms sql server遷移數(shù)據(jù)的能力，不過(guò)對(duì)于qq群這種巨大數(shù)據(jù)而且分表分庫(kù)的數(shù)據(jù)來(lái)說(shuō)顯得太麻煩，因此寫了一個(gè)通用的perl腳本，用來(lái)將數(shù)據(jù)庫(kù)從mssql到mysql遷移，結(jié)合bash，很方便的將這二十多個(gè)庫(kù)上百?gòu)埍斫o轉(zhuǎn)移過(guò)去了，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的服務(wù)器;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服務(wù)器, 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;

#在目標(biāo)mysql上創(chuàng)建對(duì)應(yīng)的數(shù)據(jù)庫(kù)

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

#獲取表結(jié)構(gòu)，用來(lái)生成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;

#創(chuàng)建索引

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;

}

}

#轉(zhuǎn)移數(shù)據(jù)

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

#結(jié)束，輸出任務(wù)信息

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;

#等待所有子進(jìn)程結(jié)束

while (wait() != -1) {}

my $total_elapsed = time - $total_start;

print all tasks from $db finished, $total_elapsed seconds.\n;

這個(gè)腳本會(huì)根據(jù)每一個(gè)表fork出一個(gè)子進(jìn)程和相應(yīng)的數(shù)據(jù)庫(kù)連接，因此做這種遷移之前得確保目標(biāo)mysql數(shù)據(jù)庫(kù)配置的最大連接數(shù)能承受。

然后在bash下執(zhí)行

代碼如下:

for x in {1..11};do ./qq.pl quninfo$x; done

for x in {1..11};do ./qq.pl groupdata$x; done

就不用管了，腳本會(huì)根據(jù)mssql這邊表結(jié)構(gòu)來(lái)在mysql那邊創(chuàng)建一樣的結(jié)構(gòu)并配置索引。