sqlserver数据导入mysql二: 根据sqlserver表结构创建mysql表的pe

思路是 读取sqlserver的表名和字段 类型

因为sqlserver和mysql中类型不同 ? 把类型做相应的变化 ?

后 拼接建表sql语句 在mysql中运行?

代码如下:

#!/usr/bin/perl
use DBI;
use Switch;
use Encode;
use Encode::CN;

    




	my $source_name = "mysqldata";
my $source_user_name = "sa";
my $source_user_psd = "123";

	
	my $db_name="sqldata";
	my $location="192.168.0.208";
	my $port="3306";
	my $db_user="sa";
	my $db_pass="123";
	
	
my $dbh=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd);
#获取所有的用户表
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();
my $n=0;
while (@data=$sth->fetchrow_array())
{
	#print $data[0].$data[1];
	$n+=1;
	
	#获取列
	get_columns($data[0],$data[1]);
		open(FILE,">>createtableallname.txt");  
syswrite(FILE,"$nn");  
syswrite(FILE,"$data[0]n");  
close(FILE); 
}
$sth->finish;
	$dbh ->disconnect;

##获取所有的列
sub get_columns
{
	$dbh2=DBI->connect("dbi:ODBC:$source_name",$source_user_psd);
	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 = "";
	
	my $n=0;
	my $varlen=2000;
		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"; }
			 case "datetime2" { $type_name="date";}
			 case "money" { $type_name="decimal";}
			 else { $type_name=$type_name;}
		}
		if($type_name eq "varchar")
		{
		
		$n++;
		}
		if($n==0)
		{$n=1}
		$varlen=21000/$n;
	}
	my $cols2=$dbh2 -> prepare($sql);
	$cols2->execute();
	while(@col= $cols2->fetchrow_array())
	{
		($col_name,$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"; }
			 case "datetime2" { $type_name="date";}
			 case "money" { $type_name="decimal";}
			 else { $type_name=$type_name;}
		}
		if($cols_str ne "")
		{
			$cols_str = "$cols_str,n";
		}
		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" 
				{
					if($max_length == -1)
					{
						$cols_str = "$cols_str($varlen)";
					}
					else
					{
					#$cols_str = "$cols_str(985)";
						$cols_str = "$cols_str($max_length)";
					}
				}
				case "numeric"
				{
					$cols_str = "$cols_str($precision,$scale)";
				}
				case "char" 
				{
					if($max_length == -1)
					{
						$cols_str = "$cols_str($varlen)";
					}
					else
					{
					#$cols_str = "$cols_str(985)";
						$cols_str = "$cols_str($max_length)";
					}
				}
				
			}
			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);");
	$cols->finish;
	$cols2->finish;
	$dbh2 ->disconnect;
	
}



sub exec_mysql
{
	
	
	my $data_base = "DBI:mysql:$db_name:$location:$port";
	
	
	my $dbh3=DBI -> connect($data_base,$db_user,$db_pass);
	
		$dbh3->do("SET character_set_client = 'utf8'");
   $dbh3->do("SET character_set_connection = 'utf8'");
	# my	 $data_str=encode("utf-8",decode("gbk",$_[0]));
		my $data_str=$_[0];

	my $sth=$dbh3->prepare($data_str);
	
	
	#open(FILE,">>createtableallname.txt");  
#syswrite(FILE,"$data_str");  
#close(FILE); 
	
	$sth->execute() or die "$data_str----ERROR::$data_str::$dbh3->errstr";
	$dbh3->disconnect;
	print 'ok';
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!