MySQL: Why am I getting a syntax error when using the FIELDS ESCAPED BY clause on a select statement

MySQL

I am attempting to generate a csv file including headers from a table in my MySQL 8.0 database. After a lot of searching I found the following suggest syntax:

    SELECT "ansId","assmt","ques","answerCode","details"
    FROM ayanswer
    UNION
    SELECT ansId,assmt,ques,answerCode,details
    FROM ayanswer
    INTO OUTFILE 'c:/data/test1.csv'
    FIELDS TERMINATED by ','
    OPTIONALLY ENCLOSED BY '"'
    FIELDS ESCAPED BY '\\';

However when I run this statement in mysql I get the following error:
ERROR 1064 (42000) at line 1: 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 'FIELDS ESCAPED BY '\'' at line 9

According to the MySQL Server 8.0 reference manual this syntax should be correct. So what am I doing wrong?

Best Answer

This seems to be a simple syntax issue. Use the below query instead which basically doesn't use FIELDS before ESCAPED by '\\' since it's already specified before TERMINATED by ','.

 SELECT "ansId","assmt","ques","answerCode","details" FROM ayanswer
 UNION
 SELECT ansId,assmt,ques,answerCode,details FROM ayanswer
 INTO OUTFILE 'c:/data/test1.csv'
 FIELDS TERMINATED by ','
 OPTIONALLY ENCLOSED BY '"'
 ESCAPED by '\\';

Supporting Resources

Related Question