当前位置：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;
  9. 导入
  10. LOAD DATA INFILE 'c:/name.txt' INTO TABLE zones
  11. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
  12. LINES TERMINATED BY '\n';
  13. */
  14. set_time_limit(0);
  15. define(HOST,'127.0.0.1');
  16. define(USER,'root');
  17. define(PWD,'');
  18. define(DBNAME,'test');
  19. define(DIR,'E:/www/mysql_data/');
  20. //0为导入 1为导出 2为修复
  21. define(OPERATION,1);
  23. mysql_connect(HOST,USER,PWD) or
  24. die("Could not connect: " . mysql_error());
  25. mysql_select_db(DBNAME) or
  26. die("Could not select db: " . mysql_error());
  27. $result = mysql_query("show tables");
  29. if(!is_dir(DIR)){
  30. die('Folder does not exist');
  31. }
  32. if(OPERATION==0){
  33. //前提：需要先前导入表结构
  34. //导出命令：mysqldump -u root -p -d test>test.sql
  35. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
  36. $fileName=DIR.$row[0].".txt";
  37. if(file_exists($fileName)){
  38. //delete truncate
  39. $querySql='TRUNCATE TABLE `'.$row[0].'`';
  40. mysql_query($querySql) or
  41. die("Could not truncate table: ". $querySql . mysql_error());
  43. $querySql = "LOAD DATA INFILE '".$fileName."'
  44. INTO TABLE `".$row[0]."`
  45. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
  46. LINES TERMINATED BY '\n'";
  47. mysql_query($querySql) or
  48. die("Could not insert into table: ". $querySql . mysql_error());
  49. echo 'insert into table '.$row[0].' success.
    ';
  50. }else{
  51. echo 'Not insert into table '.$row[0].'
    ';
  52. }
  53. }
  54. echo "The task is finished";
  55. }else if(OPERATION==1){
  56. if (is_writable(DIR)) {
  57. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
  58. $fileName=DIR.$row[0].".txt";
  59. if(file_exists($fileName)){
  60. unlink($fileName);
  61. }
  62. $querySql = "SELECT * INTO OUTFILE '".$fileName."'
  63. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
  64. LINES TERMINATED BY '\n'
  65. FROM `".$row[0]."`";
  66. mysql_query($querySql) or
  67. die("Could not dump table: ". $querySql . mysql_error());
  68. echo 'dump table '.$row[0].' success.
    ';
  69. }
  70. echo "The task is finished";
  71. }else{
  72. echo 'Folder '.DIR.' can not writeable';
  73. }
  74. }else if(OPERATION==2){
  75. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
  76. $fileName=DIR.$row[0].".txt";
  77. if(file_exists($fileName)){
  78. unlink($fileName);
  79. }
  80. $querySql = "REPAIR TABLE `$row[0]`";
  81. mysql_query($querySql) or
  82. die("Could not repair table: ". $querySql . mysql_error());
  83. echo 'repair table '.$row[0].' success.
    ';
  84. }
  85. }
  87. mysql_free_result($result);
  88. mysql_close();
  89. ?>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！