INTO OUTFILE (MySQL)
De gebruikelijke manier om vanuit MySQL records naar een bestand te schrijven, gaat met into outfile
.
Voorbeelden
select * from tabel_export into outfile '/tmp/tabel_export.tsv';
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
SELECT field1, field2 FROM table1 INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' FIELDS ESCAPED BY '\' LINES TERMINATED BY '\n';
Voorbeelden: Sprocs + DDL
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_save`( in tabelnaam varchar(100), bestandsnaam varchar(100) ) BEGIN set @ddl=concat ( "select * from ",tabelnaam," order by internal_sort_order asc ", "into outfile '/var/mysql-export/", bestandsnaam, "-", DATE_FORMAT( NOW(),'%Y%m%d-%H%i%S'),".tsv'", " fields terminated by '\\t' enclosed by '",char(34), "' lines terminated by '\\n'" ); prepare s1 from @ddl; execute s1; drop prepare s1;
-- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments before and after the routine body will not be stored by the server -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `export_save`( in bron varchar(100), in sorteer varchar(100), in bestandsnaam varchar(100) ) BEGIN -- -- Write records to a file -- ======================= -- -- Input arguments: -- -- * 'bron': Name of a table or view -- * 'sorteer': Sort order without the words "order by" -- * 'bestandsnaam': filename. -- -- Examples -- ======== -- -- * call export_save("superquery_tmp","","superquery"); -- * call export_save("superquery_tmp","internal_sort_order asc","superquery"); set @ddl=concat ( "select * from ",bron, if ( sorteer = "", " ", concat(" order by ",sorteer," ") ), "into outfile '/var/mysql-export/", bestandsnaam, "-", DATE_FORMAT( NOW(),'%Y%m%d-%H%i%S'),".tsv'", " fields terminated by '\\t' enclosed by '",char(34), "' lines terminated by '\\n'" ); select @ddl; prepare s1 from @ddl; execute s1; drop prepare s1; END
Bestanden kunnen niet overschreven worden
Je kunt een bestaand bestand niet overschrijven. Oplossing: DDL gebruiken en een timestamp aan de bestandsnaam toevoegen.
/var ipv. /tmp
Schrijven naar /tmp
geeft voortdurend vage klachten. Daarom schrijf ik naar /var
. Naar
/var/mysql-export
om precies te zijn. Permissies op die map zijn __7, zodat het MySQL-proces kan schrijven.
Character set
De characterset hangt af van de characterset van de brongegevens in de MySQL-database. Let op: Die kun je zowel per tabel als per kolom specificeren. Oplossing: Op tabelniveau defineren (bv. latin1) en op kolomniveau specificeren: table default
.
Bronnen
- http://stackoverflow.com/questions/21253704/how-to-save-mysql-query-output-to-excel-or-txt-file
- https://www.michaelrigart.be/en/blog/export-directly-from-mysql-to-csv.html
- http://dev.mysql.com/doc/refman/5.7/en/select-into.html
- http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_filesystem