Mysql命令下导出select查询数据之 select ... into outfile方法

时间:2023-07-10 23:58:50

Mysql日常使用中经常遇到将select查询的数据导出到本地目录的情况,以便数据备份、分析等。

接下来将介绍Mysql终端下使用 select ... into outfile 语句导出数据方法

命令:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'; /**
参数说明:
FIELDS TERMINATED BY 用来对字段值之间进行分割,
FIELDS [OPTIONALLY] ENCLOSED BY 用来对字段值进行包裹,
FIELDS  LINES TERMINATED BY 用来对每行结束指定换行符
*/

举例

mysql> SELECT * from student into outfile '/home/homework/shenwenzhe/student.txt'; 

另外:

介绍下into outfile命令的相反命令:load data infile  数据导入,命令如下:

load data infile "/home/homework/shenwenzhe/student.txt" into table student;

注意点:如果导出时用到了FIELDS TERMINATED BY ',' ,OPTIONALLY ENCLOSED BY '"', LINES TERMINATED BY '\n'等语句,那么load时也要加上同样的分隔限制语句,并且要注意数据编码问题。