Pass outfile FILE_PATH as parameter when exporting CSV from MySQL query

Question

How do I pass the outfile file path to MySQL? I have tried the below. It does not work.

My SQL file

SET @query = CONCAT('SELECT *
FROM
     `registered_user`
INTO OUTFILE ',@output_location,' FIELDS TERMINATED BY \',\'
LINES TERMINATED BY \'\n\'');

PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

My command-line command

mysql -u root -p test -e "set @output_location= '/var/tmp/out.csv'; source test2.sql;"

I get the below error

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 '/var/tmp/out.csv FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'' at line 12

It works if I give only out.csv. But I don't know where the file is stored. Am I missing any escape sequence in the above command?

This problem has been reported to MySQL. But has there been a solution?

Answer 1

You can simply debug by selecting the query like select @query how it was framed.

On concat Try double quotes " instead of single quotes ' when you have quotes inside. This will avoid missing ' which you have right now. And don't forget to escape \n when concat like \\n

SET @query = CONCAT("SELECT *
FROM
     `registered_user`
INTO OUTFILE '",@output_location,"'  -- you need to enclose path with quotes
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\\n'");

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;