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';

Sproc (1)

Vanwege de redenen die hieronder genoemd worden, maak ik gebruik van sprocs met DDL. Dat bevalt me uitstekend, is flexibel en erg eenvoudig. Bv.:

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;

Sproc (2)

-- --------------------------------------------------------------------------------
-- 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