mysql表的匯入匯出

標籤：mysql表匯入匯出

⒈select ... into outfile匯出資料/load data infile匯入資料
⑴建立新表，定義表結構
  ⅰ方法一：在同一資料庫中
      ①複製表結構及資料到新表
      CREATE TABLE newtable  SELECT * FROM oldtable
      ② 只複製表結構到新表
       CREATE TABLE newtable  SELECT * FROM oldtable WHERE 1=2
  ⅱ方法二：適應庫與庫之間，或者單庫
      ①只複製表結構到新表
     匯出：
     mysqldump -uroot -pdbpasswd -d dbname [tablename]>createtable.sql;
     匯入：
      mysql>source /path/createtable.sql  or  mysql -u root -p <createtable.sql;
    mysql> source /usr/yzx_loadtest/createbus_dt.sql;
   Query OK, 0 rows affected (0.01 sec)
⑵匯出/匯入表資料
匯出數
select * from mytbl into outfile ‘/tmp/mytbl2.txt‘ fields terminated by ‘,‘ enclosed by ‘"‘ lines terminated by ‘\n‘;
select * from bus_dt into outfile ‘/usr/yzx_loadtest/bus_dtbackdata.txt‘ fields terminated by ‘,‘ enclosed by ‘"‘ lines terminated by ‘\n‘;
匯入資料
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name.txt‘  [REPLACE | IGNORE]  INTO TABLE tbl_name  [FIELDS  [TERMINATED BY ‘string‘]  [[OPTIONALLY] ENCLOSED BY ‘char‘] [ESCAPED BY ‘char‘ ] ]     [LINES  [STARTING BY ‘string‘]  [TERMINATED BY ‘string‘] ]
load data infile ‘/usr/yzx_loadtest/bus_dtbackdata.txt‘ into table bus_dt fields terminated by ‘,‘ enclosed by ‘"‘ lines terminated by ‘\n‘;
關於NULL值
null值被處理成\N

⑶大資料表如何節省匯入/匯出的時間

ⅰ對於Myisam類型的表，可以通過以下方式快速的匯入大量的資料。 ALTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS; 這兩個命令用來開啟    或者關閉Myisam表非唯一索引的更新。在匯入大量的資料到一個非空的Myisam表時，通過設定這兩個命令，可以提高匯入的效率。 對於匯入大量資料到一個空的Myisam表，預設就是先    匯入資料然後才建立索引的，所以不用進行設定。

ⅱ對於Innodb類型的表，有以下幾種方式可以提高匯入的效率：
①因為Innodb類型的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順序排列，可以有效提高匯入資料的效率。如果Innodb表沒有主鍵，那麼系統會預設建立一個內部列作    為主鍵，所以如果可以給表建立一個主鍵，將可以利用這個優勢提高匯入資料的效率。
②在匯入資料前執行SET UNIQUE_CHECKS=0，關閉唯一性校正，在匯入結束後執行SET UNIQUE_CHECKS=1，恢複唯一性校正，可以提高匯入的效率。
③如果應用使用自動認可的方式，建議在匯入前執行SET AUTOCOMMIT=0，關閉自動認可，匯入結束後再執行SET AUTOCOMMIT=1，開啟自動認可，也可以提高匯入的效率。

ⅲ對於表級上千萬或者上億，需要分檔案進行匯入;
為什嗎？load data file需要寫二進位日誌，另外如果是主從複製，還會有延遲，如果失敗，回退仍需要時間。
查看插入資料的行數:
[[email protected] yzx_loadtest]# wc -l bus_dtbackdata.txt
1021 bus_dtbackdata.txt
利用split進行分割：
[[email protected] yzx_loadtest]# split -l 500 bus_dtbackdata.txt bus_dtbackdata.txt001
[[email protected] yzx_loadtest]# ls -l
total 48
-rw-rw-rw- 1 mysql mysql 17247 Apr  9 11:45 bus_dtbackdata.txt
-rw-r--r-- 1 root  root   7907 Apr  9 13:40 bus_dtbackdata.txt001aa
-rw-r--r-- 1 root  root   8939 Apr  9 13:40 bus_dtbackdata.txt001ab
-rw-r--r-- 1 root  root    401 Apr  9 13:40 bus_dtbackdata.txt001ac
-rw-r--r-- 1 root  root   1640 Apr  9 11:50 createbus_dt.sql
[[email protected] yzx_loadtest]#

分割之後使用：
load data infile ‘/usr/yzx_loadtest/bus_dtbackdata.txt001aa‘ into table bus_dt fields terminated by ‘,‘ enclosed by ‘"‘ lines terminated by ‘\n‘;
load data infile ‘/usr/yzx_loadtest/bus_dtbackdata.txt001ab‘ into table bus_dt fields terminated by ‘,‘ enclosed by ‘"‘ lines terminated by ‘\n‘;

注意事項：必須保證匯入的庫與匯出的庫的字元集相同
 show variables like ‘char%‘
 set ...=gbk;

本文出自 “風聲水起” 部落格，請務必保留此出處http://linuxybird.blog.51cto.com/5689151/1638053

mysql表的匯入匯出