MySQL匯入匯出

標籤：nes   select   cap   字串表   replace   imp   file   更新   lines   

MySQL匯出匯入資料有以下幾種方法：

1）select ... into outfile

2）load data

3）mysqldump

4）mysqlimport

5）mysql 一、表層級匯入、匯出

關於字元處理有以下相關子句：

1）fields terminated by ‘‘：各個欄位之間使用tab分隔。

2）[optionally] enclosed by ‘‘：欄位值使用什麼符號引起來，如果指定了optionally選項，則enclosed by ‘‘指定字串類型欄位有效。

3）escaped by ‘‘：定義逸出字元，預設為“\”。

4）lines terminated by ‘‘：定義分行符號，linux下預設為\n。

文字檔格式：

資料：可以使用科學計數法

字串：字串裡的特殊字元必須使用反斜線字元作為識別標識，以區別各種分隔字元

日期：安照2017-05-30格式字串表示

時間：按照15:12:00格式字串表示

時間戳記：20170520151200格式整數對待

null值：假設“\”作為逸出字元，“‘”作為字串前尾碼標識，那麼在匯出操作中\N表示null值。

1、使用select ... into outfile匯出表資料

使用select ... into outfile可以進行表層級的匯出操作，並且輸出檔案不能先於輸出存在。

例如：

mysql> select * into outfile ‘/datas/column_charset_00.sql‘ from column_charset;

mysql> select * into outfile ‘/datas/column_charset_01.sql‘ fields terminated by ‘,‘ from column_charset;

mysql> select * into outfile ‘/datas/column_charset_03.sql‘ fields terminated by ‘,‘ lines terminated by ‘\n‘ from column_charset;

mysql> select * into outfile ‘/datas/column_charset_04.sql‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ from column_charset;

2、使用load data匯入資料到表中

select ... into outfile命令可以匯出表資料到檔案中，load data命令巧好於select ... into outfile命令相反，可以匯入檔案中資料到某張表中。

參數local作用：如果匯出檔案在伺服器上，使用命令load data infile ... 匯入資料；否則可以使用load data local infile ... 命令匯入用戶端本地檔案資料到表中。

使用load data命令匯入資料時需要注意mysql伺服器字元集的設定。如果load data infile在某些mysql版本中不支援字元集，這時，mysql將假定匯入字元集為character_set_database指定字元集，如果沒有設定character_set_database參數，則預設採用character_set_server指定字元集，可以使用set character_set_database或set names命令更改伺服器字元集設定，也可以在load data命令中指定字元集。

例如：

mysql> load data infile ‘/datas/column_charset_00.sql‘ into table column_charset;

--匯入資料採用character_set_database預設指定字元集。

mysql> load data infile ‘/datas/column_charset_00.sql‘ into table column_charset character set utf8;

--命令中設定字元集匯入tab分隔檔案。

mysql> load data infile ‘/datas/column_charset_01.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘;

--匯入‘，’分隔檔案到表中。

mysql> load data infile ‘/datas/column_charset_03.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ lines terminated by ‘\t‘;

--匯入‘，’分隔列，tab分隔行的檔案。

mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ enclosed by ‘"‘ lines terminated by ‘\n‘;

或者

mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘;

--匯入‘，’分隔列，“\n”分隔行的檔案，‘"‘號引用欄位的檔案。

mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4);

--指定匯入表的欄位順序。

mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4) set c1=‘dbking‘;

--load data同時指定更新列。

mysql> load data local infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4) set c1=‘dbking‘;

mysql> load data local infile ‘/datas/column_charset_04.sql‘ replace into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4) set c1=‘chavin‘;

--匯入本地檔案到表中。

local data最佳化：

相對於普通的mysql命令，load data命令匯入速度要快得多，一般可以達到幾萬條記錄每秒，如果想要擁有更高的速度，可以進行以下最佳化操作。這裡針對於innodb和myisam儲存引擎分別介紹：

針對於innodb模式，建議最佳化方式有：

1）將innodb_buffer_pool_size值設定的大一些。

2）將innodb_log_file_size設定的大一些。

3）設定忽略二級索引的唯一性限制式，set unique_checks=0.

4）設定忽略外檢約束，set foreign_key_checks=0.

5）設定不記錄二進位檔案，set sql_log_bin=0.

6）按主鍵順序匯入資料。

7）對於innodb引擎表，可以在匯入前設定autocommit=0。

8）將大的檔案切割成多個小的檔案匯入，例如split。

