php MySQL INTO OUTFILE 覆盖现有文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/960627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL INTO OUTFILE override existing file?
提问by Derek Organ
I've written a big sql script that creates a CSV file. I want to call a cronjob every night to create a fresh CSV file and have it available on the website.
我写了一个大的 sql 脚本来创建一个 CSV 文件。我想每晚调用一个 cronjob 来创建一个新的 CSV 文件并在网站上提供它。
Say for example I'm store my file in '/home/sites/example.com/www/files/backup.csv'
比如说我将我的文件存储在“/home/sites/example.com/www/files/backup.csv”中
and my SQL is
我的 SQL 是
SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ( ....
MySQL gives me an error when the file already exists
当文件已经存在时，MySQL 给我一个错误
File '/home/sites/example.com/www/files/backup.csv' already exists
文件“/home/sites/example.com/www/files/backup.csv”已经存在
Is there a way to make MySQL overwrite the file?
有没有办法让 MySQL 覆盖文件？
I could have PHP detect if the file exists and delete it before creating it again but it would be more succinct if I can do it directly in MySQL.
我可以让 PHP 检测文件是否存在并在再次创建之前将其删除，但如果我可以直接在 MySQL 中执行它会更简洁。
回答by brian-brazil
No, there's no way to overwrite it. From the docs:
不，没有办法覆盖它。从文档：
file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.
file_name 不能是现有文件，这可以防止 /etc/passwd 和数据库表等文件被破坏。
It might be a better idea to use a different filename each night, as having multiple backups means you can recover from problems that have existed for more than a day. You could then maintain a symlink that always points at the latest complete csv.
每晚使用不同的文件名可能是一个更好的主意，因为拥有多个备份意味着您可以从存在超过一天的问题中恢复。然后，您可以维护一个始终指向最新完整 csv 的符号链接。
回答by Flavius Stef
Why not rm -f /home/sites/example.com/www/files/backup.csvin the script ran by cron?
为什么不在rm -f /home/sites/example.com/www/files/backup.csv由 cron 运行的脚本中？
You can run this from inside mysql. Just escape to the shell with \!For example:
您可以从 mysql 内部运行它。只需使用\!例如逃逸到外壳：
Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv
Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv
回答by user118659
For a job like this I would place it into a bash file, delete the file
对于这样的工作，我会将它放入一个 bash 文件中，删除该文件
#!/bin/bash
rm /path/to/backup.csv
./backup_sql_query.sh <<-- This contains the script to backup to CSV.
The better option is to actually add a timestamp though. Disk space isn't expensive in this day and age.
更好的选择是实际添加时间戳。在这个时代，磁盘空间并不昂贵。
回答by mani
There is no way.
不可能。
Only one possible you can procedure with dynamic statement.
只有一种可能您可以使用动态语句进行程序。
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;
回答by Benjamin SAVIGNAC
3 steps to do this right. Your backup will be executed every night while you sleep (or not)
正确执行此操作的 3 个步骤。您的备份将在您睡觉（或不睡觉）时每晚执行
STEP 1 : Create a stored procedure for your SQL
步骤 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 ( ....
STEP 2 : Create a script "/home/backupCSV.sh" to delete old file and call the stored procedure
STEP 2：创建一个脚本“/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 "
STEP 3 : Update Crontab to execute the script everyday
第 3 步：更新 Crontab 以每天执行脚本
# m h dom mon dow user command
0 3 * * * root sh -x /home/backupCSV.sh
STEP 4 (optionnal) : thanks me ;)
第 4 步（可选）：谢谢我；）
回答by da Bich
old question.. but there is an option OVERWRITE ON that you can add to the statement
老问题.. 但是有一个选项 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
...
回答by WxWizard
Simply escape to a shell from within mysql and execute a rm command to remove the file before you attempt to write it. For example:
在尝试写入文件之前，只需从 mysql 中转义到 shell 并执行 rm 命令以删除该文件。例如：
Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv
