MySQL Server Blog
Ik wil graag een minder bekend MySQL SQL commando onder de aandacht brengen, analyseren hoe het werkt en een aantal van de beslissingen die genomen moeten worden bij het gebruik ervan begrijpen.
Laten we beginnen met de basis: De Reference Manual.
Hier staat inderdaad veel in over LOAD DATA. Maar we zullen ons concentreren op LOAD DATA INFILE. In een notendop heeft het deze syntaxis:
LOAD DATA INFILE 'file_name' INTO TABLE tbl_name ...)] ENCLOSED BY 'char'] ] ] ...)] ...]
OK, dus dit is een commando om de gegevens in een tekstbestand in een tabel te laden.
Laten we het eens proberen.
Laten we de volgende tabeldefinitie gebruiken:CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT);
En het volgende gegevensbestand (data.txt):
1,12,23,34,4
Maar … Hmm.
Dit is dus een SQL-opdracht die op een servercomputer wordt uitgevoerd door een clientcomputer. Ik weet hoe ik de tabel moet maken. Maar waar moet ik mijn gegevensbestand plaatsen?
terug naar de referentiehandleiding:
Als LOCAL niet is opgegeven, moet het bestand zich op de serverhost bevinden en wordt het direct door de server gelezen.
OK, dus ik moet data.txt op mijn server zetten. Laten we het eens proberen:
mysql> create table t1 (a INT PRIMARY KEY, b INT, c INT);Query OK, 0 rows affected (0.05 sec)
mysql> select @@datadir;+--------------------------------------------+| @@datadir |+--------------------------------------------+| /home/gkodinov/work/mysql-8.0/bld/datadir/ |+--------------------------------------------+1 row in set (0.00 sec)
mysql> exit;Bye
~/work/mysql-8.0/bld/mysql-test: mkdir /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/~/work/mysql-8.0/bld/mysql-test: cat > /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/data.txt1,12,23,34,4~/work/mysql-8.0/bld/mysql-test: mysql -u rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.16-debug Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> load data infile 'data/data.txt' into table t1 fields terminated by ',';ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Hmm !
mysql> select @@secure_file_priv;+--------------------+| @@secure_file_priv |+--------------------+| NULL |+--------------------+1 row in set (0.00 sec)
Deze NULL-waarde (wat betekent “geen bestandstoegang toestaan”) voorkomt dat de server toegang heeft tot een lokaal bestand in het bestandssysteem. OK. Dat lijkt een redelijke standaardwaarde.
Nu, om de actie voort te zetten, laten we het mogelijk maken om toegang te krijgen tot bestanden in de data-subdirectory van de datadirectory door het volgende toe te voegen:
--secure-file-priv=/home/gkodinov/work/mysql-8.0/bld/datadir/textdata
aan mysqld.
Tweede poging:
mysql> select @@datadir,@@secure_file_priv\G*************************** 1. row ***************************@@datadir: /home/gkodinov/work/mysql-8.0/bld/datadir/@@secure_file_priv: /export/home/tmp/gkodinov/work/mysql-8.0/bld/datadir/textdata/1 row in set (0.00 sec)
mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',';ERROR 1261 (01000): Row 1 doesn't contain data for all columns
Hmm, het lijkt erop dat we voorbij het toegangshek zijn. Maar, zoals de server correct opmerkte, hebben we 3 kolommen in de tabel en slechts 2 kolommen voor elke rij in data.txt. Laten we dat ook oplossen:
mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);Query OK, 4 rows affected (0.01 sec)Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t1;+---+------+------+| a | b | c |+---+------+------+| 1 | 1 | NULL || 2 | 2 | NULL || 3 | 3 | NULL || 4 | 4 | NULL |+---+------+------+4 rows in set (0.00 sec)
Perfect !
Zoals u kunt zien werkt LOAD DATA INFILE ongeveer hetzelfde als het importeren van tekstgegevens in een MS Excel-spreadsheet. En het heeft een redelijke standaardconfiguratie om te voorkomen dat mensen willekeurige gegevens op het bestandssysteem van de server lezen. Je hoeft alleen maar je gegevens in een geschikte directory van de server computer te hebben en je kunt ze importeren.
De voordelen van deze extra moeite zijn vooral dat je maar een enkel INSERT statement hoeft te parsen en het dan gewoon zo vaak hoeft uit te voeren als er rijen zijn.
Zo ver gaat het goed. Maar … hmmm … heb je je gegevens op de server computer ? Zodat je ze kunt importeren en weer opslaan in de data directory op dezelfde server computer ?
Dit zou kunnen werken voor kleine bestanden. Maar als je heel veel gegevens hebt, dan is het niet eens mogelijk om twee verschillende kopieën van die gegevens op dezelfde computer te hebben, omdat de ruimte beperkt is.
Maar, zoals je je van eerder herinnert, zijn er twee computers betrokken bij het uitvoeren van een SQL commando: de server computer en de client computer. Wat als we de bestandsopslagruimte van de laatste kunnen gebruiken en de gegevens op de een of andere manier naar de eerste kunnen streamen via de client/server-verbinding (samen met het SQL-commando zelf)?
Dit is precies wat de optionele LOCAL-modifier doet !
Maar hé, dit zou een netwerkprotocoluitbreiding vereisen: je hebt een heleboel extra gegevens te verzenden. Laten we eens kijken hoe dit eruit ziet op de draad. We zouden natuurlijk wireshark of een andere netwerk data analyse tool kunnen gebruiken om dat te doen. Maar gelukkig hoeven we dat niet te doen.
De mysql client/server protocol draadformaten zijn gedocumenteerd in de Doxygen documentatie van de server. Deze bevat het volgende schema:
In principe werkt het dus als volgt:
- De client stuurt de COM_QUERY om het SQL commando uit te voeren, zoals hij gewoonlijk doet.
- In ons geval is dit “LOAD DATA LOCAL INFILE datafile/data.txt”
- De server stuurt (in plaats van een resultatenset of een status) een speciaal verzoek (0xFB) samen met de bestandsnaam van het bestand dat de client moet lezen en verzenden
- In ons geval is dit “datafile/data.txt”
- De client begint met het streamen van de inhoud van het bestand dat door de server wordt opgevraagd, gevolgd door een leeg pakket (lengte nul) om het einde van het bestand aan te geven
- De server voert de opdracht uit en gebruikt de verzonden gegevens en gaat verder zoals hij normaal met elke andere DML-opdracht zou doen en stuurt een OK-pakket (of een ERR-pakket) om het resultaat van de uitvoering aan te geven.
Goed ! Maar … wacht …
Waarom moet de server een bestandsnaam naar de client sturen om te lezen? Heeft hij niet gewoon een SQL commando gegeven met diezelfde bestandsnaam erin?
Ja, dat heeft hij gedaan. Maar helaas (of niet) is SQL niet de gemakkelijkste taal om te ontleden. Om de bestandsnaam te weten moet de client lokaal een volwaardige parser hebben voor het MySQL SQL dialect die *in sync* is met de betreffende parser op de server. Lastig !
Daarom probeert de client gebruik te maken van de parsing die toch al door de server wordt gedaan en verwacht dat deze het ontvangen statement parseert en datgene levert wat de client nodig heeft: de bestandsnaam om te lezen.
OK, laten we ons gegevensbestand naar de clientcomputer verplaatsen en dit hele ding eens proberen:
mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);ERROR 1148 (42000): The used command is not allowed with this MySQL version
Wat?
Deze niet erg vriendelijke foutmelding zegt dat LOAD DATA LOCAL INFILE is uitgeschakeld. Maar waarom?
RefMan to the rescue again (over -local-infile):
Deze variabele regelt de LOCAL-mogelijkheid aan de serverzijde voor LOAD DATA-statements. Afhankelijk van de instelling local_infile, weigert de server of staat hij het laden van lokale gegevens toe door clients die LOCAL aan de client-zijde hebben ingeschakeld.
En de standaardinstelling van deze variabele is UIT sinds 8.0.2.
OK, laten we dit inschakelen:
mysql> select @@local_infile;
+----------------+| @@local_infile |+----------------+| 1 |+----------------+1 row in set (0.00 sec)
mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);ERROR 1148 (42000): The used command is not allowed with this MySQL version
Hmm, werkt het nog steeds niet ?! Wat is er mis?!
Herken je de bovenstaande uitleg over netwerkuitwisseling nog? De client ontvangt van de server een naam van een bestand om van te lezen. Dus de client moet zijn eigen beslissing nemen of hij de server en de bestandsnaam die hij hem stuurt wil vertrouwen.
Deze situatie wordt heel goed uitgelegd in deze Referentie handleiding pagina. Het vermeldt ook dat de client moet aangeven dat hij de server en de verbinding ermee vertrouwt door een extra optie (of mysql_options() optie) toe te voegen: -local-infile.
Laten we dat eens proberen:
~/work/mysql-8.0/bld/datadir: ../runtime_output_directory/mysql --local-infile=1 <your options here>Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.16-debug Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);Query OK, 4 rows affected (0.00 sec)Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
Mooi ! Eindelijk!
Ok, laten we eens samenvatten wat er nodig is om LOAD DATA LOCAL INFILE werkend te krijgen:
- Je moet de server vertellen om het voor zijn clients te gebruiken door -local-infile op de server op te geven.
- Je moet je mysql client instrueren om het voor alle SQL statements te gebruiken. Of beheer het op een meer fijnkorrelige basis (bijv. per verbinding) als je de C API gebruikt.
- En natuurlijk moet je het juiste bestand hebben en de juiste importopties opgeven.
Ik weet dat het een hele opgave is en een ernstige beperking van de bruikbaarheid. Maar het is zeker de moeite waard als je bedenkt wat voor magie het is om een stel tekstgegevens over te zetten en ze te importeren in een relationele tabel op de server. Alles met een enkel commando.
Dank je voor het gebruik van MySQL !