6.4.9 Syntaxe de LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'nom_de_fichier.txt'     [REPLACE | IGNORE]     INTO TABLE nom_de_table     [FIELDS         [TERMINATED BY '\t']         [[OPTIONALLY] ENCLOSED BY '']         [ESCAPED BY '\\' ]     ]     [LINES TERMINATED BY '\n']     [IGNORE nombre LINES]     [(nom_de_colonne,...)]

La commande LOAD DATA INFILE lit les lignes dans un fichier texte et les insère à très grande vitesse. Si le mot clef LOCAL est spécifié, le fichier sera lu sur la machine du client. Si le mot clef LOCAL n'est pas spécifié, le fichier doit se trouver sur le serveur ( LOCAL est disponible à partir de la version 3.22.6 de MySQL).

Pour des raisons de sécurité, lorsque les fichiers sont lus sur le serveur, ils doivent se trouver dans le répertoire de la base de données courante, ou bien être lisible par tous. Pour utiliser la commande LOAD DATA INFILE sur des fichiers du serveur, vous devez avoir le droit de FILE sur le serveur. Droits fournis par MySQL .

Si vous spécifiez le mot clef LOW_PRIORITY , l'exécution de la commande LOAD DATA est ajournée jusqu'à ce qu'aucun client ne lise plus de la table.

L'utilisation de LOCAL sera un peu plus lente que laisser le serveur accéder directement au fichiers, car le contenu du fichier devra transiter du client jusqu'au serveur. D'un autre côté, vous n'aurez pas besoin du droit FILE pour charger les fichiers locaux.

Vous pouvez aussi charger des fichiers de données en utilisant l'utilitaire mysqlimport ; Il opère en envoyant la commande LOAD DATA INFILE au serveur. L'option --local fais que mysqlimport lit les fichiers de données chez le client. Vous pouvez spécifier l'option --compress pour avoir de meilleurs performances avec les connexions lentes si le client et le serveur supportent le protocole compressé.

• Si un chemin absolu est fourni, le serveur utilise le chemin tel quel.
• Si un chemin relatif est fourni, avec un ou plusieurs éléments de dossiers, le serveur recherche le fichier relativement à son dossier de données.
• Si le fichier n'a pas d'éléments de dossier, le serveur recherche les données dans le dossier de base de données courante.

mysql> USE db1;
mysql> LOAD DATA INFILE "donnees.txt" INTO TABLE db2.ma_table;

Si vous chargez un fichier sur votre machine client avec l'option LOCAL , le serveur ne peut pas interrompre la transmission du fichier au milieu de l'opération : par défaut, il utilisera l'option IGNORE .

LOAD DATA INFILE est le complémentaire de SELECT ... INTO OUTFILE . Syntaxe des SELECT . Pour écrire des données depuis une table dans un fichier, utilisez SELECT ... INTO OUTFILE . Pour lire les données dans la table, utilisez 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 précéder LINES , si les deux sont specifiées.

Si vous ne spécifiez par de clause FIELDS , les valeurs par défaut sont :

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

LINES TERMINATED BY '\n'

• Recherche des limites de lignes parmis les nouvelles lignes.
• Scinde les lignes en champs avec les tabulations.
• Ne suppose pas que les champs sont entourés de guillemets.
• Interprète les occurrences de tabulation, nouvelle ligne, `\' précédées par `\' comme des caractères littéraux qui font partie de la valeur d'un champs.

• Ecrit des tabulations entre les champs.
• N'entoure pas les champs de guillemets.
• Utilise '\' pour echapper les occurences de tabulation, nouvelle ligne, '\' trouvées dans les valeurs.
• Insère une nouvelle ligne entre les lignes.

mysql> LOAD DATA INFILE "/tmp/nom_fichier" INTO TABLE test IGNORE 1 LINES;

mysql> SELECT * INTO OUTFILE 'donnees.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM ...;

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

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

La commande LOAD DATA INFILE peut être utilisée pour lire des données issues d'autres sources. Par exemple, un fichier au format dBASE présente des champs séparés par des virgules, et entourés de guillemets doubles. Si les lignes sont terminées par de nouvelles lignes, la commande ci-dessous illustre la relecture d'un tel fichier avec MySQL :

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

