Geek Answers Handbook

MySQL Export to Outfile and Overwrite

I am interested in exporting the mysql select statement to a file on share samba. Everything works fine until I try to update the file. For example, I have mysql exporting to outfile peachtree.csv, and when the data changes, I want it to overwrite the old file with the new data, I get the error: ERROR 1086 (HY000): File '/ srv / samba / share / peachtree .csv 'already exists. Is there an option or switch that I can put in a statement to force or do this over the top of an existing file?

SELECT * FROM peachtree, INTO OUTFILE '/srv/samba/share/peachtree.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
+4
source share
2 answers

You can not. This is a safety feature. Otherwise, you may be able to select files such as / etc / password or / etc / shells.

From the manual:

Form SELECT ... INTO OUTFILE 'file_name' SELECT writes selected lines to a file. The file is created on the server, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file that, among other things, prevents files such as / etc / passwd and database tables from being destroyed.

http://dev.mysql.com/doc/refman/5.0/en/select.html

+5
source

This solution uses dynamic SQL and writes to the Windows file system.

 Select Now() Into @Now; # get DateTime Set @Now = Replace(@Now, ':', '-'); # format DateTime for the filesystem # set Destination filename using formatted DateTime Set @FilNam = Concat("'", 'd:/Folder/qrySelectToFile ', @Now, '.TXT', "'"); #Select @FilNam; # debug # build query to execute, one clause at a time, for encoding purposes Set @Sel = 'Select * From vewViewName Into OutFile ' ; # Select From Into Set @FldsTrm = ' Fields Terminated By ","' ; # Fields Terminated Set @FldsEnc = Concat(" Enclosed By '", Char(34 Using utf8), "'") ; # Enclosed. 'Using' clause is to avoid a blob of 34 spaces. Set @LinsTrm = " Lines Terminated By '\\r\\n'" ; # Lines Terminated Set @Qry = Concat(@Sel, @FilNam, @FldsTrm, @FldsEnc, @LinsTrm, ';'); # all together now Prepare Stmt From @Qry; # Prepare Execute Stmt ; # Execute Deallocate Prepare Stmt ; # Done

Other MySQL INTO OUTFILE answers overlap an existing file?

0
source

More articles:


All Articles