1、select into outfile可以导出数据到指定目录下的文件中

语法：

SELECT...INTOOUTFILE'file_name'[CHARACTERSETcharset_name][export_options]export_options:[{FIELDS|COLUMNS}[TERMINATEDBY'string'][[OPTIONALLY]ENCLOSEDBY'char'][ESCAPEDBY'char']][LINES[STARTINGBY'string'][TERMINATEDBY'string']

2、示例如下：

1）查看要导出数据的表t1

mysql>select*fromt1;+------+--------+|id|name|+------+--------+|1|wang||2|steven||3|tiger||4|lilu|+------+--------+4rowsinset(0.00sec)

2）查看导出的文件

[mysql@oneproxyoutfile]$moret1.file1,"wang"2,"steven"3,"tiger"4,"lilu"

3）创建要导入的表t2;

mysql>createtablet2asselect*fromt1where1=2;QueryOK,0rowsaffected(0.03sec)

4）将导出的数据导入t2

mysql>LOADDATAINFILE'/mysql/outfile/t1.file'INTOTABLEt2FIELDSTERMINATEDBY','ENCLOSEDBY'"'LINESTERMINATEDBY'\n';QueryOK,4rowsaffected(0.01sec)Records:4Deleted:0Skipped:0Warnings:0

5）查看导入的数据

mysql>select*fromt2;+------+--------+|id|name|+------+--------+|1|wang||2|steven||3|tiger||4|lilu|+------+--------+4rowsinset(0.00sec)