Par exemple, pour charger un fichier de blagues, qui sont séparées par une ligne de %% , dans une table vous pouvez faire :

CREATE TABLE blagues (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   blague TEXT NOT NULL
   );
LOAD DATA INFILE "/tmp/blagues.txt" INTO TABLE blagues FIELDS TERMINATED BY ""
LINES TERMINATED BY "\n%%\n" (blague);

"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"

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

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

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

• La caractère FIELDS ESCAPED BY
• Le caractère FIELDS [OPTIONALLY] ENCLOSED BY
• Le premier caractère des valeurs de FIELDS TERMINATED BY et LINES TERMINATED BY
• ASCII 0 (en fait, ce qui est écrit après le caractère d'échappement est le caractère ASCII '0' , et non pas le code ASCI de zéro)

Pour plus d'informations sur la syntaxe avec les caractères d'échappement '\' , consultez Comment écrire les chaînes et les nombres ? .

• Si le caractère de LINES TERMINATED BY est une chaîne vide et que celui de FIELDS TERMINATED BY ne l'est pas, ce dernier sera celui utilisé pour LINES TERMINATED BY .
• Si les valeurs FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont vides toutes les deux ( '' ), un format à taille de champ fixe est utilisé. Avec ce format, aucun délimiteur n'est utilisé entre les champs. Au lieu de cela, les valeurs des colonnes sont écrites avec leur configuration d'affichage. Par exemple, si une colonne a été déclarée INT(7) , la valeur de cette colonne sera écrite avec 7 caractères. Lors de la relecture, la valeur de la colonne sera obtenue en lisant à nouveau 7 caractères. Ce format à taille fixe affecte la gestion de la valeur NULL ; voyez plus loin pour cela. Notez que ce format ne fonctionne pas avec les jeux de caractères multi-octets.

• Pour les valeurs par défaut de FIELDS et LINES , NULL est écrit \N et \N est lu NULL (en supposant que le caractère d'échappement est '\' ).
• Si FIELDS ENCLOSED BY n'est pas vide, un champ contenant le mot NULL comme valeur sera lu comme la valeur NULL (ce qui diffère du mot NULL , entouré du caractère FIELDS ENCLOSED BY , qui sera lu comme le mot 'NULL' ).
• Si FIELDS ESCAPED BY est vide, NULL est écrit comme le mot 'NULL' .
• Avec le format à taille fixe (ce qui arrive si FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont tous les deux vides), les valeurs NULL sont écrites sous forme de chaîne vide. Notez que cela fait que NULL et les chaînes vides seront représentées par une valeur qui ne les distingue pas l'une de l'autre. Si vous avez besoin de différencier entre les deux, n'utilisez par ce format !

• Lignes à tailles fixes ( FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont tous les deux vides) et les types de colonne BLOB ou TEXT .
• Si vous spécifiez un séparateur qui est le même qu'un autre préfixe, LOAD DATA INFILE ne sera pas capable de relire proprement le résultat. Par exemple, la clause FIELDS suivante posera sûrement des problèmes :

  FIELDS TERMINATED BY '"' ENCLOSED BY '"'

• Si FIELDS ESCAPED BY est vide, une valeur de colonne qui contient une occurence de FIELDS ENCLOSED BY ou de LINES TERMINATED BY suivi du caractère FIELDS TERMINATED BY interrompra la lecture de LOAD DATA INFILE trop tôt. Cela est dû au fait que LOAD DATA INFILE ne peut pas faire la différence entre la valeur dans le champ et la fin de la ligne.

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

Si vous voulez charger uniquement quelques colonnes dans une table, spécifiez la liste des champs :

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

Si une ligne a trop peu de champs, les colonnes omises prendront leur valeur par défaut. Les affectations de valeurs par défaut sont décrites dans Syntaxe de CREATE TABLE .

• Pour les types chaîne, la colonne est remplie avec la chaîne vide.
• Pour les types numériques, la colonne est mise à 0 .
• Pour les types dates et heures, la colonne est mise au zéro approprié pour le type. Types dates et heures .

Si une ligne d'entrée comporte trop de colonnes, les champs en trop sont ignorés, et le nombre d'alertes est incrémenté.

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x