MySQL數據導入和導出純數據的方式有哪些

130次閱讀

共計 7872 個字符，預計需要花費 20 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 MySQL 數據導入和導出純數據的方式有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

MySQL 的數據導入和導出純數據的方式，一般采用的是 load data file 、mysqlimport 、select into outfile、/ 重定向的方式，這里主要介紹 load data file 和 select into outfile 的方式。

一、MySQL 導入和導出數據：
1、load data file
簡介：
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT …INTO OUTFILE.
官方參考手冊：http://dev.mysql.com/doc/refman/5.6/en/load-data.html
語法格式：
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE file_name
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,…)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY string]
[[OPTIONALLY] ENCLOSED BY char ]
[ESCAPED BY char]
]
[LINES
[STARTING BY string]
[TERMINATED BY string]
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,…)]
[SET col_name = expr,…]
load data file 示例:
1)直接導入文件
LOAD DATA INFILE test_data.txt INTO TABLE dbtest.t1;
2) 設置列分隔符和行分隔符
LOAD DATA LOCAL INFILE test_data.txt INTO TABLE t1
FIELDS TERMINATED BY ,
OPTIONALLY ENCLOSED BY
LINES TERMINATED BY \n
3）導入到特定的列
LOAD DATA LOCAL INFILE test_data.txt INTO TABLE t1 (col1,col2,col3);
參數說明：
1）local 參數
如果指定了 LOCAL，被認為與連接的客戶端有關，則文件會被客戶主機上的客戶端讀取，并被發送到服務器。文件會被給予一個完整的路徑名稱，以指定確切的位置。如果給定的是一個相對的路徑名稱，則此名稱會被理解為相對于啟動客戶端時所在的目錄。
如果 LOCAL 沒有被指定，則文件必須位于服務器主機上，并且被服務器直接讀取。
當在服務器主機上為文件定位時，服務器使用以下規則：
如果給定了一個絕對的路徑名稱，則服務器使用此路徑名稱。
如果給定了帶有一個或多個引導組件的相對路徑名稱，則服務器會搜索相對于服務器數據目錄的文件。
如果給定了一個不帶引導組件的文件名稱，則服務器會在默認數據庫的數據庫目錄中尋找文件。
注意，這些規則意味著名為./myfile.txt 的文件會從服務器數據目錄中被讀取，而名為 myfile.txt 的同樣的文件會從默認數據庫的數據庫目錄中讀取。
從客戶端使用絕對路徑 load 數據：LOAD DATA LOCAL INFILE /import/test_data.txt INTO TABLE dbtest.t1;
從服務器里使用相對路徑 load 數據，下面的 LOAD DATA 語句會從 dbtest 數據庫目錄中讀取文件 test_data.txt，因為 db1 是當前數據庫。即使語句明確把文件載入到 db2 數據庫中的表里，也會從 dbtest 目錄中讀取。
USE dbtest;
LOAD DATA INFILE test_data.txt INTO TABLE dbtest.t1;
總而言之：如果指定 local 關鍵詞，則表明從客戶主機讀文件。如果 local 沒指定，文件必須位于服務器上。
2）IGNORE number LINES 參數
IGNORE number LINES 選項可以被用于在文件的開始處忽略行。可以使用 IGNORE 1 LINES 來跳過一個包含列名稱的起始標題行：
LOAD DATA INFILE /tmp/test.txt INTO TABLE test IGNORE 1 LINES;
3）REPLACE、IGNORE 參數
如果您指定了 REPLACE，則輸入行會替換原有行（與原有行一樣，對一個主索引或唯一索引具有相同值的行）。
如果您指定 IGNORE，則把原有行復制到唯一關鍵字值的輸入行被跳過。
如果您這兩個選項都不指定，則運行情況根據 LOCAL 關鍵詞是否被指定而定。不使用 LOCAL 時，當出現重復關鍵字值時，會發生錯誤，并且剩下的文本文件被忽略。使用 LOCAL 時，默認的運行情況和 IGNORE 被指定時的情況相同；這是因為在運行中間，服務器沒有辦法中止文件的傳輸。
4）FIELDS 參數
指定了文件字段的分割格式，列分隔符參數語法為
[{FIELDS | COLUMNS}
[TERMINATED BY string]
[[OPTIONALLY] ENCLOSED BY char ]
[ESCAPED BY char]
]

terminated by 描述字段的分隔符，默認情況下是 tab 字符（\t）
enclosed by 描述的是字段的括起字符，如果您忽略了詞語 OPTIONALLY，則所有的字段都被包含在 ENCLOSED BY 字符串中, 如果您指定了 OPTINALLY，則 ENCLOSED BY 字符只被用于包含具有字符串數據類型（比如 CHAR, BINARY, TEXT 或 ENUM）的列中的值.
escaped by 描述的轉義字符。默認的是反斜杠（backslash：\）
如果您不指定 FIELDS 子句，則默認值為假設您寫下如下語句時的值：
FIELDS TERMINATED BY \t ENCLOSED BY ESCAPED BY \\

