7.15 LOAD DATA INFILE

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

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.)

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 :

• Si un chemin absolu est fourni, le serveur utilise le chemin tel quel.
• Si un chemin relatif est fourni, avec un ou plusieurs composant, le serveur recherche le fichier dans le dossier données de la base de données.
• Si un nom de fichier est fourni, le serveur va le rechercher dans le dossier de la base de données courante.

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. 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 :

• Recherche de la limite de ligne
• Segmenter la ligne en champs, grce aux tabulations
• Ne pas s'attendre à ce que les champs soient entourés de guillemets
• Interpréter les occurrences de tabulation, nouvelle ligne, ou backslash ``\'' précédés par ``\'' comme des caractères à part entières

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

• Ecrire des tabulations entre les champs
• Ne pas entourer les champs avec des guillemets
• Ajouter un backslash ``\'' avant toutes les occurrences de tabulation, nouvelle ligne, ou backslash ``\'.'
• Ecrire une nouvelle ligne à chaque fin de ligne.

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 chaîne","100.20"
"2"," une chaîne contenant une , virgule","102.20"
"3"," une chaîne contenant un \" guillemet","102.20"
"4"," une chaîne 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 chaîne ",100.20
2," une chaîne contenant une , virgule ",102.20
3," une chaîne contenant un \" guillemet ",102.20
4," une chaîne 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'échappement est vide. Lors de la relecture, un problème surviendra surement durant la deuxième ligne :

1," une chaîne ",100.20
2," une chaîne contenant une , virgule ",102.20
3," une chaîne contenant un " guillemet ",102.20
4," une chaîne 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

• Pour le caractère FIELDS ESCAPED BY
• Pour le caractère FIELDS [OPTIONALLY] ENCLOSED BY
• Pour le premier caractère FIELDS TERMINATED BY et LINES TERMINATED BY
• Pour le caractère ASCII 0 (qui sera en fait écrit avec la séquence caractère d'échappement suivi de 0 )

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 .

Pour plus d'informations à propos des séquences d'échappement, Literals.

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

• Si LINES TERMINATED BY est une chaîne vide, et FIELDS TERMINATED BY est une chaîne non vide, les lignes seront aussi terminées FIELDS TERMINATED BY.
• Si FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont tous les deux des chaînes vides (''), un format à longueur fixe est utilisé. Avec le format à longueur fixe, il n'y a plus besoin de délimiteurs. A la place, les colonnes et les lignes sont écrites en utilisant la taille d'affichage des colonnes. Par exemple, si une colonne est déclarée de type INT(7), les valeurs seront écrite en utilisant des colonne de 7 caractères. En lecture, les données sont obtenues en lisant 7 caractères. Les fichiers sans délimiteurs ont un impact sur la façon avec laquelle la valeur NULL est enregistrée.

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

• Par pour les valeurs par défaut de FIELDS and LINES, NULL est symbolisé par as \N en lecture et en écriture. (en supposant que le caractère ESCAPED BY est ``\'').
• Si FIELDS ENCLOSED BY n'est pas vide, un champs contenant littéralement la chaîne NULL est utilisé, en écriture et en lecture. (Ceci est différent du mot 'NULL', entouré de caractères FIELDS ENCLOSED BY et lu comme la chaîne 'NULL').
• Si FIELDS ESCAPED BY est vide, NULL est directement écrit NULL.
• Avec le format à largueur de colonne fixée, (ce qui arrive lorsque FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont tous les deux vides), NULL s'écrit comme une chaîne vide. Il faut noter que cela entraîne l'identité de la valeur NULL et des chaînes vides. Il ne sera alors pas possible d'en faire la différence.

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

• Lignes de taille fixe (FIELDS TERMINATED BY et FIELDS ENCLOSED BY tous les deux vides) et des colonnes de type BLOB ou TEXT.
• Si deux séparateurs sont identiques, LOAD DATA INFILE ne seront pas capable d'interpréter le fichier. Par exemple, le champs FIELDS va poser problème:

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

• Si FIELDS ESCAPED BY est vide, un champs qui contient une occurrence de FIELDS ENCLOSED BY ou LINES TERMINATED BY suivi FIELDS TERMINATED BY va provoquer une erreur, et l'arret de la lecture du fichier. Ceci, car LOAD DATA INFILE ne peut pas determiner correctement la fin de la valeur, ou du champs.

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. Les valeurs par défaut sont décrites dans la section CREATE TABLE.

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

• Pour les champs de type chaîne, la colonne prend la valeur de la chaîne vide.
• Pour les types numériques, la colonne prend la valeur 0.
• Pour les types date et heures, la colonne prend la valeur ``zéro'' adéquate. 7.2.6 Types date et heure.

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.