\n

通常我们都用 phpMyAdmin 来导出，不过如果你的数据库如果存在下列问题，那么 phpMyAdmin 也无能为力。


\n

    \n
  • 数据库的字符集与应用程序的字符集不一致；
    \n
  • 应用程序用错误的编码将数据保存到了数据库中；
    \n
  • 用 phpMyAdmin 和 mysqldump 导出的数据总是乱码。

\n

总之你用 phpMyAdmin 和 mysqldump 导出的数据有乱码时，就试试看这个脚本吧。


\n

使用很简单：

php export_db.php 数据库名 [-h 主机名] [-c 字符集] [-f 输出文件名] [-u 用户名] [-p]  
数据库名是必须提供的，其他参数如果没有提供则使用下面的默认值：     
默认主机名 : localhost     
默认字符集 : utf8     
默认用户名 : root     
默认密码 : (无)     
默认输出文件 : 数据库名.sql

\n

这个脚本的导出结果就是一个 .sql 文件，只有 insert 语句。
所以数据结构需要单独导出，不过这个就不存在字符集问题了。


\n


\n

源代码：


\n

\n

    \n
  1. <?php
    \n

  2. \n
  3. if (!function_exists(‘mysql_connect’)) {
    \n
  4. if (DIRECTORY_SEPARATOR == ‘/’) {
    \n
  5. dl(‘php_mysql.so’);
    \n
  6. } else {
    \n
  7. dl(‘php_mysql.dll’);
    \n
  8. }
    \n
  9. }
    \n

  10. \n
  11. database = null;
    \n
  12. if (isset(argv[1])) {
    \n
  13. database = argv[1];
    \n
  14. } else {
    \n
  15. display_help();
    \n
  16. exit;
    \n
  17. }
    \n

  18. \n
  19. optional_args = array(
    \n
  20. ‘-h’ => ‘hostname’,
    \n
  21. ‘-c’ => ‘charset’,
    \n
  22. ‘-f’ => ‘filename’,
    \n
  23. ‘-u’ => ‘username’
    \n
  24. );
    \n

  25. \n
  26. options = array(
    \n
  27. ‘hostname’ => ‘localhost’,
    \n
  28. ‘charset’ => ‘utf8′,
    \n
  29. ‘filename’ => ‘%s.sql’,
    \n
  30. ‘username’ => ‘root’,
    \n
  31. );
    \n

  32. \n
  33. input_password = false;
    \n
  34. for (i = 2; i < argc; i++) {
    \n
  35. arg = argv[i];
    \n
  36. if (arg == ‘-p’) {
    \n
  37. input_password = true;
    \n
  38. continue;
    \n
  39. }
    \n
  40. if (isset(optional_args[arg])) {
    \n
  41. value_name = optional_args[arg];
    \n
  42. if (isset(argv[i + 1])) {
    \n
  43. options[value_name] = argv[i + 1];
    \n
  44. i++;
    \n
  45. }
    \n
  46. }
    \n
  47. }
    \n

  48. \n
  49. if (input_password) {
    \n
  50. echo “password: “;
    \n
  51. fscanf(STDIN, ‘%s’, password);
    \n
  52. options[\'password\'] = password;
    \n
  53. echo “\\n”;
    \n
  54. } else {
    \n
  55. options[\'password\'] = ”;
    \n
  56. }
    \n

  57. \n
  58. if (database == null) {
    \n
  59. display_help();
    \n
  60. exit;
    \n
  61. }
    \n

  62. \n
  63. mysql_connect(options[\'hostname\'], options[\'username\'], options[\'password\']);
    \n
  64. mysql_select_db(database);
    \n
  65. mysql_query(“SET NAMES ‘{options[\'charset\']}’”);
    \n

  66. \n
  67. // 设置要导出的表
    \n
  68. tables = list_tables(database);
    \n

  69. \n
  70. filename = sprintf(options[\'filename\'], database);
    \n
  71. fp = fopen(filename, ‘w’);
    \n
  72. foreach (tables as table) {
    \n
  73. dump_table(table, fp);
    \n
  74. }
    \n
  75. fclose(fp);
    \n
  76. mysql_close();
    \n
  77. echo “done.\\n”;
    \n
  78. exit;
    \n

  79. \n

  80. \n

  81. \n
  82. function list_tables(database)
    \n
  83. {
    \n
  84. rs = mysql_list_tables(database);
    \n
  85. tables = array();
    \n
  86. while (row = mysql_fetch_row(rs)) {
    \n
  87. tables[] = row[0];
    \n
  88. }
    \n
  89. mysql_free_result(rs);
    \n
  90. return tables;
    \n
  91. }
    \n

  92. \n
  93. function dump_table(table, fp = null)
    \n
  94. {
    \n
  95. need_close = false;
    \n
  96. if (is_null(fp)) {
    \n
  97. fp = fopen(table . ‘.sql’, ‘w’);
    \n
  98. need_close = true;
    \n
  99. }
    \n
  100. fwrite(fp, “– \\n– {table}\\n– \\n”);
    \n
  101. rs = mysql_query(“SELECT * FROM {table}”);
    \n
  102. while (row = mysql_fetch_row(rs)) {
    \n
  103. fwrite(fp, get_insert_sql(table, row));
    \n
  104. }
    \n
  105. mysql_free_result(rs);
    \n
  106. if (need_close) {
    \n
  107. fclose(fp);
    \n
  108. }
    \n
  109. fwrite(fp, “\\n\\n”);
    \n
  110. }
    \n

  111. \n
  112. function get_insert_sql(table, row)
    \n
  113. {
    \n
  114. sql = “INSERT INTO {table} VALUES (“;
    \n
  115. values = array();
    \n
  116. foreach (row as value) {
    \n
  117. values[] = “‘” . mysql_real_escape_string(value) . “‘”;
    \n
  118. }
    \n
  119. sql .= implode(‘, ‘, values) . “);\\n”;
    \n
  120. return sql;
    \n
  121. }
    \n

  122. \n
  123. function display_help()
    \n
  124. {
    \n
  125. echo <<<EOT

  126. \n
  127. syntax:
    \n
  128. php export_db.php database [-h hostname] [-c charset] [-f filename] [-u username] [-p]
    \n

  129. \n
  130. defualt hostname : localhost
    \n
  131. default charset : utf8
    \n
  132. default username : root
    \n
  133. default password : (none)
    \n
  134. default filename : [database].sql
    \n

  135. \n
  136. EOT;
    \n
  137. }
    \n

  138. \n
  139. ?>

\n