Chargement d'un fichier directement dans la base MySQL

retour

La commande LOAD DATA INFILE lit des lignes à partir d'un fichier, et le transforme en table, à très grande vitesse. Si l'option LOCAL est précisée, le fichier est lu depuis le client (cette fonction est disponible à partir la version 3.22.6 de MySQL.)

LOAD DATA [LOCAL] INFILE 'Nom_fichier.txt' [REPLACE | IGNORE]
    INTO TABLE Nom_table
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(Nom_col,...)]

Pour des raisons de sécurité, lors de la lecture de fichier situé sur le serveur, les fichiers doivent être disponibles dans le dossier de MySQL, ou bien lisible par tous. De plus, pour utiliser LOAD DATA INFILE sur des fichiers serveurs, il faut avoir les droits fichiers.

Utiliser l'option LOCAL est un peu plus lente que l'option par défaut, car le contenu des fichiers doit du client vers le serveur. d'un autre coté, il n'y plus de problème de droits d'accès.

L'utilitaire mysqlimport assure aussi l'importation de fichier. Il le fait en envoyant une requête LOAD DATA INFILE au serveur. L'option -local force mysqlimport à lire le fichier depuis l'hôte client. Si le client et le serveur supporte le protocole compressé, l'option --compress donnera de meilleures performances sur des réseaux chargés.

Pour retrouver les fichiers sur le serveur, le serveur utilise les règles suivantes :

Il faut noter que ces règles signifie que le fichier `./monFichier.txt' sera lu depuis le dossier données du serveur, tandis que `myfile.txt' sera lu depuis le dossier de la base de données courante. Il faut aussi noter qu'avec la commande ci-dessous, le fichier est lu depuis le dossier de la base db1, et non pas db2 :

mysql> USE db1;
mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table; 

Les options REPLACE et IGNORE règlent la gestion index redondants, dans les colonnes de type unique. Avec REPLACE, la nouvelle ligne remplacera l'ancienne, avec la même valeur d'index. Avec IGNORE, la nouvelle ligne sera ignorée. Si rien n'est précisé, une erreur surviendra lors de la tentative d'insertion du doublon, et le reste du fichier sera ignoré.

Lors du chargement de lignes à partir d'un fichier local et avec l'option LOCAL, le serveur n'a aucun moyen d'interrompre la transmission du fichier durant l'opération, alors le comportement par défaut est IGNORE.

DATA INFILE est la commande complémentaire de SELECT ... INTO OUTFILE. Voir la section 7.11 SELECT. Pour écrire des lignes depuis une base de données vers un fichier, il faut utiliser SELECT ... INTO OUTFILE. Pour lire des lignes depuis un fichier vers une base, il faut utiliser LOAD DATA INFILE. La syntaxe des clauses FIELDS et LINES est la même pour les deux commandes. Ces deux clauses sont optionnelles, mais FIELDS doit impérativement précéder LINES si les deux sont présents.

Si la clause est FIELDS présente, alors chacune des sous clauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY t ESCAPED BY) sont optionnelles, mais il faut au moins en spécifier une.

Si la clause FIELDS n'est pas spécifiée, les valeurs par défaut sont les suivantes :

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Si la clause LINES n'est pas spécifiée, les valeurs par défaut sont les suivantes :

LINES TERMINATED BY '\n'

En bref, les options par défaut de LOAD DATA INFILE fonctionnent comme suit :

Dans le sens inverse, les options par défaut de SELECT ... INTO OUTFILE fonctionnent comme suit :

Il faut noter que pour écrire write FIELDS ESCAPED BY '\\', il faut écrire 2 backslash pour en avoir un de lu.

L'option IGNORE number LINES permet d'ignorer les premières lignes, qui contiendrait un entête, par exemple.

mysql> LOAD DATA INFILE "/tmp/Nom_fichier" into table test IGNORE 1 LINES;

Pour pouvoir écrire un fichier avec SELECT ... INTO OUTFILE, puis le relire LOAD DATA INFILE ultérieurement avec, il est impératif que les options de lecture et d'écriture soient les mêmes. Sinon, l'interprétation du fichier à la relecture sera erronée. Par exemple, un fichier est écrit avec SELECT ... INTO OUTFILE avec des virgules comme délimiteur de champs :

mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt'
           FIELDS TERMINATED BY ','
           FROM ...

Pour relire ce fichier, la bonne commande est :

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY ',';

Si, au contraire, le fichier est relu avec la commande ci-dessous, la relecture sera erronée, car les délimiteurs attendus sont des tabulations :

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY '\t';

Il est probable que le fichier soit interprété comme un seul champs.

LOAD DATA INFILE peut aussi lire des fichiers issues d'autres sources. Par exemple, un fichier au format dBase a des champs séparés par des virgules, et insérés dans des doubles guillemets. Si les lignes dans le fichier sont terminées par des nouvelles lignes, la commande suivante permettra d'acquérir un fichier au format.

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE Nom_table
           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
           LINES TERMINATED BY '\n';

Les options FIELDS ou LINES peuvent être des chaînes vides. Si ils ne sont pas vides, les options FIELDS [OPTIONALLY] ENCLOSED BY et FIELDS ESCAPED BY doivent être une chaîne d'un seul caractère. Les options FIELDS TERMINATED BY et LINES TERMINATED BY peuvent avoir un ou plusieurs caractères. Par exemple, si les lignes sont terminées par la paire retour-chariot/nouvelle-ligne, il est possible d'utiliser l'option LINES TERMINATED BY '\r\n'.

