Idag upptäckte jag en finurlig funktion som MySQL har, nämligen den att man kan skriva data till en tabell från en fil, och snabbt går det dessutom.

Om du har eller har haft behovet av att kunna skriva data via batch insert, dvs. skriva flera rader data via en sql insert så kanske du har upptäckt att vanlig insert har begränsningar i storlek samt att det tar en hel del tid.

Test tabell

Jag tänkte visa på skillanden mellan "vanlig" batch insert och MySQL Load Data Infile. Vi börjar med att skapa en test tabell som vi kan fylla med data.


CREATE TABLE test_data
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, test_field TEXT NOT NULL);


Vanlig batch-skrivning

Vanlig batch-skrivning är ekel men har t.ex. begränsningar i antalet parametrar som kan skrivas i en och samma insert. Vid små mängder data kan batch-skrivning dock vara bra, och det fungerar så här


INSERT INTO test_data (test_field) VALUES("en text"),("en text till"),("mer text")


Stora mängder data?

Om du, liksom vi på jobbet, har stora mängder data som måste skrivas till databasen på en och samma gång så bör du istället titta på MySQL Load Data Infile.

Det som måste göras när du vill använda load data infile är att skapa en fil med data där varje rad motsvarar en rad i den tabell du vill skriva data.

data.txt


"\N","en text åäö"
"\N","en text åäö, med komma"
"\N","en text åäö med \" citat"
"\N","en text åäö med \", citat och komma"


"\N" skriver jag för att auto_increment ska fungera. Du kan skriva "NULL" istället för "\N".

Här är SQL-frågan som du kör för att skriva den data i filen "data.txt" till tabellen test_data.


LOAD DATA INFILE 'absolut/sökväg/till/data.txt' INTO TABLE test_data
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';


Du kommer nog inte att märka någon skillnad i hastigheten om du bara skriver ett fåtal rader till databasen, skillnaden blir dock markant när du börjar närma dig 4-5000 rader.

Load Data Infile, problem med UTF-8?

Har du problem med UTF-8 när du kör databasfrågorna kan det vara så att php-filen, data.txt, databasen, tabellen eller kolumnen i tabellen inte är UTF-8.

Om du vill fixa problemet utan att bry dig om detta kan du använda den här funktionen som jag hittade hos sempersolutions.


function fixEncoding($input) {
$cur_encoding = mb_detect_encoding($input);
if($cur_encoding == "UTF-8" && mb_check_encoding($input, "UTF-8"))
return $input;
else
return utf8_encode($input);
}


Lycka till med kodandet!