MySQL how do you append to a file with INTO OUTFILE?

Question

I have the following code:

SELECT * INTO OUTFILE'~/TestInput/Results.csv'      
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM Results;

Desired results are to continually keep on appending to Results.csv

Moina

Answer 1

You can merge results and write at once. TEE will log everything which might not be desirable. In your case:

SELECT * FROM Results UNION 
SELECT * FROM Results INTO OUTFILE '~/TestInput/Results.csv';

Mainsheet

Answer 2

You can't do that with SELECT INTO OUTFILE. That command only creates new files, and will fail if the file already exists.

You can append query output to an existing files using the TEE command in the MySQL client.

Here's your example appending two query results to the same file using TEE:

TEE ~/TestInput/Results.csv

SELECT * 
FROM Results;

SELECT * 
FROM Results;

NOTEE

Sandbox

How would I wrap that in a stored procedure and check if the file exists first (before using TEE)? – Moina

If you want to check if the file exists first, you should do that in a scripting language (python, bash, perl, etc), not in a stored procedure. – Sandbox

Answer 3

It is not possible to do it directly in MySQL. But you may try to add date-time part into file name, then combine some files to a new one with a 'cat' (UNIX command) or 'type' (DOS command).

Help: cat (Unix)

Interpretation

Thanks. The link is changed. – Interpretation

Answer 4

Mysql 5.7 and beyond allows outfile to be a fifo file (mkfifo). You can run a script that reads from the fifo, as simple as cat myfifo myfifo myfifo myfifo myfifo myfifo ... >outfilefile. The fifo file needs as many open read close sequences as you'll execute select into outfile. Otherwise select into outfile does not allow overwriting or appending to an existing regular file.

Sialagogue