SELECT ... INTO OUTFILE statement on Stored Procedure in MySQL

Question

I need export one MySQL table in .csv format.

To do this I have tried this Stored Procedure using the SELECT ... INTO OUTFILE statement is intended to enable dumping a table to a csv file on the server host.

But I receive thsi error :

Procedure execution failed 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 'C:\inetpub\ftproot\tm_20200322.csv' at line 1

Can you help me ?

My code below.

BEGIN

  SET @tbl = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY), '%Y%m%d');
  SET @s = CONCAT('C:\\inetpub\\ftproot\\\tm_', @tbl, '.csv');
  PREPARE stmt FROM @s;
  EXECUTE stmt;

  SET @s = CONCAT('SELECT * FROM `dotable` INTO OUTFILE @s FIELDS TERMINATED BY ";" LINES TERMINATED BY "\n"');
  PREPARE stmt FROM @s;
  EXECUTE stmt;

END

Answer 1

The file name must be enclosed with single quotes. Also, it is unclear why you would need to execute twice. You also need to carefuly use the escaping \ (there are places where you have one to many, others where one is mising).

This should do what you want:

SET @tbl = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY), '%Y%m%d');
SET @s = CONCAT('C:\\inetpub\\ftproot\\tm_', @tbl, '.csv');
SET @s = CONCAT('SELECT * FROM `dotable` INTO OUTFILE ''', @s, ''' FIELDS TERMINATED BY ";" LINES TERMINATED BY "\\n"');
PREPARE stmt FROM @s;
EXECUTE stmt;

This code generates the following query:

SELECT * FROM `dotable` INTO OUTFILE 'C:\inetpub\ftproot\tm_20200322.csv' FIELDS TERMINATED BY ";" LINES TERMINATED BY "\n"