INTO OUTFILE (MySQL)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

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