Syntax Error - OUTFILE with Timestamp

Question

I have managed to come up wit the below script for exporting some files. This needs to be done on a number of datasets, and is done daily.

The problem I am having is the I am getting the following error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''20160913.csv'' at line 12

    SET @exportfile = 
   CONCAT("SELECT   
            'FIELD_1'
        ,   'FIELD_2'
        ,   'FIELD_3'
        ,   'FIELD_4'
        ,   'FIELD_5'
        ,   'FIELD_6'
        UNION ALL
        SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_' 
        FIELDS TERMINATED BY ','
        ENCLOSED BY '''
        LINES TERMINATED BY '\r\n'"
       , DATE_FORMAT( NOW(), '%Y%m%d')
       , ".csv'"
    );

PREPARE processing FROM @exportfile;
EXECUTE processing;
DROP PREPARE processing;

Please can someone identify what I am doing wrong?

Answer 1

The immediate issue is that you are appending the formatted date string and .csv' to the end of the query string, rather than to the end of the file path as I am assuming is the intention.

It seems to me your CONCAT expression should actually go like this:

CONCAT("SELECT ... INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_"
  , DATE_FORMAT( NOW(), '%Y%m%d')
  , ".csv'
    FIELDS TERMINATED BY ','
    ..."
)

Note that there's no ' at the end of the E:/Data/exports/DailyExport/myfilename_, because the date is appended at that point, followed by .csv' which completes the name.

Once you have fixed that, you are going to face another issue, which will be caused by the ENCLOSED BY line of your dynamic query. The ' character must be escaped when itself enclosed in '. So, it should be either:

...
ENCLOSED BY ''''
...

or

...
ENCLOSED BY '\''
...