<?php
header("content-Type: text/html; charset=utf-8");
///
///把access数据库转换成mysql的SQL语句
///请在命令行运行
///

//连接access数据库
$db = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./test.mdb"), "", "");

//生成sql文件
$file = "test.sql";
save_file($file, "SET NAMES utf8;\n\n");
$tables = get_tables($db);
for($i=0; $i<count($tables); $i++){
	$table = $tables[$i];
	echo("$i\t$table\n");
	$res = odbc_exec($db, $sql = "SELECT * FROM `$table`");

	$fields = get_table_fields($res);
	$structure_sql = get_table_structure($table, $fields)."\n\n";
	$data_sql = iconv("GBK", "UTF-8", get_table_data($res, $table)."\n\n\n");

	save_file($file, $structure_sql);
	save_file($file, $data_sql);
}
echo("ok");

/////////////////////////////////函数//////////////////////////////////////
//保存文件
function save_file($file, $data){
	$fp = fopen($file, ‘a+‘);
	fwrite($fp, $data);
	fclose($fp);
}

//获取数据表
function get_tables($db){
	$res = odbc_tables($db);
	$tables = array();
	while (odbc_fetch_row($res)){
		if(odbc_result($res, "TABLE_TYPE")=="TABLE")
			$tables[] = odbc_result($res, "TABLE_NAME");
	}
	return $tables;
}
//获取表字段
function get_table_fields($res){
	$fields = array();
	$num_fields = odbc_num_fields($res);
	for($i=1; $i<=$num_fields; $i++){
		$item = array();
		$item[‘name‘] = odbc_field_name($res, $i);
		$item[‘len‘] = odbc_field_len($res, $i);
		$item[‘type‘] = odbc_field_type($res, $i);
		$fields[] = $item;
	}
	return $fields;
}
//生成建表SQL
function get_table_structure($table, $fields){
	$primary_key = ‘‘;
	$sql = array();
	foreach($fields as $item){
		if($item[‘type‘]==‘COUNTER‘){
			$sql[] = "\t`".$item[‘name‘]."` int(".$item[‘len‘].") NOT NULL AUTO_INCREMENT";
			$primary_key = $item[‘name‘];
		}else if($item[‘type‘]==‘VARCHAR‘){
			$sql[] = "\t`".$item[‘name‘]."` varchar(".$item[‘len‘].") NOT NULL DEFAULT ‘‘";
		}else if($item[‘type‘]==‘LONGCHAR‘){
			$sql[] = "\t`".$item[‘name‘]."` text NOT NULL";
		}else if($item[‘type‘]==‘INTEGER‘){
			$sql[] = "\t`".$item[‘name‘]."` int(".$item[‘len‘].") NOT NULL DEFAULT ‘0‘";
		}else if($item[‘type‘]==‘SMALLINT‘){
			$sql[] = "\t`".$item[‘name‘]."` smallint(".$item[‘len‘].") NOT NULL DEFAULT ‘0‘";
		}else if($item[‘type‘]==‘REAL‘){
			$sql[] = "\t`".$item[‘name‘]."` tinyint(1) NOT NULL DEFAULT ‘0‘";
		}else if($item[‘type‘]==‘DATETIME‘){
			$sql[] = "\t`".$item[‘name‘]."` datetime NOT NULL";
		}else if($item[‘type‘]==‘CURRENCY‘){
			$sql[] = "\t`".$item[‘name‘]."` float NOT NULL DEFAULT ‘0‘";
		}else{
			$sql[] = "\t`".$item[‘name‘]."` varchar(255) NOT NULL DEFAULT ‘‘";
		}
	}
	return "CREATE TABLE IF NOT EXISTS `$table` (\n".implode(",\n", $sql).($primary_key?",\n\tPRIMARY KEY (`".$primary_key."`)\n":"\n").") ENGINE=MyISAM DEFAULT CHARSET=utf8 ;";
}
//获取表数据
function get_table_data($res, $table){
	$row_sql = array();
	while( $row = odbc_fetch_array($res) ) {
		$row_sql[] = get_row_sql($table, $row);
	}
	return implode("\n", $row_sql);
}
//生成插入SQL
function get_row_sql($table, $row){
	$keys = array_keys($row);
	$values = array_values($row);
	for($i=0;$i<count($values);$i++){
		$values[$i] = addslashes($values[$i]);
	}
	return "INSERT INTO ".$table."(`".implode("`,`", $keys)."`) VALUES(‘".implode("‘,‘",$values)."‘);";
}
?>