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.

Tekencodering

In beginsel wordt een bestand weggeschreven in dezelfde karakterset als de betreffende tabel/kolommen. 't Zou handig zijn als je dit kunt aanpassen tijden het wegschrijven.

Specificeren bij wegschrijven?

Deze posting suggereert dat je zoiets kunt doen:

select * from sq_tmp 
into outfile '/tmp/test02.csv'
character set latin1;

Ik krijg welliswaar geen foutmelding, maar de tekencodering wordt helaas niet aangepast. Verderop in dezelfde posting heeft iemand ditzelfde probleem. De MySQL Reference Guide vermeldt deze optie niet,behalve voor de codering van de bestandsnaam

Converteren bij wegschrijven?

Dezelfde posting suggereert ook nog zoiets als:

SELECT convert(col_name USING latin1) FROM posts INTO OUTFILE '/tmp/x.csv' …;

maar ik zie niet goed hoe ik dat op een gemakkelijke manier kan toepassen op een tabel als geheel. Wat in ieder geval niet werkt:

select convert(* using latin1) from sq_tmp;

Systeemvariabele?

Er lijkt een systeemvariabele te bestaan, die de codering van exports regelt. Die variabele kun je uiteraard aanpassen - Hopelijk zonder herstart oid.

Mbv. een tijdelijke tabel

De oplossing die ik eigenlijk altijd toepas:

Kopiëer de betreffende tabel naar een andere, tijdelijke tabel
Pas de codering van die tijdelijke tabel aan
Schrijf die tijdelijke tabel weg.

Werk altijd, en belangrijker: Het is een hele simpele oplossing, dus als ik die code over een half jaar weer onder ogen kom, snap ik nog steeds wat er bedoeld wordt.