当前位置:Gxlcms > PHP教程 > mysql以文件形式导入导出整个数据库

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

时间:2021-07-01 10:21:17 帮助过:14人阅读

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. ?>

人气教程排行