MySQL匯入匯出檔案檔案

匯出文字：
select ...into outfile 匯出檔案（注：匯出的檔案存放在mysql的伺服器上）
   子句：FIELDS TERMINATED BY '*'   指定欄位分隔符
   子句：OPTIONALLY ENCLOSED BY '*' 指定欄位資料包圍符
   子句：LINES TERMINATED BY '*'    指定行行結束符
   如：
   select * from ndb_t1 into outfile '/opt/tmpdata/ndb_t1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

   參考：http://dev.mysql.com/doc/refman/5.6/en/select.html

匯入文字：
load data命令
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，將從客戶端主機上讀取檔案，併傳送到伺服器作業系統的臨時目錄下（Windows：c:\windows\temp;Linux:/tmp ）。這兒要注意mysql對目錄的許可權
    沒有指定LOCAL，匯入的檔案必須在mysql伺服器上。

    例子：
  Converting strings into dates while loading data using LOAD DATA INFILE:

  In the following example, we are trying to convert the data in the file for date columns col3, col4 in formats 'mm/dd/yyyy', 'dd/mm/yyyy' into MySQL standard YYYY-mm-dd respectively.

  load data infile '/tmp/xxx.dat'
  into table xxx
  fields terminated by '|'
  lines terminated by '\n'
  (col1,
  col2,
  @col3,
  @col4,
  col5)
  set
  col3 = str_to_date(@col3, '%m/%d/%Y'),
  col4 = str_to_date(@col4, '%d/%m/%Y')
  ;

    參考：http://dev.mysql.com/doc/refman/5.6/en/load-data.html

Character	Escape Sequence
\0	An ASCII NUL (0x00) character
\b	A backspace character
\n	A newline (linefeed) character
\r	A carriage return character
\t	A tab character.
\Z	ASCII 26 (Control+Z)
\N	NULL

如果希望load data 能夠匯入null而非空串''，在文字檔案中欄位相應位置使用\N代替null。
null與''的區別，見理解：MySQL的null與空字串的不同

附：匯出資料到客戶端的方法：
1、使用mysql的tee（\T）命令，也就是把MYSQL的所有輸出都輸入到指定檔案。
例：
mysql>tee /*/a.txt
mysql>select * from t1;

2、使用mysql -e "select * from t1" > t1.txt
詳見：http://blog.chinaunix.net/uid-167175-id-3621600.html

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

MySQL匯入匯出檔案檔案

相關文章