INTO OUTFILE (MySQL): verschil tussen versies
Regel 11: | Regel 11: | ||
product_fba_202004 | product_fba_202004 | ||
order by | order by | ||
− | + | sku | |
− | |||
− | |||
into outfile | into outfile | ||
'/tmp/product_fba_202004.tsv' | '/tmp/product_fba_202004.tsv' | ||
Regel 20: | Regel 18: | ||
fields terminated by | fields terminated by | ||
'\t' | '\t' | ||
+ | escaped by | ||
+ | '' | ||
optionally enclosed by | optionally enclosed by | ||
'"' | '"' |
Huidige versie van 19 jan 2021 om 16:28
De gebruikelijke manier om vanuit MySQL records naar een bestand te schrijven, gaat met into outfile
.
Voorbeelden
April-mei 2020:
select * from product_fba_202004 order by sku into outfile '/tmp/product_fba_202004.tsv' character set utf8 fields terminated by '\t' escaped by '' optionally enclosed by '"' lines terminated by '\n';
Oudere 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';
Bestanden kunnen niet overschreven worden
Je kunt een bestaand bestand niet overschrijven. Oplossing: DDL gebruiken en een timestamp aan de bestandsnaam toevoegen.
Bestandsnaam mag geen variabele zijn
Helaas: De bestandsnaam mag geen variabele zijn. Ook niet in een sproc. Dit werkt dus niet:
##################################################################################################################### # Set file name ##################################################################################################################### # set @file_name = concat ( "/tmp/adgroup-", DATE_FORMAT( NOW(),'%Y%m%d-%H%i'), ".tsv" ); ##################################################################################################################### # Write to file ##################################################################################################################### # select * from adgroup order by sort_order, campaign_name, adgroup_name into outfile @file_name character set utf8 fields terminated by '\t' escaped by '' lines terminated by '\n';
Ook mag je een input-variabele niet gebruiken als bestandsnaam.
Oplossing: DDL gebruiken.
/var ipv. /tmp
Schrijven naar /tmp
gaf ooit vage klachten. Toen schrijf ik naar /var
. Naar
/var/mysql-export
om precies te zijn. Permissies op die map zijn __7, zodat het MySQL-proces kan schrijven.
In recente jaren gebruik ik gewoon weer /tmp.
Schrijven naar willekeure locaties
Zie Can't create/write to file (MySQL).
Escape-codes verwijderen
Als MySQL schrijft naar een bestand, worden control characters (bv. null of newline) of andere bijzondere karakters (bv. apostrophe!) voorzien van een escape-prefix (typisch "\"). Da's niet altijd handig.
De truuk is, dat je zelf kunt bepalen wat die escape-prefix is. Als je hiervoor een lege string specificeert, is het probleem opgelost.
Helaas lijkt dit niet te werken voor NULL-waardes. Zie daarvoor het script hieronder.
Voorbeeld: Mei 2020
Een exportbestand bevatte backslashes voor apostrophes.
Initiële oplossing
select campaign_name, adgroup_name_20_gads_approved as adgroup_name, adgroup_status, currency_code, max_cpc, adgroup_type from adgroup order by sort_order, campaign_name, adgroup_name # Je kunt sorteren op de alias! into outfile '/tmp/adgroup.tsv' character set utf8 fields terminated by '\t' optionally enclosed by '"' escaped by '' # ← tjakka! lines terminated by '\n';
Er ontstond een nieuw probleem: Sommige velden bevatte dubbele aanhalingstekens, bv. Vine de "Ensor" 1914. Deze moesten ge-escaped worden, omdat '"' ook het veld is voor optionally enclosed. Echter: Dat optionally enclosed boeide niet, want het is allemaal tekst.
Definitieve versie
Dit werkt als een tierelier:
select campaign_name, adgroup_name_30_applied as adgroup_name, adgroup_status, currency_code, max_cpc, adgroup_type from adgroup order by sort_order, campaign_name, adgroup_name into outfile '/tmp/adgroup.tsv' character set utf8 fields terminated by '\t' # optionally enclosed by # Niet! # '"' escaped by '' lines terminated by '\n';
Script om NULL-waardes te vervangen
Best simpel, en werkt super. In de loop der jaren broed ik vaak op alternatieve namen, maar tot op heden (lente 2020) houd ik het op export_fix_null
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_fix_null`(in tblname tinytext ) BEGIN # # Replace each NULL values in the given table with "" - Last updated: May 2020 ###################################################################################################################################### # # * Beautifully simple code :) # * Where-clause should include db-name. Otherwise columns from other databases will be included! # * Tinytext: Up to 255 characters - That's plenty # # ###################################################################################################################################### # DDL ###################################################################################################################################### # set @string=concat ( "Update ",tblname," set ", ( select group_concat(column_name,"=ifnull(",column_name,",'')") from information_schema.columns where table_name=tblname and table_schema=database() ), ";" ); ###################################################################################################################################### # Check ###################################################################################################################################### # # select @string; ###################################################################################################################################### # Execute ###################################################################################################################################### # prepare ps1 from @string; execute ps1; drop prepare ps1; END
Casus: Tabs in exportbestand (jan. 2021)
Dit betreft een export inclusief een 'description'-veld waarin allerlei tekens worden gebruikt, waaronder tab. Dit lijkt te werken:
select * from product_kbo3_202012 order by sku into outfile '/tmp/product_kbo3_202012.tsv' character set utf8 fields terminated by '\t' # De truuk: "enclosed" ipv. "optionally enclosed" ################################################# # enclosed by '"' # Geen escape-character ################################################# # # Ik weet niet zeker of dit hier een rol speelt, om bv. "\" voor " te voorkomen # escaped by '' lines terminated by '\n';
Zie ook
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
- https://stackoverflow.com/questions/9140879/mysql-select-into-outfile-export-options
- https://stackoverflow.com/questions/13548118/can-i-use-a-variable-to-specify-outfile-in-mysql