mysql以文件形式导入导出整个数据库
- //使用时一定要注意是否是处于导入状态,如果是导入操作,一定要先备份数据库,否则数据会被清空。。
- /*
- 导出
- SELECT * INTO OUTFILE 'c:/name.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n'
- FROM zones;
- 导入
- LOAD DATA INFILE 'c:/name.txt' INTO TABLE zones
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n';
- */
- set_time_limit(0);
- define(HOST,'127.0.0.1');
- define(USER,'root');
- define(PWD,'');
- define(DBNAME,'test');
- define(DIR,'E:/www/mysql_data/');
- //0为导入 1为导出 2为修复
- define(OPERATION,1);
- mysql_connect(HOST,USER,PWD) or
- die("Could not connect: " . mysql_error());
- mysql_select_db(DBNAME) or
- die("Could not select db: " . mysql_error());
- $result = mysql_query("show tables");
- if(!is_dir(DIR)){
- die('Folder does not exist');
- }
- if(OPERATION==0){
- //前提:需要先前导入表结构
- //导出命令:mysqldump -u root -p -d test>test.sql
- while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
- $fileName=DIR.$row[0].".txt";
- if(file_exists($fileName)){
- //delete truncate
- $querySql='TRUNCATE TABLE `'.$row[0].'`';
- mysql_query($querySql) or
- die("Could not truncate table: ". $querySql . mysql_error());
-
- $querySql = "LOAD DATA INFILE '".$fileName."'
- INTO TABLE `".$row[0]."`
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n'";
- mysql_query($querySql) or
- die("Could not insert into table: ". $querySql . mysql_error());
- echo 'insert into table '.$row[0].' success.
';
- }else{
- echo 'Not insert into table '.$row[0].'
';
- }
- }
- echo "The task is finished";
- }else if(OPERATION==1){
- if (is_writable(DIR)) {
- while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
- $fileName=DIR.$row[0].".txt";
- if(file_exists($fileName)){
- unlink($fileName);
- }
- $querySql = "SELECT * INTO OUTFILE '".$fileName."'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n'
- FROM `".$row[0]."`";
- mysql_query($querySql) or
- die("Could not dump table: ". $querySql . mysql_error());
- echo 'dump table '.$row[0].' success.
';
- }
- echo "The task is finished";
- }else{
- echo 'Folder '.DIR.' can not writeable';
- }
- }else if(OPERATION==2){
- while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
- $fileName=DIR.$row[0].".txt";
- if(file_exists($fileName)){
- unlink($fileName);
- }
- $querySql = "REPAIR TABLE `$row[0]`";
- mysql_query($querySql) or
- die("Could not repair table: ". $querySql . mysql_error());
- echo 'repair table '.$row[0].' success.
';
- }
- }
-
- mysql_free_result($result);
- mysql_close();
- ?>
复制代码
|
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
作者最新文章
-
2024-10-22 09:46:29
-
2024-10-13 13:53:41
-
2024-10-12 12:15:51
-
2024-10-11 22:47:31
-
2024-10-11 19:36:51
-
2024-10-11 15:50:41
-
2024-10-11 15:07:41
-
2024-10-11 14:21:21
-
2024-10-11 12:59:11
-
2024-10-11 12:17:31