MySQL Admin Cookbook by Daniel Schneller & Udo Schwedt

Author:Daniel Schneller & Udo Schwedt [Daniel Schneller]
Language: eng
Format: epub, mobi
Publisher: Packt Publishing

There's more...

While the previous SELECT … INTO OUTFILE statement will work well in most cases, there are some circumstances in which you still might encounter problems. The following topics will show you how to handle some of those.

Handling errors if the target file already exists

If you try to execute the SELECT … INTO OUTFILE statement twice, an error File 'C:/target.csv' already exists occurs. This is due to a security feature in MySQL that makes sure that you cannot overwrite existing files using the SELECT … INTO OUTFILE statement. This makes perfect sense if you think about the consequences. If this were not the case, you could overwrite the MySQL data files using a simple SELECT because MySQL server needs write access to its data directories. As a result, you have to choose different target files for each export (or remove old files in advance).

Unfortunately, it is not possible to use a non-constant file name (like a variable) in the SELECT … INTO OUTFILE export statement. If you wish to use different file names, for example, with a time stamp as part of the file name, you have to construct the statement inside a variable value before executing it:

mysql> SET @selInOutfileCmd := concat("SELECT * FROM sample.table1 INTO OUTFILE 'C:/target-", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".csv' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\r\n';");mysql> PREPARE statement FROM @selInOutfileCmd;mysql> EXECUTE statement;

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.