MySQL表數據的導入與導出方法

本篇內容介紹了“表數據的導入與導出方法”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

LOAD DATA INFILE

LOAD DATA INFILE語(yǔ)句可以快速將文本記錄導入到表中， SELECT ... INTO OUTFILE可以將表中數據導入到文件中，兩者的FIELDS、LINES子句語(yǔ)法相同。
執行語(yǔ)句的用戶(hù)需要有FILE權限，且mysql用戶(hù)對數據文件可讀。
mysqlimport也可以實(shí)現將文本文件導入到表中，mysqlimport也是向調用LOAD DATA INFILE語(yǔ)句，--local選項可以讀取客戶(hù)端的文件。

LOAD DATA INFILE語(yǔ)法如下：

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時(shí)，客戶(hù)端將讀取文件發(fā)送到服務(wù)器操作系統的臨時(shí)目錄(非MySQL臨時(shí)目錄)，如空間不如將導致執行失敗。如未指定local，則會(huì )從服務(wù)器讀取，相對路徑是相對于data_dir的路徑。

  LOAD DATA INFILE:當數據非法或數據唯一索引沖突將導致執行失敗。
  LOAD DATA LOCAL INFILE：因為服務(wù)器端無(wú)法終止客戶(hù)端文件發(fā)送，當數據非法或數據唯一索引沖突時(shí)將會(huì )警告忽略錯誤繼續，類(lèi)似IGNORE。

• REPLACE
  出現數據唯一索引沖突將會(huì )替代現有的記錄

• IGNORE
  出現數據唯一索引沖突將會(huì )被忽略。如果IGNORE與REPLACE都沒(méi)指定，則取決于LOCAL選項。如果local也沒(méi)有指定，則重復數據之后的文件數據將被不會(huì )執行，如指定local，則會(huì )忽略沖突繼續執行。

• IGNORE number LINES
  指定跳過(guò)文件開(kāi)頭的幾行記錄

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會(huì )將查詢(xún)結果寫(xiě)入到服務(wù)器，因此需要有FILE權限，同時(shí)需要對指定的路徑有寫(xiě)入權限，且指定文件不能是服務(wù)器已有文件。
客戶(hù)端生成指定結果文件可使用mysql -e "SELECT ..." > file_name

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

OPTION選項含義如下：

• FIELDS TERMINATED BY 'value'
  設置字段之間的分隔字符，可以為單個(gè)或多個(gè)字符，默認為制表符'\t'

• FIELDS [OPTIONALLY] ENCLOSED BY 'value'
  設置字段的包圍字符，只能為單個(gè)字符

• FIELDS ESCAPED BY 'value'
  設置轉義字符，只能為單個(gè)字符，默認值為‘\’

• LINES STARTING BY 'value'
  設置每行數據開(kāi)頭的字符，可以為單個(gè)或多個(gè)字符，默認情況下不使用任何字符

• LINES TERMINATED BY 'value'
  設置每行數據結尾的字符，可以為單個(gè)或多個(gè)字符，默認值為'\n',WINDOWS回車(chē)換行符為'\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;

mysqldump -T path dbname tbl_name 也可以生成文本文件，在path目錄生成一個(gè)tbl_name.sql表定義文件和tbl_name.txt文件表數據文件，可以指定fields與lines選項指定生成數據文件的格式。