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

[1]:

Column values are written converted to the character set specified in the CHARACTER SET clause. 
If no such clause is present, values are dumped using the binary character set. In effect, there 
is no character set conversion. If a result set contains columns in several character sets, the 
output data file will as well and you may not be able to reload the file correctly.

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