MySQL into Outfile Override Existing File

MySQL INTO OUTFILE override existing file?

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.

MySQL Export into outfile and overwrite

You cannot. This is a security feature. Otherwise, you might be able
to select into such files as /etc/password or /etc/shells.

From the manual:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the
selected rows to a file. The file is created on the server host, so
you must have the FILE privilege to use this syntax. file_name cannot
be an existing file, which among other things prevents files such as
/etc/passwd and database tables from being destroyed.

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

why does SELECT INTO OUTFILE give file exists error even though file does not exist?

SELECT INTO OUTFILE writes results to a server file.

Are you checking for the file existence on server?

If you want to select into a local file on your client machine, just redirect mysql output:

mysql mydb < script.sql > /tmp/records_materialized_view.txt

MySQL select into outfile does not write the file to the directory that I choose

The path doesn't seem write - if you are on Linux server then I would expect the path with contains slashes and not backslashes, if you are on Windows machine I would expect the driver letter to appears somewhere (and you can't write on a network folder - just local to the DB server machine).

Also, just to eliminate permissions issue or trying to override existing file, try to write to '/tmp/non_existing_file.csv'.

INTO OUTFILE using Execute Statement

I highly doubt that MySQL supports passing the query string as a parameter in the insert into outfile syntax. You would need to do string concatenation instead. It would also be a good idea to quote the file name, since it will contain blank characters.

set @filename = concat('/home/reports/', current_timestamp(), '.csv');

set @query=
    concat(
        "insert into outfile '", 
        @filename, 
        "' select * from  tablename limit 10 ",
       "fields terminated by ',';"
    );

prepare s1 from @query;
execute s1;
deallocate prepare s1;

---

Related Topics