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 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 vous spécifiez le mot clef CONCURRENT avec un table au format MyISAM, les autres threads pourront accéder à la table durant l'exécution de la commande LOAD DATA. L'utilisation de cette option ralentira un peu les performances de LOAD DATA même si aucun thread n'utilise la table en même si aucun autre thread n'accède à la table en même temps.

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.

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.

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.

Depuis MySQL 3.23.49 et MySQL 4.0.2 (4.0.13 sur Windows), LOCAL fonctionne uniquement si votre serveur et votre client ont été configuré pour. Par exemple, si mysqld a été lancé avec --local-infile=0, LOCAL ne fonctionnera pas.

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 plus ancienne que 3.23.25, vous pouvez uniquement utiliser cette technique avec LOAD DATA LOCAL INFILE.

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.

Lorsque les fichiers de données sont sur le serveur, celui-ci 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 é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.

Notez que ces règles font qu'un fichier tel que ./myfile.txt est lu dans le dossier de données du serveur, alors que s'il est nommé myfile.txt, il sera lu dans le dossier de base de données courante. Par exemple, la commande LOAD DATA suivante lit le fichier donnees.txt dans le dossier de la base db1 car db1 est la base de données courante, même si la commande charge explicitement le fichier dans la base de données db2 :

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

Les mots réservés REPLACE et IGNORE contrôlent la méthode d'insertion de lignes lorsque des doublons apparaissent pour les clés uniques.

Si vous spécifiez REPLACE, les nouvelles lignes remplaceront les anciennes.

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

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.

Si vous utilisez LOAD DATA INFILE sur une table vide de type MyISAM, tous les index non-uniques seront créés dans un processus séparé (tout comme REPAIR). Cela rend LOAD DATA INFILE beaucoup plus rapide si vous avez plusieurs index.

LOAD DATA INFILE est le complémentaire de SELECT ... INTO OUTFILE. 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 spécifiez la clause FIELDS, les sous-clauses TERMINATED BY, [OPTIONALLY] ENCLOSED BY, et ESCAPED BY sont aussi optionnelles, mais vous devez en spécifier au moins une.

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

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

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

LINES TERMINATED BY '\n'

En d'autres termes, les valeurs par défaut font que LOAD DATA INFILE lit les données comme suit :

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.

Notez que pour utiliser FIELDS ESCAPED BY '\\', vous devez spécifier deux anti-slash pour que cette valeur soit interprétée comme un anti-slash simple.

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.

L'option IGNORE nombre LINES sert à ignorer une en-tête de fichier, telle que des noms de colonnes, qui débutent parfois un fichier à charger :

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

Lorsque vous utilisez SELECT ... INTO OUTFILE conjointement avec LOAD DATA INFILE pour écrire des données dans un fichier et les relire dans une table, les options de FIELDS et LINES doivent être identiques. Sinon, LOAD DATA INFILE ne pourra pas interpréter le contenu du fichier correctement. Supposez que la commande SELECT ... INTO OUTFILE ait écrit un fichier délimité par des virgules :

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

Pour lire ce fichier, la commande correcte serait :

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

Si au contraire, vous essayez de lire le fichier avec la commande ci-dessous, cela ne fonctionnera pas, car la commande LOAD DATA INFILE essaie de lire des tabulations entre les champs :

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

Il est probable que chaque ligne d'entrée sera interprétée que comme un seul champ.

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';

Les clauses FIELDS et LINES peuvent prendre des chaînes vides comme valeur. S'il la chaîne n'est pas vide, FIELDS [OPTIONALLY] ENCLOSED BY et FIELDS ESCAPED BY ne doivent avoir qu'un seul caractère. Les valeurs de FIELDS TERMINATED BY et LINES TERMINATED BY peuvent avoir plus d'un caractère. Par exemple, pour écrire des lignes terminées par le couple retour chariot/nouvelle ligne, ou pour lire un tel fichier, spécifiez la clause LINES TERMINATED BY '\r\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);

FIELDS [OPTIONALLY] ENCLOSED BY contrôle la mise entre guillemets des champs. Pour l'écriture de fichier (SELECT ... INTO OUTFILE), si vous omettez le mot OPTIONALLY, tous les champs seront entourés par le caractère spécifié dans la clause ENCLOSED BY. Par exemple, si la virgule est utilisée comme séparateur 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"

Si vous spécifiez OPTIONALLY, le caractère ENCLOSED BY n'est utilisé que pour protéger les colonnes de types 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

Notez que les occurrences du caractère ENCLOSED BY dans un champs sont échappée en les préfixant avec le caractère ESCAPED BY. Notez aussi que si vous spécifiez un caractère d'échappement vide, il n'est pas possible de garantir que les champs seront correctement relus par LOAD DATA INFILE. Par exemple, l'exemple ci-dessus apparaîtra comme montré ci-dessous. Notez que le second champ de la quatrième ligne comporte une virgule suivant un guillemet qui semble (mais c'est faux) terminer la 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

Lors des lectures, le caractère ENCLOSED BY, s'il est présent, est supprimé des extrémités de la valeur du champ. (ce qui est vrai, qu'il y ait l'option OPTIONALLY ou pas). Les occurrences du caractère ENCLOSED BY, précédées par le caractère ESCAPED BY sont interprétées comme faisant partie de la valeur du champ. Les caractères ENCLOSED BY doublées, apparaissant dans la chaîne, sont interprétés comme le caractère ENCLOSED BY lui-même. Par exemple, si ENCLOSED BY '"' est spécifié, les guillemets sont gérés comme ceci :

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

FIELDS ESCAPED BY contrôle les caractères spéciaux. Si le caractère FIELDS ESCAPED BY n'est pas vide, il est utilisé pour préfixer les caractères suivants en écriture :
- 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)

Si le caractère FIELDS ESCAPED BY est vide, aucun caractère ne sera échappé. Ce n'est probablement pas une bonne idée de spécifier un caractère d'échappement vide, en particulier si les valeurs dans vos champs risquent d'utiliser l'un des caractères de la liste ci-dessus.

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 !

Certains cas ne sont pas supportés par LOAD DATA INFILE:
- 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;

Aucun champ n'est spécifié, ce qui fait que LOAD DATA INFILE s'attend à ce que les lignes lues contiennent le bon nombre de champs. Les valeurs par défaut de FIELDS et LINES sont utilisées.

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

Vous devez aussi spécifier les champs si l'ordre dans lequel ils seront lus diffère de l'ordre des colonnes dans la table. Sinon, MySQL ne pourra pas savoir à quelle colonne correspond une valeur.

- Si une ligne a trop peu de champs, les colonnes omises prendront leur valeur par défaut.

Une valeur de champs vide et un champ manquant ne seront pas interprétés de la même façon :
- 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. See Section 11.3, « Les types date et heure ».

Notez que vous obtiendrez le même résultat en assignant à ces différents types de champs la chaîne vide dans une commande INSERT ou UPDATE.

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.

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

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

Les alertes sont générées dans les mêmes circonstances que pour la commande INSERT, excepté que LOAD DATA INFILE génère aussi des alertes s'il y a trop peu ou trop de champs dans une ligne. Les alertes ne sont pas stockées; le nombre d'alertes est la seule indication. Si vous recevez des alertes et vous voulez savoir exactement ce qui s'est passé, exécutez une commande SELECT ... INTO OUTFILE dans un autre fichier et comparez le avec le fichier original.