【匯出】：

基本語法：
SELECT ... FROM table_name INTO OUTFILE '/tmp/test.txt';
或者
SELECT ... INTO OUTFILE '/tmp/test.txt' FROM table_name;

mysql> SELECT field_name FROM Country INTO OUTFILE '/tmp/test.txt';
Query OK, 239 rows affected (0.00 sec)

mysql> SELECT field_name INTO OUTFILE '/tmp/Country2.txt' FROM Country;
Query OK, 239 rows affected (0.00 sec)

匯出文件如下：
可以看出，每條記錄以回車換行為界，每個欄位以tab為界，這是outfile的預設分界符。

如果想自定義欄位的分隔符、每條記錄的分隔符、還有每條欄位的包裹符，可以透過加引數來實現。
以第一種匯入方式為例（SELECT ... FROM table_name INTO OUTFILE '/tmp/test.txt')：

mysql> SELECT * FROM Country INTO OUTFILE '/tmp/test.txt'
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';

FIELDS TERMINATED BY 用於設定欄位的分隔符
OPTIONAALY ENCLOSED BY 用於設定欄位由什麼符號包裹
LINES TERMINATED BY 用於設定每條記錄分隔符，比如\n意為換行，\r為回車

【匯入】：
基本語法：
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE table_name ;

完整語法：

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

根據不同的file分隔符、換行符等不同，可以加上不同的引數：
比如

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE table_name
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
LINES
TERMINATED BY '\r';

如果要忽略，可以IGNORE n LINES，代表忽略前n行。
上述詳細語法已經給出。（詳細語法摘自MySQL 5.6 Reference Manual）

---------------------------- 更新
mysqldump也也有等同into outfile的引數：
比如

mysqldump -u -p --no-create-info --tab=. --fields-terminated-by=, --fields-optionally-enclosed-by=\" $db_name $tb_name

預設會在當前目錄下生成一個 $tb_name.txt 的檔案
等同於

SELECT * FROM $db_name.$tb_name
INTO OUTFILE '/tmp/$tb.name'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

作者公眾號（持續更新）

MySQL INTO OUTFILE和LOAD DATA INFILE用法

相關文章