針對於myisam模式，建議最佳化方式有：

1）將bulk_insert_tree_size、myisam_sort_buffer_size、key_buffer_size設定的大一些。

2）先禁用key（alter table ... disable keys），然後再匯入資料，然後再啟用key（alter table ... enable keys）。重新啟用key後，會重新大量建立索引，大量建立索引比一條一條建立索引效率高的多。alter table ... disable keys命令只禁用非唯一性索引，唯一索引和主鍵是不能禁用的，除非你手工移除它。

3）使用load data，tab分隔的檔案更容易解析，比其他方式快。 二、使用mysqldump匯出資料

mysqldump匯出的一般是SQL檔案，也成為轉儲檔案或dump檔案，我們可以使用mysql工具或mysqlimport工具匯入mysqldump匯出檔案。

例如：

匯出chavin資料庫：

mysqldump -uroot -pmysql chavin > chavin00.sql

mysqldump -uroot -pmysql --complete-insert --force --insert-ignore --add-drop-database --hex-blob --database chavin > chavin02.sql

匯出chavin庫中的某些表：

mysqldump -uroot -pmysql chavin --tables column_charset column_collate > chavin01.sql

匯出chavin庫，採用sql與資料分離模式：

mysqldump -uroot -pmysql --tab=/datas/chavin00 chavin

匯出chavin庫，採用sql與資料分離，資料欄位使用“，”分隔：

mysqldump -uroot -pmysql --tab=/datas/chavin01 --fields-terminated-by=‘,‘ --fields-enclosed-by=‘"‘ chavin

匯出所有資料庫：

mysqldump -uroot -pmysql --all-database --add-drop-database >db00.sql

匯出xml格式資料：

mysqldump -uroot -pmysql --xml chavin >chavin.03.xml

匯出資料庫並增加壓縮功能：

mysqldump -uroot -pmysql --hex-blob chavin|gzip >chavin04.sql.gz

匯出全庫：

mysqldump -uroot -pmysql --flush-logs --master-data=2 --hex-blob -R -E -f --all-databases 2>> /datas/full-log |gzip > mysql-full.gz

僅匯出資料結構：

mysqldump -uroot -pmysql -d --add-drop-table chavin > chavin11.sql

mysqldump -uroot -pmysql --no-data --add-drop-table chavin > chavin12.sql 三、匯入由mysqldump匯出的資料

1、使用mysql命令列工具可以匯入由mysqldump匯出的檔案。

例如：

匯入檔案chavin.sql：

mysql -uroot -pmysql restore01 < chavin00.sql

匯入壓縮過的匯出檔案：

gzip -dc chavin04.sql.gz | mysql -uroot -pmysql chavin08

匯入檔案並且確保用戶端、串連、檔案字元集一致性：

mysql -uroot -pmysql --default-character-set=utf8 restore02 < chavin00.sql

2、mysqlimport工具可以用來匯入資料。

3、使用source命令恢複資料

mysql> source /datas/chavin10.sql 四、使用mysql工具批處理功能匯出資料

1、匯出column_charset表：

mysql -uroot -poracle -h192.168.108.128 -P3306 --batch --default-character-set=utf8 -e "select * from chavin.column_charset;" > output.txt

mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --batch "--execute=select * from column_charset;" chavin > output03.txt

mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --batch -e "select * from column_charset;" chavin > output04.txt

2、查詢結果縱向顯示

mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --vertical "--execute=select * from chavin.column_charset;" > output00.txt

3、產生html格式輸出

mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --html "--execute=select * from chavin.column_charset;" > output01.html

4、產生xml格式的輸出

mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --xml "--execute=select * from chavin.column_charset;" > output02.xml 五、作業系統split工具切割資料檔案

split命令作用是切割檔案。-l參數指定按多少行進行切割，不指定預設為每1000行切割一份。

# split -l 32 output03.txt split/output_split_sub_

# ll split/

total 20

-rw-r--r-- 1 root root 880 Jan 22 05:39 output_split_sub_aa

-rw-r--r-- 1 root root 896 Jan 22 05:39 output_split_sub_ab

-rw-r--r-- 1 root root 896 Jan 22 05:39 output_split_sub_ac

-rw-r--r-- 1 root root 896 Jan 22 05:39 output_split_sub_ad

-rw-r--r-- 1 root root 28 Jan 22 05:39 output_split_sub_ae

將大檔案切割成小檔案後，通過多個用戶端並行匯入，會提高效率。

MySQL匯入匯出