MySQL如何覆盖已存在的文件进行INTO OUTFILE操作？

Question

我编写了一个大型的SQL脚本来创建一个CSV文件。我想每晚都调用cronjob来创建一个全新的CSV文件，并在网站上提供它。

比如说，我将文件存储在“/home/sites/example.com/www/files/backup.csv”中，

我的SQL代码是：

SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM ( ....

当文件已经存在时，MySQL会给我一个错误

文件 '/home/sites/example.com/www/files/backup.csv' 已经存在

有没有一种方法让MySQL覆盖该文件？

我可以让PHP检测文件是否存在并在再次创建它之前将其删除，但如果我可以直接在MySQL中操作，那将更简洁。

- Derek Organ

Answer 1

没有办法覆盖它。根据文档：

“file_name”不能是现有文件，这可以防止破坏像/etc/passwd和数据库表之类的文件。

每晚使用不同的文件名可能是一个更好的主意，因为拥有多个备份意味着您可以从存在超过一天的问题中恢复。然后，您可以维护一个符号链接，它始终指向最新的完整csv文件。

- brian-brazil

---

干杯，我曾经以为我可能做不到。噢，算了。这不完全是一个备份，更像是一个用户可访问的备份。备份是独立进行的。不过，值得思考。 - Derek Organ

你有没有看过Linux的logrotate工具？ - Konerak

Answer 2

为什么不能在由cron运行的脚本中使用rm -f /home/sites/example.com/www/files/backup.csv命令？

您可以从mysql中运行此命令。只需使用\!转义到shell即可，例如：

Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv

- Flavius Stef

---

是的，我可以做到，但最好在MySQL中完成，因为它需要循环遍历许多数据库并创建数百个文件。不过看起来我可能必须在PHP中完成它。 - Derek Organ

我经历了完全相同的过程，试图找到一种方法，结论是你不能这样做，因为文档提到了安全问题。 - Artem Russakovskii

我尝试过这个.. 但MySQL的响应是："rm: cannot remove `/tmp/my-report-2013-10.csv': Operation not permitted" .. 文件权限为666。有什么想法吗？ - lucasvscn

我喜欢它，这样就不用再添加一个cron了。 - Mark

执行\! whoami命令显示用户命令是由启动mysql客户端的用户执行的，而不是服务器。因此，该用户无法删除文件；我们会收到错误消息rm: cannot remove ...': Operation not permitted`。 - David Portabella

Answer 3

没有别的方法。

唯一可能的方法是使用动态语句。

CREATE PROCEDURE export_dynamic(IN file_name char(64)) 
BEGIN 
set @myvar = concat('SELECT * INTO OUTFILE ',"'",file_name,"'",' FROM Table1') ; 
PREPARE stmt1 FROM @myvar; 
EXECUTE stmt1; 
Deallocate prepare stmt1; 
END; 

- mani

Answer 4

对于像这样的工作，我会将其放入一个 bash 文件中，删除该文件。

#!/bin/bash
rm /path/to/backup.csv
./backup_sql_query.sh  <<-- This contains the script to backup to CSV.

更好的选择是实际上添加一个时间戳。如今，磁盘空间并不昂贵。

- user118659

---

不贵，只是每天更新HTML链接到新文件有点麻烦。虽然可能可以做到，但可能比必要的工作还要多。谢谢。 - Derek Organ

只需将backup.csv快速地链接到最新且最好的版本即可，一旦它被创建 - 创建新版本，删除backup.csv，链接新版本到backup.csv-- 完成了。 - Alex Martelli

是的，我喜欢ln -s的想法。嗯。 - Derek Organ

Answer 5

完成此任务需要3个步骤。

步骤1：为你的SQL创建一个存储过程。

当你入睡时（或者不入睡），你的备份将每晚执行一次。

CREATE PROCEDURE backupCSV()
SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ( ....

步骤二：创建一个脚本“/home/backupCSV.sh”，用于删除旧文件并调用存储的过程。

echo "$(date +"%Y-%m-%d %T") START MAINTENANCE SCRIPT "
rm /home/sites/example.com/www/files/backup.csv
echo "$(date +"%Y-%m-%d %T")    SUCCESS >> Old file deleted"
mysql --user=[user] --password=[password] [dataBaseName] --execute="CALL backupCSV();"
echo "$(date +"%Y-%m-%d %T")    SUCCESS >> New file created"
echo "$(date +"%Y-%m-%d %T") END MAINTENANCE SCRIPT "

步骤三：更新Crontab以便每天执行脚本。

# m h dom mon dow user  command

 0   3  * * *   root    sh -x /home/backupCSV.sh

步骤四(可选)：感谢我 ;)

- Benjamin SAVIGNAC

Answer 6

虽然这是一个老问题，但你可以在语句中添加选项 OVERWRITE ON。

SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' OVERWRITE ON
...

- da Bich

---

我已经在MySQL 8.0和MariaDB 10.3上测试了OVERWRITE ON，两者都会出现“ERROR 1064 (42000) ... You have an error in your SQL syntax;”的错误。我也找不到文档中提到OVERWRITE ON的内容。那么，哪个版本/分支的MySQL支持此选项？ - dbdemon

看起来Amazon RDS有OVERWRITE ON选项（保存到S3存储桶时）：https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Integrating.SaveIntoS3.html - dbdemon

正确的...抱歉...我正在使用亚马逊RDS（Aurora） - da Bich

Answer 7

在mysql中从一个shell中简单地执行rm命令以删除文件，然后再尝试写入它。例如：

Mysql> \\! rm -f /home/sites/example.com/www/files/backup.csv

- WxWizard

---

尝试过这个，但是出现了“rm: cannot remove ‘/tmp/blah.User.csv’: Operation not permitted”的错误。 - Michael Cole