Heim  >  Artikel  >  Backend-Entwicklung  >  mysql以文件形式导入导出整个数据库

mysql以文件形式导入导出整个数据库

WBOY
WBOYOriginal
2016-07-25 09:09:52885Durchsuche
mysql以文件形式导入导出整个数据库
  1. //使用时一定要注意是否是处于导入状态,如果是导入操作,一定要先备份数据库,否则数据会被清空。。
  2. /*
  3. 导出
  4. SELECT * INTO OUTFILE 'c:/name.txt'
  5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
  6. LINES TERMINATED BY '\n'
  7. FROM zones;
  8. 导入
  9. LOAD DATA INFILE 'c:/name.txt' INTO TABLE zones
  10. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
  11. LINES TERMINATED BY '\n';
  12. */
  13. set_time_limit(0);
  14. define(HOST,'127.0.0.1');
  15. define(USER,'root');
  16. define(PWD,'');
  17. define(DBNAME,'test');
  18. define(DIR,'E:/www/mysql_data/');
  19. //0为导入 1为导出 2为修复
  20. define(OPERATION,1);
  21. mysql_connect(HOST,USER,PWD) or
  22. die("Could not connect: " . mysql_error());
  23. mysql_select_db(DBNAME) or
  24. die("Could not select db: " . mysql_error());
  25. $result = mysql_query("show tables");
  26. if(!is_dir(DIR)){
  27. die('Folder does not exist');
  28. }
  29. if(OPERATION==0){
  30. //前提:需要先前导入表结构
  31. //导出命令:mysqldump -u root -p -d test>test.sql
  32. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
  33. $fileName=DIR.$row[0].".txt";
  34. if(file_exists($fileName)){
  35. //delete truncate
  36. $querySql='TRUNCATE TABLE `'.$row[0].'`';
  37. mysql_query($querySql) or
  38. die("Could not truncate table: ". $querySql . mysql_error());
  39. $querySql = "LOAD DATA INFILE '".$fileName."'
  40. INTO TABLE `".$row[0]."`
  41. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
  42. LINES TERMINATED BY '\n'";
  43. mysql_query($querySql) or
  44. die("Could not insert into table: ". $querySql . mysql_error());
  45. echo 'insert into table '.$row[0].' success.
    ';
  46. }else{
  47. echo 'Not insert into table '.$row[0].'
    ';
  48. }
  49. }
  50. echo "The task is finished";
  51. }else if(OPERATION==1){
  52. if (is_writable(DIR)) {
  53. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
  54. $fileName=DIR.$row[0].".txt";
  55. if(file_exists($fileName)){
  56. unlink($fileName);
  57. }
  58. $querySql = "SELECT * INTO OUTFILE '".$fileName."'
  59. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
  60. LINES TERMINATED BY '\n'
  61. FROM `".$row[0]."`";
  62. mysql_query($querySql) or
  63. die("Could not dump table: ". $querySql . mysql_error());
  64. echo 'dump table '.$row[0].' success.
    ';
  65. }
  66. echo "The task is finished";
  67. }else{
  68. echo 'Folder '.DIR.' can not writeable';
  69. }
  70. }else if(OPERATION==2){
  71. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
  72. $fileName=DIR.$row[0].".txt";
  73. if(file_exists($fileName)){
  74. unlink($fileName);
  75. }
  76. $querySql = "REPAIR TABLE `$row[0]`";
  77. mysql_query($querySql) or
  78. die("Could not repair table: ". $querySql . mysql_error());
  79. echo 'repair table '.$row[0].' success.
    ';
  80. }
  81. }
  82. mysql_free_result($result);
  83. mysql_close();
  84. ?>
复制代码


Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn