INTO OUTFILE (MySQL): verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
Regel 67: Regel 67:
 
De truuk is, dat je zelf kunt bepalen wat die escape-prefix is. Als je hiervoor een lege string specificeert, is het probleem opgelost.
 
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. Tot op heden (lente 2020) lijkt find-and-replace de enige oplossing te zijn.
+
Helaas lijkt dit niet te werken voor NULL-waardes. Zie daarvoor het script hieronder.
  
−
=== Casus: Mei 2020 ===
+
=== Voorbeeld: Mei 2020 ===
  
 
Een exportbestand bevatte backslashes voor apostrophes.  
 
Een exportbestand bevatte backslashes voor apostrophes.  
Regel 135: Regel 135:
 
lines terminated by
 
lines terminated by
 
     '\n';
 
     '\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 ""
 +
######################################################################################################################################
 +
#
 +
# * Beautifully simple code :)
 +
# * Where-clause should include db-name. Otherwise columns from other databases will be included!
 +
#
 +
#
 +
######################################################################################################################################
 +
# 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>
  

Versie van 23 mei 2020 17:01

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!
#
#
######################################################################################################################################
# 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

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=INTO_OUTFILE_(MySQL)&oldid=21706"