Search code examples
mysqlexport-to-csvselect-into-outfile

Mysql How to outfile in csv format, with sheets options?

I want to export data from mysql into a csv. There are many tables so I want a csv file with many sheets. How it can be done?

I suppose somethings like:

SELECT *
FROM product
WHERE active = 1
INTO OUTFILE '/root/tmp/data.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

SELECT *
FROM member
WHERE active = 1
INTO OUTFILE '/root/tmp/data.csv' // using the same .csv
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

etc...
Solution

  • You cannot append to an existing file using INTO OUTFILE.

    https://dev.mysql.com/doc/refman/5.7/en/select-into.html says:

    file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

    So you'll have to output to a different file per table, and then concatenate them together yourself (that is, not using SQL).