INTO OUTFILE using Execute Statement

Question

I need to get a data from a mysql database. I have the below like:

set @filename=concat('/home/reports/',current_timestamp(),'.csv');
set @querys=concat("select * from  tablename limit 10 ",
"into outfile ? fields terminated by ',';");
prepare s1 from @querys;
execute s1 using @filename;
deallocate prepare s1;

And I got this error: 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 '? fields terminated by ','' at line 1
Do you know why?

Answer 1

I highly doubt that MySQL supports passing the query string as a parameter in the insert into outfile syntax. You would need to do string concatenation instead. It would also be a good idea to quote the file name, since it will contain blank characters.

set @filename = concat('/home/reports/', current_timestamp(), '.csv');

set @query=
    concat(
        "insert into outfile '", 
        @filename, 
        "' select * from  tablename limit 10 ",
       "fields terminated by ',';"
    );

prepare s1 from @query;
execute s1;
deallocate prepare s1;

Answer 2

I think your query syntax is wrong, it should be

SELECT * INTO OUTFILE '/tmp/USERS.csv' from users FIELDS TERMINATED BY ',' ENCLOSED BY '"' limit 10;

Also check path location mentioned in:

mysql> SELECT @@secure_file_priv;