INTO OUTFILE (MySQL): verschil tussen versies
Regel 144: | Regel 144: | ||
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_fix_null`(in tblname tinytext ) | CREATE DEFINER=`root`@`localhost` PROCEDURE `export_fix_null`(in tblname tinytext ) | ||
BEGIN | BEGIN | ||
− | + | # | |
# Replace each NULL values in the given table with "" | # Replace each NULL values in the given table with "" | ||
###################################################################################################################################### | ###################################################################################################################################### | ||
Regel 150: | Regel 150: | ||
# * Beautifully simple code :) | # * Beautifully simple code :) | ||
# * Where-clause should include db-name. Otherwise columns from other databases will be included! | # * Where-clause should include db-name. Otherwise columns from other databases will be included! | ||
+ | # * Tinytext: Up to 255 characters - That's plenty | ||
# | # | ||
# | # | ||
Regel 160: | Regel 161: | ||
"Update ",tblname," set ", | "Update ",tblname," set ", | ||
( | ( | ||
− | + | select group_concat(column_name,"=ifnull(",column_name,",'')") | |
from information_schema.columns | from information_schema.columns | ||
where table_name=tblname and table_schema=database() | where table_name=tblname and table_schema=database() | ||
− | ) | + | ), |
− | + | ";" | |
− | + | ); | |
Versie van 23 mei 2020 19:04
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 sorder_order, campaign_name, adgroup_name into outfile '/tmp/product_fba_202004.tsv' character set utf8 fields terminated by '\t' 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.
/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 "" ###################################################################################################################################### # # * 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
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