MYSQL - How would I Export tables specifying only certain fields?

Question

How would I Export tables specifying only certain fields?

I am using mysql 5.0 - using either a sql command or mysqldump.

My table is X, and the fields I want to export are A,B,C

Solution 1

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

You need the FILE privilege to do this, and it won't overwrite files.

INTO OUTFILE has a bunch of options to it as well, such as FIELDS ENCLOSED BY, FIELDS ESCAPED BY, etc... that you may want to look up in the manual.

To produce a CSV file, you would do something like:

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

To load the data back in from the file, use the LOAD DATA INFILE command with the same options you used to dump it out. For the CSV format above, that would be

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

Solution 2

If you are using phpMyAdmin,

Run the query

SELECT A,B,C FROM X

and there is an export option in the bottom of the result.

Solution 3

OUTFILE doesn't produce you a SQL dump, neither mysqldump can operate on subset of columns. But you can create table temp_weeeee select ...., export and drop it.

Solution 4

Shell command

echo 'select field from db.table;' | mysql -u user -p password > output.txt

Solution 5

Try:

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

View more solutions

Comments

[Danny Schoemann]

Perfect! Obviously it works with multiple fields also: echo 'select column1, column2, column3 from database.table;' | mysql -u user -ppassword > output.txt

[axil]

Thanks for the shell command! This also includes the field name in the output. Is there any way it can be ommited?

[Burhan Ali]

@axil Use the -N, --skip-column-names option.