MySQL表資料匯入與匯出

LOAD DATA INFILE

LOAD DATA INFILE語句可以快速將文字記錄匯入到表中， SELECT ... INTO OUTFILE可以將表中資料匯入到檔案中，兩者的FIELDS、LINES子句語法相同。
執行語句的使用者需要有FILE許可權，且mysql使用者對資料檔案可讀。
mysqlimport也可以實現將文字檔案匯入到表中，mysqlimport也是向伺服器呼叫LOAD DATA INFILE語句，--local選項可以讀取客戶端的檔案。

LOAD DATA INFILE語法如下：

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,...]

• LOCAL
  當指定local時，客戶端將讀取檔案傳送到伺服器作業系統的臨時目錄(非MySQL臨時目錄)，如空間不如將導致執行失敗。如未指定local，則會從伺服器讀取，相對路徑是相對於data_dir的路徑。

  LOAD DATA INFILE:當資料非法或資料唯一索引衝突將導致執行失敗。
  LOAD DATA LOCAL INFILE：因為伺服器端無法終止客戶端檔案傳送，當資料非法或資料唯一索引衝突時將會警告忽略錯誤繼續，類似IGNORE。

• REPLACE
  出現資料唯一索引衝突將會替代現有的記錄

• IGNORE
  出現資料唯一索引衝突將會被忽略。如果IGNORE與REPLACE都沒指定，則取決於LOCAL選項。如果local也沒有指定，則重複資料之後的檔案資料將被不會執行，如指定local，則會忽略衝突繼續執行。

• IGNORE number LINES
  指定跳過檔案開頭的幾行記錄

LOAD DATA INFILE用法：

預設load data infile的格式(不指定FIELDS，LINES選項)
FIELDS TERMINATED BY '\t'
       ENCLOSED BY ''
       ESCAPED BY '\\'
LINES TERMINATED BY '\n'
STARTING BY ''

匯入csv檔案
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

匯入包含指定列的檔案
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ','
  (column2,column3);

匯入檔案設定指定列的值
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ','
  (column1,column2)
  set column3 = CURRENT_TIMESTAMP;;

SELECT ... INTO OUTFILE

SELECT ... INTO OUTFILE會將查詢結果寫入到伺服器，因此需要有FILE許可權，同時需要對指定的路徑有寫入許可權，且指定檔案不能是伺服器已有檔案。
客戶端生成指定結果檔案可使用mysql -e "SELECT ..." > file_name

SELECT ... INTO OUTFILE與LOAD DATA INFILE的FIELDS、LINES子句語法相同。

OPTION選項含義如下：

• FIELDS TERMINATED BY 'value'
  設定欄位之間的分隔字元，可以為單個或多個字元，預設為製表符'\t'

• FIELDS [OPTIONALLY] ENCLOSED BY 'value'
  設定欄位的包圍字元，只能為單個字元

• FIELDS ESCAPED BY 'value'
  設定轉義字元，只能為單個字元，預設值為‘\’

• LINES STARTING BY 'value'
  設定每行資料開頭的字元，可以為單個或多個字元，預設情況下不使用任何字元

• LINES TERMINATED BY 'value'
  設定每行資料結尾的字元，可以為單個或多個字元，預設值為'\n',WINDOWS回車換行符為'\r\n'

匯出csv格式的檔案
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29733787/viewspace-2015673/，如需轉載，請註明出處，否則將追究法律責任。