<script type="text/javascript"> </script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script>
通常我们都用 phpMyAdmin 来导出,不过如果你的数据库如果存在下列问题,那么 phpMyAdmin 也无能为力。
- 数据库的字符集与应用程序的字符集不一致;
- 应用程序用错误的编码将数据保存到了数据库中;
- 用 phpMyAdmin 和 mysqldump 导出的数据总是乱码。
总之你用 phpMyAdmin 和 mysqldump 导出的数据有乱码时,就试试看这个脚本吧。
使用很简单:
php export_db.php 数据库名 [-h 主机名] [-c 字符集] [-f 输出文件名] [-u 用户名] [-p]
数据库名是必须提供的,其他参数如果没有提供则使用下面的
默认值:
默认主机名 : localhost
默认字符集 : utf8
默认用户名 : root
默认密码 : (无)
默认输出文件 : 数据库名.sql
这个脚本的导出结果就是一个 .sql 文件,只有 insert 语句。
所以数据结构需要单独导出,不过这个就不存在字符集问题了。
源代码:
<?
php
if ( ! function_exists ( ' mysql_connect ' )) {
if (DIRECTORY_SEPARATOR == ' / ' ) {
dl ( ' php_mysql.so ' );
} else {
dl ( ' php_mysql.dll ' );
}
}
$database = null ;
if ( isset ( $argv [ 1 ]))
{
$database = $argv [ 1 ];
} else {
display_help();
exit ;
}
$optional_args = array (
' -h ' => ' hostname ' ,
' -c ' => ' charset ' ,
' -f ' => ' filename ' ,
' -u ' => ' username '
);
$options = array (
' hostname ' => ' localhost ' ,
' charset ' => ' utf8 ' ,
' filename ' => ' %s.sql ' ,
' username ' => ' root ' ,
);
$input_password = false ;
for ( $i = 2 ; $i < $argc ; $i ++ ) {
$arg = $argv [ $i ];
if ( $arg == ' -p ' ) {
$input_password = true ;
continue ;
}
if ( isset ( $optional_args [ $arg ])) {
$value_name = $optional_args [ $arg ];
if ( isset ( $argv [ $i + 1 ])) {
$options [ $value_name ] = $argv [ $i + 1 ];
$i ++ ;
}
}
}
if ( $input_password ) {
echo " password: " ;
fscanf (STDIN , ' %s ' , $password );
$options [ ' password ' ] = $password ;
echo " " ;
} else {
$options [ ' password ' ] = '' ;
}
if ( $database == null ) {
display_help();
exit ;
}
mysql_connect ( $options [ ' hostname ' ] , $options [ ' username ' ] , $options [ ' password ' ]);
mysql_select_db ( $database );
mysql_query ( " SET NAMES '{$options['charset']}' " );
echo $options [ ' hostname ' ];
echo " " ;
echo $options [ ' username ' ];
echo " " ;
echo $options [ ' password ' ];
echo " " ;
echo $database ;
echo " " ;
// 设置要导出的表
$tables = list_tables( $database );
$filename = sprintf ( $options [ ' filename ' ] , $database );
$fp = fopen ( $filename , ' w ' );
foreach ( $tables as $table ) {
dump_table( $table , $fp );
}
fclose ( $fp );
mysql_close ();
echo " done. " ;
exit ;
function list_tables( $database )
{
$rs = mysql_list_tables ( $database );
$tables = array ();
while ( $row = mysql_fetch_row ( $rs )) {
$tables [] = $row [ 0 ];
}
mysql_free_result ( $rs );
return $tables ;
}
function dump_table( $table , $fp = null )
{
$need_close = false ;
if ( is_null ( $fp )) {
$fp = fopen ( $table . ' .sql ' , ' w ' );
$need_close = true ;
}
fwrite ( $fp , " -- -- {$table} -- " );
$rs = mysql_query ( " SELECT * FROM `{$table}` " );
while ( $row = mysql_fetch_row ( $rs )) {
fwrite ( $fp , get_insert_sql( $table , $row ));
}
mysql_free_result ( $rs );
if ( $need_close ) {
fclose ( $fp );
}
fwrite ( $fp , " " );
}
function get_insert_sql( $table , $row )
{
$sql = " INSERT INTO `{$table}` VALUES ( " ;
$values = array ();
foreach ( $row as $value ) {
$values [] = " ' " . mysql_real_escape_string ( $value ) . " ' " ;
}
$sql .= implode ( ' , ' , $values ) . " ); " ;
return $sql ;
}
function display_help()
{
echo <<< EOT
syntax :
php export_db . php database [ - h hostname] [ - c charset] [ - f filename] [ - u username] [ - p]
defualt hostname : localhost
default charset : utf8
default username : root
default password : (none)
default filename : [database] . sql
EOT;
}
?>
if ( ! function_exists ( ' mysql_connect ' )) {
if (DIRECTORY_SEPARATOR == ' / ' ) {
dl ( ' php_mysql.so ' );
} else {
dl ( ' php_mysql.dll ' );
}
}
$database = null ;
if ( isset ( $argv [ 1 ]))
{
$database = $argv [ 1 ];
} else {
display_help();
exit ;
}
$optional_args = array (
' -h ' => ' hostname ' ,
' -c ' => ' charset ' ,
' -f ' => ' filename ' ,
' -u ' => ' username '
);
$options = array (
' hostname ' => ' localhost ' ,
' charset ' => ' utf8 ' ,
' filename ' => ' %s.sql ' ,
' username ' => ' root ' ,
);
$input_password = false ;
for ( $i = 2 ; $i < $argc ; $i ++ ) {
$arg = $argv [ $i ];
if ( $arg == ' -p ' ) {
$input_password = true ;
continue ;
}
if ( isset ( $optional_args [ $arg ])) {
$value_name = $optional_args [ $arg ];
if ( isset ( $argv [ $i + 1 ])) {
$options [ $value_name ] = $argv [ $i + 1 ];
$i ++ ;
}
}
}
if ( $input_password ) {
echo " password: " ;
fscanf (STDIN , ' %s ' , $password );
$options [ ' password ' ] = $password ;
echo " " ;
} else {
$options [ ' password ' ] = '' ;
}
if ( $database == null ) {
display_help();
exit ;
}
mysql_connect ( $options [ ' hostname ' ] , $options [ ' username ' ] , $options [ ' password ' ]);
mysql_select_db ( $database );
mysql_query ( " SET NAMES '{$options['charset']}' " );
echo $options [ ' hostname ' ];
echo " " ;
echo $options [ ' username ' ];
echo " " ;
echo $options [ ' password ' ];
echo " " ;
echo $database ;
echo " " ;
// 设置要导出的表
$tables = list_tables( $database );
$filename = sprintf ( $options [ ' filename ' ] , $database );
$fp = fopen ( $filename , ' w ' );
foreach ( $tables as $table ) {
dump_table( $table , $fp );
}
fclose ( $fp );
mysql_close ();
echo " done. " ;
exit ;
function list_tables( $database )
{
$rs = mysql_list_tables ( $database );
$tables = array ();
while ( $row = mysql_fetch_row ( $rs )) {
$tables [] = $row [ 0 ];
}
mysql_free_result ( $rs );
return $tables ;
}
function dump_table( $table , $fp = null )
{
$need_close = false ;
if ( is_null ( $fp )) {
$fp = fopen ( $table . ' .sql ' , ' w ' );
$need_close = true ;
}
fwrite ( $fp , " -- -- {$table} -- " );
$rs = mysql_query ( " SELECT * FROM `{$table}` " );
while ( $row = mysql_fetch_row ( $rs )) {
fwrite ( $fp , get_insert_sql( $table , $row ));
}
mysql_free_result ( $rs );
if ( $need_close ) {
fclose ( $fp );
}
fwrite ( $fp , " " );
}
function get_insert_sql( $table , $row )
{
$sql = " INSERT INTO `{$table}` VALUES ( " ;
$values = array ();
foreach ( $row as $value ) {
$values [] = " ' " . mysql_real_escape_string ( $value ) . " ' " ;
}
$sql .= implode ( ' , ' , $values ) . " ); " ;
return $sql ;
}
function display_help()
{
echo <<< EOT
syntax :
php export_db . php database [ - h hostname] [ - c charset] [ - f filename] [ - u username] [ - p]
defualt hostname : localhost
default charset : utf8
default username : root
default password : (none)
default filename : [database] . sql
EOT;
}
?>
php export_db.php ALTEIL2 -h 192.168.0.77 -c utf8 -f mysql_20070606bak.sql -p