14.1.5 Syntaxe de LOAD DATA INFILE

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

La commande LOAD DATA INFILE lit les lignes dans un fichier texte et les insère à très grande vitesse. Pour plus d'informations sur l'efficacité des commandes INSERT comparativement à LOAD DATA INFILE et pour accélérer les commandes LOAD DATA INFILE , voyez Vitesse des requêtes INSERT . 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é. mysqlimport , importer des données depuis des fichiers texte .

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.

Si le mot clé LOCAL est spécifié, il est interprété en suivant les règles suivantes :

• Si LOCAL est spécifié, le fichier est lu par le programme client, et envoyé vers l'hôte.
• Si LOCAL n'est pas spécifiée, le fichier doit être sur le serveur hôte, et sera lu directement par le serveur.

LOCAL est disponible depuis MySQL 3.22.6 ou plus récent.

Utiliser LOCAL est plus lent que de laisser le serveur accéder directement aux fichiers, car le contenu du fichier doit être envoyé via le réseau au serveur. D'un autre coté, vous n'aurez pas besoin de droits de FILE pour faire un chargement local.

Si vous avez besoin de lire des données LOAD DATA depuis un pipe, vous devez utiliser la technique suivante :

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

Si vous utilisez une version de MySQL antérieure à la 3.23.24 vous ne pouvez lire à partir d'un FIFO avec LOAD DATA INFILE . Si vous avez besoin de lire à partir d'un FIFO (par exemple la sortie de gunzip), utilisez LOAD DATA LOCAL INFILE .

• 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 spécifiez IGNORE , les nouvelles lignes seront ignorées. Si vous ne spécifiez pas cette option, une erreur sera générée à chaque doublon, et le reste du fichier sera ignoré. Avec l'option LOCAL , le comportement par défaut est le même que si IGNORE est spécifié : ceci est dû au fait que le serveur n'a pas moyen de stopper la transmission du fichier au milieu de l'opération.

Si vous voulez ignorer les clés étrangères le temps du chargement du fichier, utilisez la commande SET FOREIGN_KEY_CHECKS=0 avant d'exécuter LOAD DATA .

LOAD DATA INFILE est le complémentaire de SELECT ... INTO OUTFILE . Syntaxe de 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 spécifié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 parmi les nouvelles lignes.
• Si LINES STARTING BY prefix est utilisé, lit jusqu'au préfixe, et commence à lire après le préfixe. Si la ligne n'inclut pas de préfixe, elle sera ignorée.
• 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.

A l'inverse, les valeurs par défaut font que SELECT ... INTO OUTFILE écrit les données comme ceci :

• Ecrivez des tabulations entre les champs.
• N'entourez pas les champs de guillemets.
• Utilisez '\' pour échapper les occurrences de tabulation, nouvelle ligne, '\' trouvées dans les valeurs.
• Insère une nouvelle ligne entre les lignes.

Note : si vous avez généré le fichier sur Windows, vous devrez peut-être utiliser LINES TERMINATED BY '\r\n' pour lire le fichier correctement, car les programmes Windows utilisent généralement deux caractères comme fin de ligne. Certains programmes, comme WordPad , peuvent utiliser \r comme terminateur de ligne lors de l'écriture. Pour lire ces fichiers, utilisez LINES TERMINATED BY '\r .

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 ASCII de zéro)

En lecture, si le caractère FIELDS ESCAPED BY n'est pas vide, les occurrences de ce caractère sont supprimées, et le caractère suivant est lu littéralement. Les exceptions à cette règle sont '0' ou 'N' (par exemple, 0 ou \N si le caractère d'échappement est '\' ). Ces séquences sont interprétées comme l'octet nul (ASCII 0 ) et la valeur NULL . Voyez plus bas pour la gestion des valeurs NULL .

Dans certains cas, les options de FIELDS et LINES interfèrent entre elles :

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

La gestion des valeurs NULL dépend des options FIELDS et LINES que vous utilisez :

• 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 occurrence 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.

L'exemple suivant charge toutes les colonnes de la table persondata :

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

Les colonnes TIMESTAMP prendront la date et l'heure courante uniquement si on leur affecte la valeur NULL , ou (pour la première colonne TIMESTAMP seulement) si la colonne TIMESTAMP est ignorée de la liste des colonnes spécifiée.

LOAD DATA INFILE considère toutes les valeurs lues comme des chaînes de caractères : vous ne pourrez donc pas utiliser la forme numérique des colonnes ENUM ou SET , comme d'habitude. Toutes les colonnes ENUM et SET doivent être spécifiée comme des chaînes ! Si vous utilisez l'API C, vous pouvez obtenir des informations à propos de la requête en utilisant la fonction mysql_info() quand LOAD DATA INFILE se termine. Le format de la chaîne d'informations est le suivant :

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

En MySQL version 4.1.1 vous pouvez utiliser SHOW WARNINGS pour obtenir la liste des premières max_error_count alertes. Syntaxe de SHOW WARNINGS .