INTO OUTFILE (MySQL): verschil tussen versies
(22 tussenliggende versies door dezelfde gebruiker niet weergegeven) | |||
Regel 2: | Regel 2: | ||
== Voorbeelden == | == Voorbeelden == | ||
+ | |||
+ | April-mei 2020: | ||
+ | |||
+ | <pre> | ||
+ | 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'; | ||
+ | </pre> | ||
+ | |||
+ | Oudere voorbeelden: | ||
select * from tabel_export into outfile '/tmp/tabel_export.tsv'; | select * from tabel_export into outfile '/tmp/tabel_export.tsv'; | ||
Regel 21: | Regel 46: | ||
Je kunt een bestaand bestand ''niet'' overschrijven. Oplossing: DDL gebruiken en een timestamp aan de bestandsnaam toevoegen. | 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: | ||
+ | |||
+ | <pre> | ||
+ | ##################################################################################################################### | ||
+ | # 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'; | ||
+ | </pre> | ||
+ | |||
+ | Ook mag je een input-variabele niet gebruiken als bestandsnaam. | ||
+ | |||
+ | Oplossing: DDL gebruiken. | ||
== /var ipv. /tmp == | == /var ipv. /tmp == | ||
Regel 32: | Regel 103: | ||
In recente jaren gebruik ik gewoon weer /tmp. | In recente jaren gebruik ik gewoon weer /tmp. | ||
− | == Escape-codes == | + | == 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 ''' | ||
+ | |||
+ | <pre> | ||
+ | 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'; | ||
+ | </pre> | ||
+ | |||
+ | 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: | |
<pre> | <pre> | ||
− | set @ | + | 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'; | ||
+ | </pre> | ||
+ | |||
+ | === 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'' | ||
+ | |||
+ | <pre> | ||
+ | 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 | ||
</pre> | </pre> | ||
− | Dit | + | == 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: | ||
+ | |||
+ | <pre> | ||
+ | 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'; | ||
+ | </pre> | ||
== Zie ook == | == Zie ook == | ||
+ | * [[AppArmor]] | ||
+ | * [[Can't create/write to file (MySQL)]] | ||
* [[INTO OUTFILE & tekencodering (MySQL)]] | * [[INTO OUTFILE & tekencodering (MySQL)]] | ||
Regel 65: | Regel 282: | ||
* http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_filesystem | * 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/9140879/mysql-select-into-outfile-export-options | ||
+ | * https://stackoverflow.com/questions/13548118/can-i-use-a-variable-to-specify-outfile-in-mysql |
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