Contrôle les caractères qui entoure les champs. Lors de l'exportation(SELECT ... INTO OUTFILE), l'absence de l'option OPTIONALLY force tous les champs à être entouré par le caractère ENCLOSED BY. Par exemple, en utilisant la virgule comme délimiteur de champs :

"1","une chaine","100.20"
"2"," une chaine contenant une , virgule","102.20"
"3"," une chaine contenant un \" guillemet","102.20"
"4"," une chaine contenant un \", guillemet et une virgule","102.20"

L'option OPTIONALLY force l'utilisation du caractère ENCLOSED BY seulement pour les champs de type CHAR et VARCHAR.

1," une chaine ",100.20
2," une chaine contenant une , virgule ",102.20
3," une chaine contenant un \" guillemet ",102.20
4," une chaine contenant un \", guillemet et une virgule ",102.20

On peut noter que les occurrences du caractère ENCLOSED BY situés dans une chaîne sont toujours échappée, grâce au caractère ENCLOSED BY. On peut aussi noter que si le caractère d'échappement est une chaîne vide, le fichier ne pourra pas être relu correctement par LOAD DATA INFILE. Par exemple, le fichier de sortie ci-dessus, va devenir le fichier de sortie ci-dessous, si le caractère d'éhappement est vide. Lors de la relecture, un problème surviendra surement durant la deuxième ligne :

1," une chaine ",100.20
2," une chaine contenant une , virgule ",102.20
3," une chaine contenant un " guillemet ",102.20
4," une chaine contenant un \", guillemet et une virgule ",102.20

En entrée, le caractère est éliminé à la fin de chaque champs. (ceci est vrai, qu'il y ai l'option OPTIONALLY ou pas. OPTIONALLY n'a pas d'impact sur la procédure d'acquisition). Les occurrences du caractère ENCLOSED BY précédés du caractère d'échappement ESCAPED BY sont considérés comme une partie du champs. De plus, les caractères ENCLOSED BY doublés sont considérés comme une seule occurrence. Par exemple, le caractère ENCLOSED BY est '"' alors, les lignes suivantes deviennent :

"Le ""GRAND"" chef"  -> Le "GRAND" chef
Le "GRAND" chef -> Le "GRAND" chef
Le ""GRAND"" chef -> Le ""GRAND"" chef

FIELDS ESCAPED BY contrôle l'écriture et la lecture des caractères spéciaux . Si l'option FIELDS ESCAPED BY n'est pas une chaîne vide, il sert de préfixe dans les cas suivants 

Si le caractère FIELDS ESCAPED BY est une chaîne vide, aucun caractère ne sera échappé. Ce n'est pas une très bonne idée, surtout si certains champs contiennent l'un des caractères de la liste ci-dessus.

En lecture, si le caractère FIELDS ESCAPED BY n'est pas une chaîne vide, les occurrences de ce caractère seront éliminées, et le caractère suivant sera lu littéralement, comme une partie du champs. Exceptions faites de `0' et `N' (i.eI., \0 ou \N avec `\' comme caractère d'échappement). Ces séquences seront interprétées comme ASCII 0 (le caractère nul) et NULL. Voir ci dessous pour les règles de sauvegarde NULL .

Dans certains cas, les options FIELDS et LINES de interagissent :

La gestion de la valeur NULL dépends des options FIELDS et LINES utilisées :

Certains cas ne sont pas accepté par LOAD DATA INFILE:

 
FIELDS TERMINATED BY '"' ENCLOSED BY '"'

Les exemples suivants charge toutes les colonnes de la tables persondata:

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Aucun champs n'est précisé, alors LOAD DATA INFILE s'attend à trouver une valeur pour chaque colonne. Les valeurs par défaut FIELDS et LINES sont supposés.

Pour ne charger qu'une partie des colonnes, on peut utiliser :

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

Il faut aussi préciser la liste des champs dans l'ordre d'apparition de celles ci dans le fichier, surtout si elles apparaissent dans un autre ordre que celui de la table.

Si il manque des champs, les colonnes qui n'ont pas de valeurs seront mises à leur valeur par défaut.

La valeur d'un champs vide est interprété différemment, suivant le champs manquant :

Les colonnes de type TIMESTAMP prennent la valeur de l'heure et la date courante si une NULL leur est affectée. ou (pour la première colonne de type TIMESTAMP) si la colonne est omise de la liste de champs à lire.

Si une ligne à trop de champs, les champs supplémentaires sont ignorés, et le nombre d'alerte est augmenté.

LOAD DATA INFILE considère toutes les valeurs en entrées comme des chaînes, donc il n'est pas possible d'utiliser les formes numériques, notamment pour les types ENUM ou SET . . Toutes les énumérations doivent être spécifiée comme des chaînes

Lors de l'utilisation de LOAD DATA INFILE, la fonction mysql_info() permet d'obtenir des informations sur la requête. Le format de la réponse est comme suite :

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indique le nombre de lignes qui n'a pas pus être insérées car elles tentaient de doubler une clé primaire.. Warnings indique le nombre d'insertions qui ont générer une erreur lors de l'insertion. Warnings surviennent lorsqu'il y a une tentative incorrecte d'insertion de ligne.

Liens utiles

http://www.onlyphp.com/index.php?url=201&menu=2

http://www.phpscripts-fr.net/?page=articles&cat=PHP

Cette technique est utilisée pour remplir la table des cartes de contacts de la page d'exemple, juste après qu'elle ait été crée la première fois : http://www.tiprof.fr/PHP-mySQL/mat%C3%A9riel/form-cartes/form.php?faire=tout+voir