INTO OUTFILE (MySQL): verschil tussen versies

Versie van 30 sep 2018 12:32

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

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;

INTO OUTFILE (MySQL): verschil tussen versies

Versie van 30 sep 2018 12:32

Inhoud