MYSQL-如何导出仅指定某些字段的表?

Asked
Viewd17805

12

如何导出仅指定某些字段的表?

我正在使用mysql 5.0-使用sql命令或mysqldump。

我的表是X,我要导出的字段是A,B,C

5 个答案

21
 SELECT A,B,C
FROM X
INTO OUTFILE 'file name';
 

您需要FILE权限才能执行此操作,并且它不会覆盖文件。

INTO OUTFILE也有很多选择,例如FIELDS ENCLOSED BYFIELDS ESCAPED BY等...您可能要在手册中查找

要生成CSV文件,您可以执行以下操作:

 SELECT A,B,C
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM X;
 

要从文件中加载数据,请使用 LOAD DATA INFILE 命令,具有与转储相同的选项。对于上面的CSV格式,应该是

 LOAD DATA INFILE '/tmp/result.txt'
INTO TABLE X
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
 
1

尝试:

 SELECT col1, col2
  INTO OUTFILE '/filepath/export.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
FROM table;