MySQL INTO OUTFILE override existing file?

php mysql sql into-outfile
Derek Organ picture Derek Organ · Jun 6, 2009 · Viewed 71.4k times · Source

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.

Say for example I'm store my file in '/home/sites/example.com/www/files/backup.csv'

and my SQL is 

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

File '/home/sites/example.com/www/files/backup.csv' already exists

Is there a way to make MySQL overwrite the file?

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.

Answer

brian-brazil picture brian-brazil · Jun 7, 2009

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.

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.

Related questions

Syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in PHP

mysql_connect("localhost","root",""); mysql_select_db("hitnrunf_db"); $result=mysql_query("select * from jos_users INTO OUTFILE 'users.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' "); header("Content-type: …

Read More
How do I handle NULL values in a mysql SELECT ... OUTFILE statement in conjunction with FIELDS ESCAPED BY? NULL values are currently being truncated

I'm encountering some difficulties using MySQL's SELECT ... OUTFILE on result sets that include both null values and columns that require double quote escaping (ie, columns that contain '"' characters). This is the outfile syntax I am using: INTO OUTFILE …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More