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

Question

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?

Answer 1

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

• 13.2.10.1 SELECT ... INTO Statement

• TABLE employees ORDER BY lname LIMIT 1000
      INTO OUTFILE '/tmp/employee_data_1.txt'
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
      LINES TERMINATED BY '\n';
  

• 13.2.7 LOAD DATA Statement