MySQL INTO OUTFILE和LOAD DATA INFILE用法

【导出】：

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

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

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

导出文档如下：
可以看出，每条记录以回车换行为界，每个字段以tab为界，这是outfile的默认分界符。



如果想自定义字段的分隔符、每条记录的分隔符、还有每条字段的包裹符，可以通过加参数来实现。
以第一种导入方式为例（SELECT ... FROM table_name INTO OUTFILE '/tmp/test.txt')：

1. mysql> SELECT * FROM Country INTO OUTFILE '/tmp/test.txt' 
2.     -> FIELDS TERMINATED BY ',' 
3.     -> OPTIONALLY ENCLOSED BY '"' 
4.     -> 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分隔符、换行符等不同，可以加上不同的参数：
比如 

1. LOAD DATA INFILE '/tmp/test.txt' INTO TABLE table_name
   
2. FIELDS
   
3. TERMINATED BY ','
   
4. ENCLOSED BY '"'
   
5. LINES
   
6. TERMINATED BY '\r';

如果要忽略，可以IGNORE n LINES，代表忽略前n行。
上述详细语法已经给出。（详细语法摘自MySQL 5.6 Reference Manual）

----------------------------  更新
mysqldump也也有等同into outfile的参数：
比如

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

默认会在当前目录下生成一个 $tb_name.txt 的文件
等同于

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

作者公众号（持续更新）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29773961/viewspace-1795089/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29773961/viewspace-1795089/