MySQL导出到输出文件并覆盖

Question

我对将mysql select语句导出到samba共享上的文件感兴趣。在我尝试更新文件之前，一切都很正常。例如，我让mysql导出到outfile peachtree.csv，当数据改变时，我想让它用新数据覆盖旧文件，但是，我得到了错误:error1086 (HY000)：文件'/srv/samba/share/peachtree.csv‘已经存在。是否有一个选项或开关，我可以放在语句中，以强制或使其覆盖现有文件？

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

Answer 1

你不能。这是一项安全功能。否则，您可以选择像/etc/password或/etc/shells这样的文件。

从手册中：

SELECT ...INTO OUTFILE 'file_name‘形式的SELECT将选定的行写到文件中。该文件是在服务器主机上创建的，因此您必须具有file权限才能使用此语法。密码不能是现有文件，这可以防止/ file_name /passwd和数据库表等文件被破坏。

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

Answer 2

此解决方案使用动态SQL并写入Windows文件系统。

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

MySQL INTO OUTFILE override existing file?上的其他答案