當讀取輸入值時，默認值會使 LOAD DATA INFILE 按如下方式運行：
在新行處尋找行的邊界。
不會跳過任何行前綴。
在制表符處把行分解為字段。
不希望字段被包含在任何引號字符之中。
出現制表符、新行、或在 \ 前有 \ 時，理解為作為字段值一部分的文字字符。
備注: 如果您已經在 Windows 系統中生成了文本文件，您可能必須使用 LINES TERMINATED BY \r\n 來正確地讀取文件，因為 Windows 程序通常使用兩個字符作為一個行終止符。部分程序，當編寫文件時，可能會使用 \r 作為行終止符。要讀取這樣的文件，應使用 LINES TERMINATED BY \r。要寫入 FIELDS ESCAPED BY \\，您必須為待讀取的值指定兩個反斜杠，作為一個單反斜杠使用。
6）LINES 參數
指定了每條記錄的分隔符默認為’\n’即為換行分隔符，其語法為：
[LINES
[STARTING BY string]
[TERMINATED BY string]
]
如果您不指定 LINES 子句，則默認值為假設您寫下如下語句時的值：
LINES TERMINATED BY \n STARTING BY
如果所有您希望讀入的行都含有一個您希望忽略的共用前綴，則您可以使用 prefix_string 來跳過前綴（和前綴前的字符）。如果某行不包括前綴，則整個行被跳過。注釋：prefix_string 會出現在一行的中間。

ALTER TABLE…DISABLE KEYS
ALTER TABLE…ENABLE KEYS

2、select into outfile
簡介：
官方參考手冊：http://dev.mysql.com/doc/refman/5.6/en/select-into.html
語法格式：
SELECT … INTO var_list ##selects column values and stores them into variables.
SELECT … INTO OUTFILE ##writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
SELECT … INTO DUMPFILE ##writes a single row to a file without any formatting.

select into outfile 示例：
1)SELECT … INTO OUTFILE file_name
2)SELECT a,b,a+b INTO OUTFILE /tmp/result.txt
FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY
LINES TERMINATED BY \n
FROM test_table;
3)select * from t1 into outfile /tools/databak/dbtest_t1.txt
fields TERMINATED BY ,
lines TERMINATED BY \n
參數說明：（參考 load data 參數）

3、mysqlimport(不推薦使用)
mysqlimport 是操作系統命令，和 load data 實現功能相同，具體使用方法如下：
語法格式：
mysqlimport –defaults-file= –default-character-set=utf8 –columns=id,name –delete –fields-terminated-by= –fields-enclosed-by= –fields-optionally-enclosed-by= –fields-escaped-by= –force -h -i –ignore-lines –lines-terminated-by= -L -p -P -S -u dbname

使用示例：
mysqlimport -L -uroot dbtest /tools/databak/t1.txt –fields-terminated-by= , –lines-terminated-by= \n
參數說明：
mysqlimport –help
–print-defaults Print the program argument list and exit.
–no-defaults Don t read default options from any option file,
except for login file.
–defaults-file=# Only read default options from the given file #.
–defaults-extra-file=# Read this file after the global files are read.
–defaults-group-suffix=#
Also read groups with concat(group, suffix)
–login-path=# Read this path from the login file.
–bind-address=name IP address to bind to.
–character-sets-dir=name
Directory for character set files.
–default-character-set=name
Set the default character set.
-c, –columns=name Use only these columns to import the data to. Give the
column names in a comma separated list. This is same as
giving columns to LOAD DATA INFILE.
-C, –compress Use compression in server/client protocol.
-#, –debug[=name] Output debug log. Often this is d:t:o,filename .
–debug-check Check memory and open file usage at exit.
–debug-info Print some debug info at exit.
–default-auth=name Default authentication client-side plugin to use.
-d, –delete First delete all rows from table.
–enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
–fields-terminated-by=name
Fields in the input file are terminated by the given
string.
–fields-enclosed-by=name
Fields in the import file are enclosed by the given
character.
–fields-optionally-enclosed-by=name
Fields in the input file are optionally enclosed by the
given character.
–fields-escaped-by=name
Fields in the input file are escaped by the given
character.
-f, –force Continue even if we get an SQL error.
-?, –help Displays this help and exits.
-h, –host=name Connect to host.
-i, –ignore If duplicate unique key was found, keep old row.
–ignore-lines=# Ignore first n lines of data infile.
–lines-terminated-by=name
Lines in the input file are terminated by the given
string.
-L, –local Read all files through the client.
-l, –lock-tables Lock all tables for write (this disables threads).
–low-priority Use LOW_PRIORITY when updating the table.
-p, –password[=name]
Password to use when connecting to server. If password is
not given it s asked from the tty.
–plugin-dir=name Directory for client-side plugins.
-P, –port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
–protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-r, –replace If duplicate unique key was found, replace old row.
–secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol.
(Defaults to on; use –skip-secure-auth to disable.)
-s, –silent Be more silent.
-S, –socket=name The socket file to use for connection.
–ssl Enable SSL for connection (automatically enabled with
other flags).
–ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
–ssl).
–ssl-capath=name CA directory (check OpenSSL docs, implies –ssl).
–ssl-cert=name X509 cert in PEM format (implies –ssl).
–ssl-cipher=name SSL cipher to use (implies –ssl).
–ssl-key=name X509 key in PEM format (implies –ssl).
–ssl-crl=name Certificate revocation list (implies –ssl).
–ssl-crlpath=name Certificate revocation list path (implies –ssl).
–ssl-verify-server-cert
Verify server s Common Name in its cert against
hostname used when connecting. This option is disabled by
default.
–ssl-mode=name SSL connection mode.
–use-threads=# Load files in parallel. The argument is the number of
threads to use for loading data.
-u, –user=name User for login if not current user.
-v, –verbose Print info about the various stages.
-V, –version Output version information and exit.