[Tuto][SQL]Introduction à LOAD DATA INFILE

1 message   •   Page 1 sur 1
Avatar du membre
Administrateur PHPfrance
Administrateur PHPfrance | 13238 Messages

23 mai 2006, 13:51

Afin de transférer des données à grande vitesse depuis un fichier texte vers une base de données MySQL, il existe une instruction SQL : LOAD DATA INFILE

Comme son nom l’indique, cette instruction permet de charger des données depuis un fichier texte vers une base de données MySQL sans l’intervention d’un langage de programmation.

Il existe une fonction qui réalise l’inverse, c'est-à-dire extraire les données d’une base de données pour les enregistrer dans un fichier : son utilisation est expliquée dans le tutorial "SELECT INTO OUTFILE" (Tutorial en construction)

L’utilisation de cette fonction apporte plusieurs avantages :
  • en diminuant le nombre d’intermédiaire (directement du fichier à la base de données), on réduit le temps de traitement
  • Cette fonction est optimisée pour transférer un gros volume de données très rapidement.
Le fichier contenant les données doit seulement respecter quelques règles simples :
  • le séparateur de champs doit être fixe
  • le séparateur de lignes doit être fixe
  • Si ces séparateurs se retrouvent dans les valeurs contenues dans le fichier, ils doivent être échappés par un caractère spécifique (par exemple le caractère ‘\’, comme en PHP)
Un fichier CSV, par exemple, est un fichier valide pour un transfert avec l’instruction LOAD DATA INFILE puisque le caractère de séparation de champs est toujours ‘;’ et le caractère de séparation de lignes est toujours le retour à la ligne (‘\n’)

Prenons l’exemple du fichier CSV suivant :
"1";"Damien";"Admin"
"2";"Zeus";"Modérateur"
"3";"Truc";"ViPHP"
"4";"nicolas";"Membre"
Et la table suivante

Code : Tout sélectionner

+-----------+ | Membres | +-----------+ | Id_membre | | Login | | Statut | +-----------+
Afin de remplir la table « membres » avec le fichier CSV, il est possible d’utiliser la requete SQL suivante :
LOAD DATA INFILE ‘fichier.csv’
	INTO TABLE members
FIELDS 
	TERMINATED BY ';'
	ENCLOSED BY '"'
	ESCAPED BY '\\'
LINES
	STARTING BY ''
	TERMINATED BY '\n'
(id_membre, login, statut)
La ligne LOAD DATA INFILE ‘fichier.csv’ permet de définir le fichier à explorer. 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.
La ligne INTO TABLE membres indique dans quelle table les données doivent être chargées.

L’ensemble FIELDS permet de définir les règles sur les champs :
  • TERMINATED BY ‘ ;’ définit que le caractère ; est le séparateur entre les champs
  • ENCLOSED BY ‘"’ définit que le caractère " entoure les champs
  • ESCAPED BY ‘\\’ définit que le caractère \ permet d’échapper les caractères interdits dans le fichier. Ces caractères spéciaux sont l’ensemble des caractères définit dans les règles du fichier
L’ensemble LINES permet de définir les règles sur les tuples de données
  • STARTING BY ‘’ définit qu’il n’y a pas de caractère spécial pour démarrer la chaine
  • TERMINATED BY ‘\n’ définit que le retour à la ligne correspond au changement de ligne
La ligne (id_membre, login, statut) donne la liste des champs dans lesquels les données vont être enregistrées. L’ordre de ces champs doit correspondre à l’ordre dans lesquelles se trouvent les données dans le fichier.

En plus des lignes présentes dans l’exemple ci-dessus, vous pouvez spécifier le comportement qu’aura la requête si elle rencontre une ligne déjà présente, dans l’exemple précédent, si la requête tente d’enregistrer un id_membre déjà présent dans la vie.
Voici la requête de l’exemple précédente :
LOAD DATA INFILE ‘fichier.csv’
	REPLACE
	INTO TABLE members
FIELDS 
	TERMINATED BY ';'
	ENCLOSED BY '"'
	ESCAPED BY '\\'
LINES
	STARTING BY ''
	TERMINATED BY '\n'
(id_membre, login, statut)
L’ajout du mot clé « REPLACE » permet d’indiquer à la requête d’écraser la ligne existante pour la remplacer par les nouvelles valeurs
Il est également possible de spécifier « IGNORE » plutôt que « REPLACE ». Dans ce cas là, les données existantes seront conservées.

Une dernière instruction peut être ajoutée à la requête : IGNORE x LINES. Cette instruction permet de ne pas interpréter les x 1eres ligne.
Voici la requête adaptée pour ignorer les 2 1ères lignes :
LOAD DATA INFILE ‘fichier.csv’
	INTO TABLE members
FIELDS 
	TERMINATED BY ';'
	ENCLOSED BY '"'
	ESCAPED BY '\\'
LINES
	STARTING BY ''
	TERMINATED BY '\n'
IGNORE 2 LINES
(id_membre, login, statut)
Les lignes LOAD DATA INFILE, INTO TABLE membres et (id_membres, login, statut) sont les seules lignes obligatoires.
Voici les valeurs par défaut dans le cas où vous ne spécifiez pas explicitement une valeur.
IGNORE | REPLACE : IGNORE. Cela veut dire : valeur par défaut IGNORE (dans le cas ou le paramètre n'est pas indiqué), les lignes ne seront pas écrasées.
TERMINATED BY '\t' : le séparateur de champ par défaut est la tabulation
ENCLOSED BY '' : il n’y a pas de caractère qui entoure les champs par défaut
ESCAPED BY '\\' : le caractère d’échappement des caractères par défaut est \
TERMINATED BY '\n' : par défaut, les lignes sont terminées par un retour à la ligne

Pour plus d'informations sur la fonction SQL LOAD DATA INFILE, cliquez ici
Connaître son ignorance est la meilleure part de la connaissance
Pour un code lisible : n'hésitez pas à sauter des lignes et indenter

twitter - site perso - Github - Zend Certified Engineer

1 message   •   Page 1 sur 1