Pourquoi utiliser LOAD DATA INFILE ?
Cette commande permet de charger des données dans une table de votre base de données à une très grande de vitesse. J’ai pu par exemple insérer 500000 lignes en seulement 7 secondes. C’est quelque chose à considérer si vous avez de grosses bases à reproduire dans un environnement. En effet, par le passé, j’ai travaillé dans une entreprise où il fallait attendre 6h pour mettre en place la base de données, je pense que avec cette méthode on pourrait très bien ramener à moins de 10 minutes. Attention, le chargement est très rapide pour une table donnée, je ne tiens pas compte du temps de passage entre une table et une autre, mais normalement il n’y a pas de problème.
La méthode la plus souvent utilisée pour insérer un fichier dans une table, et de faire un fichier constitué de clause insert, cette méthode n’est pas très rapide en effet si vous avez plusieurs lignes insert mais c’est plusieurs requêtes qui sont faites cependant on peut optimiser en faisant un insert multiligne, mais même ça ça ralentit le chargement des données. En effet le fait d’exécuter une requête SQL va faire intervenir le moteur de base de données, qui doit lire la requête SQL l’interpréter l’exécuter et mettre en mémoire les choses.
Avec cette méthode, en outre passe l’exécution de requête SQL, en effet par exemple lorsque en charge un fichier CSV, il n’y a pas de requête SQL qui est faite, on injecte les données simplement colonne par colonne, ligne par ligne. Il n’y a donc pas d’exécution de code SQL, c’est une insertion brute, c’est pour ça que c’est aussi rapide.
Dans vos bases de données de développement, par exemple e-commerce, vous pouvez vous permettre d’utiliser une méthode traditionnelle, mais si vous êtes amené à travailler avec de grosses bases de données, comme un Big Data, analyse des données, vous avez de très grosses bases de données, à reconstituer dans la base de données. Cette méthode est donc très avantageuse.
Le problème avec cette fonction
Le problème avec cette fonction c’est qu’il est assez difficile de le paramétrer, en particulier avec mysql8. Le problème est encore plus ardu lorsque vous utilisez PHPmyadmin pour faire l’insertion de données. En effet, on intercalant entre le fichier et la base de données un logiciel écrit en PHP donc pas forcément très performant, vous augmentez les chances que cela ne marche pas. Je vous recommande donc de faire l’insertion depuis la ligne de commande. Même malgré ça nous allons rencontrer quelques difficultés.
Nous allons prendre un exemple simple, avec un petit fichier test.csv de quelques lignes, nous allons surtout nous concentrer sur la partie amont de l’insertion.
id,nom,prenom,email
1,"Dupont","Alex","dupont.alex@gmail.com"
2,"Dupont","Danielle","dupont.danielle@gmail.com"
3,"Durand","Ines","durand.ines@gmail.com"
4,"Durand","Hugo","durand.hugo@hotmail.com"
5,"Camus","Albert","albert.camus@gmail.com"
Nous allons disposer d’une table dont voici le code
CREATE TABLE `test` (
`id` int NOT NULL,
`nom` varchar(50) NOT NULL,
`prenom` varchar(50) NOT NULL,
`email` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Première tentative d’insertion dans PHPMyAdmin
Pour information la documentation officielle traitant de cette fonction se trouve sur cette page. La doc officielle dit aussi que MySQL doit avoir les privilèges sur les fichier à importer (logique), par exemple il doit avoir les droit de lecture sur le fichier test.csv.
LOAD DATA LOCAL INFILE 'E:\OneDrive\formapedia_cours\coursSQL\load infile\test.csv' INTO TABLE test
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, nom,prenom,email);
Ceci se solde par une erreur « #2068 - LOAD DATA LOCAL INFILE is forbidden, check related settings like mysqli.allow_local_infile|mysqli.local_infile_directory or PDO::MYSQL_ATTR_LOCAL_INFILE|PDO::MYSQL_ATTR_LOCAL_INFILE_DIRECTORY« . Ce qui est embêtant car je vois PDO donc j’imagine que ce doit être un réglage PHP dans PHPMyAdmin, il va falloir bidouiller le fichier de configuration. Je vois aussi qu’il y a un réglage dans le fichier de configuration de MySQL. Recherches infructueuses. Ce que je vois est qu’il faut mettre une variable de configuration dans my.ini (Windows) ou my.cnf (Linux)
[mysql]
local-infile=1
[mysqld]
...
local_infile=1 << parfois je vois local-infile=1 (avec le trait d'union)
[mysqldump]
quick
max_allowed_packet=512M
En redémarrant le serveur, je n’ai pas plus de succès…
Je tente de passer par MySQL en ligne de commande
En fait je fais ça, car je sais que PhpmyAdmin introduit une couche de configuration rendant la tâche plus délicate. Je passe donc par la ligne de commande qui est plus simple. En se connectant en ligne, n’oubliez pas de sélectionner la base de donnée avec use.
En collant le texte ci-dessous: Attention \r\n dans le système Windows mais \n tout court pour Linux
LOAD DATA LOCAL INFILE 'E:\OneDrive\formapedia_cours\coursSQL\load infile\test.csv' INTO TABLE test
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, nom,prenom,email);
J’ai a réponse suivante:
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
C’est donc une question de restriction de lecture de fichier, mais je suis sous Windows, je ne m’inquiète pas trop (Linux est plus subtile), j’essais néanmoins de placer le fichier csv dans le répertoire où se trouve la base de données (dans le répertoire d’installation de MySQL, chaque base de données correspond à un fichier binaire, dans un répertoire de même nom (dans le cas de InnoDB). Mais j’ai toujours le même problème.
La révélation
c’est alors que je tombe sur cette page qui me dit de me connecter avec un argument:
mysql --local-infile=1 -u root -p
Et voilà j’ai pu loader le csv sans problème !
