Howto mysql multiple outfiles into a single compressed zip

Question

1. I am running Xubuntu 16.04 and MariaDB with MySQL.
2. These 3 files (customers.csv, items.csv, invoices.csv) are created by MySQL INTO OUTFILE commands as seen below.
3. These 3 files needs to go into a single zip file (report.zip).
4. The 3 files (customers.csv, items.csv, invoices.csv) don't need to be saved permanently as they only serve as temp files so that they can be packed into the zip file.

My sample MySQL outfile commands:

SELECT customer_id, firstname, surname FROM customers INTO OUTFILE '/tmp/customers.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT item_id, itemname, item_plu FROM items INTO OUTFILE '/tmp/items.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT invoice_id, invoice_total FROM invoices INTO OUTFILE '/tmp/invoices.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Question

Do you guys know a MySQL command in which the INTO OUTFILE files go directly into a zip file without being stored as 3 additional separate files onto the disk?

Answer 1

I just read your question and I found the answer in the same time while I was looking for the same feature. So I share what I found : Unfortunately, it seems that MySQL doesnt support direct output compression, this feature is requested but not implemented yet :

This is the link

The best way in my knowledge to do that would be to do it in two steps :

1. Output
2. Zip with command line

Joffrey