MySQL How to continually create unique files to output?

Question

I have the following storedprocedure

DELIMITER ##
CREATE PROCEDURE exportFile()
BEGIN

Select * from Sample
INTO OUTFILE '~/Sample.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

END ##
DELIMITER ;

• After first call to the stored procedure you will get ERROR 1086 (HY000): File '~/Sample.csv' already exists -- And rightfully so.
• I need to continually keep creating new files. (ie. Sample1.csv, Sample2.csv, Sample3.csv ... and so on) on every call.

Question: How do I do that?

Answer 1

Why not append a timestamp to the file ?

Here an example appending timestamp to the file :

DELIMITER ##
CREATE PROCEDURE exportFile()
BEGIN

Select * from Sample
INTO OUTFILE CONCAT('~/Sample', CONCAT(DATE_FORMAT( your_date_field, '%d%m%Y') ,'.csv'))
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

END ##
DELIMITER ;

Answer 2

I arrived at a similar requirement for debugging a recursive stored proc.

This worked for me:

SET @s = CONCAT('SELECT * INTO OUTFILE ',"'", CONCAT('Result',FLOOR(1000+RAND()*9999)),'.txt',"'",' FROM TempTable112');
PREPARE stmt2 FROM @s;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

I use xampp and got the output at C:\xampp\mysql\data\myfolder.