INTO OUTFILE & tekencodering (MySQL)

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.

Codering specificeren bij aanmaak tabel

Dit werkt, maar het is niet flexibel: Je bepaalt één keer de codering. Voorbeeld:

MySQL

drop table if exists tmp;
create table tmp(column_a text) DEFAULT CHARACTER SET = utf8;
insert into tmp values ("€");
select * from tmp into outfile "/tmp/1053-utf8.txt";

drop table if exists tmp;
create table tmp(column_a text) DEFAULT CHARACTER SET = latin1;
insert into tmp values ("€");
select * from tmp into outfile "/tmp/1053-latin1.txt";

Hexdump

>> hd 1053-latin1.txt 

00000000  80 0a
00000002

>> hd 1053-utf8.txt

00000000  e2 82 ac 0a
00000004

Specificeren bij wegschrijven?

Deze posting suggereert dat je de optie character set kunt gebruiken in into outfile. Dit schijnt een vrij recente toevoeging te zijn aan MySQL, en daarom niet in alle documentatie te zijn opgenomen. In het verleden had ik hier geen succes mee, maar in okt. 2018 werkte dit:

drop table if exists tmp;
create table tmp(column_a text) DEFAULT CHARACTER SET = utf8;
insert into tmp values ("€");

select * from tmp into outfile "/tmp/1059-utf8.txt";

select * from tmp into outfile "/tmp/1059-latin1.txt" character set latin1;

>> hd 1059-latin1.txt 

00000000  80 0a                                             |..|
00000002

>> hd 1059-utf8.txt 

00000000  e2 82 ac 0a                                       |....|
00000004

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.

Test: Tabel met gemengde codering

character set-optie bij 'into outfile'

De optie character set in into outfile lijkt prima te werken voor exports met gemengde kolommen:

# Create mixed table
###############################################
#
drop table if exists tmp;
create table tmp
(
	column_a text character set latin1,
    column_b text character set utf8
);
insert into tmp values("€","€");

# Write to file
###############################################
#
select * from tmp into outfile "/tmp/1111.txt";
select * from tmp into outfile "/tmp/1111-utf8.txt" 	character set utf8;
select * from tmp into outfile "/tmp/1111-latin1.txt" 	character set latin1;

Resultaten:

>> hd 1111.txt 
00000000  80 09 e2 82 ac 0a                                 |......|
00000006


>> hd 1110-latin1.txt 
00000000  80 09 80 0a                                       |....|
00000004

>> hd 1110-utf8.txt 
00000000  e2 82 ac 09 e2 82 ac 0a                           |........|
00000008

Opmerkingen:

09 is de hexadecimale ASCII-code voor TAB
Het eerste bestand bevat daadwerkelijk gemixte tekensets. Sublime kwam daar dan ook niet goed uit: Het koos blijkbaar voor Latin1, en het UTF-8-teken werd weergegeven als â‚¬, - de Latin1-tekens die horen bij e2 82 ac!
De andere twee bestanden werden wel goed weergegeven in Sublime.

Converteer de tabel

Dit werkt:

# Create mixed table
###############################################
#
drop table if exists tmp;
create table tmp
(
   column_a text character set latin1,
   column_b text character set utf8
);
insert into tmp values("€","€");

# Convert table + write
###############################################
#
select * from tmp into outfile "/tmp/1113.txt";

alter table tmp convert to character set latin1;
select * from tmp into outfile "/tmp/1113-latin1.txt"

alter table tmp convert to character set utf8;
select * from tmp into outfile "/tmp/1113-utf8.txt"