MySQL Export into outfile and overwrite

Question

我对将MySQL选择语句导出到Samba共享文件中很感兴趣。一切都进行得很顺利，直到我尝试更新文件时发现问题。例如，我让MySQL导出到文件peachtree.csv，当数据发生更改时，希望用新数据覆盖旧文件，但是却出现了错误：ERROR 1086 (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';

- Colin Inskeep

---

不行，它不能被覆盖。这里的答案指向文档。https://dev59.com/WHNA5IYBdhLWcg3wcNbF。 - Erik Nedwidek

Answer 1

你不能这样做。这是一个安全特性。否则，你可能会选择到 /etc/password 或 /etc/shells 这样的文件。

从手册中可以看到：

SELECT ... INTO OUTFILE 'file_name' 形式的 SELECT 将选定的行写入文件中。该文件在服务器主机上创建，因此您必须具备文件权限才能使用此语法。file_name 不能是现有文件，这样可以防止破坏诸如 /etc/passwd 和数据库表等文件所造成的危害。

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

- 123

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 覆盖现有文件?

- Bilbo