Cours MariaDB / Importer et exporter un CSV : chemins, droits et encodage

Pour importer un fichier CSV dans MariaDB, faites correspondre les séparateurs, les guillemets, les fins de ligne et l’encodage, puis validez les valeurs textuelles avant de les convertir vers les types de destination. LOAD DATA LOCAL INFILE lit un fichier sur le client, tandis que SELECT INTO OUTFILE écrit sur le serveur : leurs chemins et leurs exigences de droits ne sont donc pas interchangeables.

Identifier la machine qui lit ou écrit le fichier

CSV signifie « valeurs séparées par des virgules ». La virgule est utilisée ici, mais un fichier nommé .csv peut employer un point-virgule ou d’autres conventions. Examinez le fichier dans un éditeur de texte avant d’écrire la commande d’importation.

Opération Emplacement du fichier Conditions principales
LOAD DATA LOCAL INFILE Machine du client LOCAL autorisé par le client et le serveur
LOAD DATA INFILE Machine du serveur MariaDB Privilège FILE et politique des chemins du serveur
SELECT INTO OUTFILE Machine du serveur MariaDB Privilège FILE, secure_file_priv et permissions du système

Le client est le programme qui ouvre la connexion ; le serveur gère les données. Même si vous utilisez le même ordinateur pendant l’exercice, distinguez leurs rôles : un chemin local au client ne désigne pas automatiquement un fichier du serveur distant.

N’autorisez LOCAL que pour un serveur de confiance, en tenant compte des fichiers que le client peut lire et envoyer. Ne relâchez pas toutes les permissions ou les options globales pour supprimer une erreur. Le cours sur le client MariaDB et les fichiers SQL explique le contexte d’exécution, et le cours sur INSERT et l’association des colonnes aux valeurs prépare le transfert vers une table typée. Les références officielles LOAD DATA INFILE et sa variante LOCAL et SELECT INTO OUTFILE précisent ces conditions.

Préparer un CSV UTF-8 et une table de texte brut

Utilisez la base d’apprentissage shopcourse et un compte course_user disposant des droits nécessaires pour créer, remplir et lire les tables de l’exercice. Sur le client Windows, créez le dossier C:/mariadb-lab s’il n’existe pas, puis un nouveau fichier products.csv, sans remplacer un fichier existant. Enregistrez exactement ce contenu en UTF-8 :

product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00

La première ligne contient les noms des trois champs. Le premier nom coréen signifie « grand carnet » et contient une virgule : ses guillemets maintiennent ce nom dans un seul champ. Les deux autres noms désignent une tasse et un stylo. Conservez les caractères coréens pour contrôler le même jeu de données ; les identifiants permettent de comparer les résultats.

Dans la connexion MariaDB, vérifiez que csv45_stage et csv45_products sont disponibles. Si une table existe déjà, examinez et sauvegardez son contenu, puis choisissez des noms d’exercice distincts dans tous les exemples plutôt que de l’écraser. Exécutez ce bloc dans le client SQL :

USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

csv45_stage sert de table de préparation : elle conserve les champs sous forme de texte, sans convertir immédiatement un identifiant ou un prix douteux. csv45_products recevra seulement les valeurs contrôlées, avec une clé primaire et un prix non négatif. Vous pouvez enregistrer les blocs SQL dans des fichiers UTF-8 et les exécuter avec SOURCE, en adaptant le chemin et en séparant la préparation, la validation et le transfert.

Charger le fichier du client et examiner les avertissements

À l’invite du système, ouvrez une connexion TCP avec la prise en charge de LOCAL. Le serveur d’apprentissage doit écouter au port indiqué et autoriser cette capacité ; le mot de passe est demandé séparément :

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse

Dans cette connexion MariaDB, exécutez d’abord LOAD DATA, immédiatement suivi de SHOW WARNINGS. Examinez ensuite les lignes brutes. Les requêtes de validation doivent être exécutées dans l’ordre expliqué ci-dessous :

LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
 OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
 OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- After format checks pass, verify numeric ranges and name length
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
 OR CAST(raw_id AS DECIMAL(65,0))>2147483647
 OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
 OR CHAR_LENGTH(raw_name)>100;

IGNORE 1 LINES saute l’en-tête. Les variables @id, @name et @price reçoivent les champs avant leur affectation aux colonnes brutes. LF désigne le saut de ligne \n et CR le retour chariot \r. Cet exemple coupe les lignes sur LF et enlève un CR final du dernier champ, ce qui traite aussi le cas simple courant des fins de ligne CRLF.

Si LOCAL est refusé, examinez avec l’administrateur la politique local_infile du serveur et l’option du client. N’activez pas cette fonctionnalité sur un serveur que vous ne connaissez pas pour contourner un message. Une importation LOCAL ne garantit pas le rejet de toutes les entrées incorrectes simplement parce qu’un mode SQL strict est actif : lisez les avertissements et les chaînes d’origine.

Commencez par la requête de format : elle doit ne renvoyer aucune ligne mal formée ou sans nom. Seulement après cette réussite, vérifiez les doublons d’identifiants normalisés, puis les bornes numériques et la longueur des noms. Convertir prématurément un texte incorrect peut produire une valeur ou un avertissement qui masque le défaut initial.

Sur un client Ubuntu, préparez plutôt un dossier de travail dans le terminal et enregistrez le même fichier UTF-8 à l’emplacement affiché :

mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"

Le chemin de l’instruction LOCAL doit être le chemin absolu réel du client. L’exemple suivant suppose un utilisateur de connexion nommé learner ; remplacez ce nom si nécessaire :

LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;

Exécutez seulement l’importation Windows ou Ubuntu adaptée à votre client. Charger les deux ajoute deux fois les lignes dans la table de préparation. Avec une autre convention d’échappement, un séparateur différent ou des sauts de ligne à l’intérieur des champs, convenez du format avec l’outil producteur et adaptez les options avant de charger les vraies données.

Transférer uniquement les valeurs validées

Avant l’insertion, les recherches de formats incorrects, de doublons normalisés et de valeurs hors limites doivent toutes être vides. Les identifiants doivent tenir dans un INT signé positif, les prix dans DECIMAL(10,2), et les noms dans 100 caractères au maximum. Les chaînes 01 et 1 deviennent le même identifiant après conversion : une comparaison des chaînes seules ne suffit pas.

INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;

Vous devez obtenir trois produits, ordonnés par identifiant, avec les prix 3500.00, 8000.00 et 1500.00. Le premier nom doit toujours contenir sa virgule, et les trois noms doivent rester lisibles en coréen. Une réussite de la commande sans vérification des champs ne démontre pas que le format a été correctement interprété.

Si une validation échoue, arrêtez le transfert et corrigez le fichier ou la règle de validation. Conservez la source et les diagnostics avant de vider une tentative. Une nouvelle commande LOAD ajoute des lignes ; répéter INSERT après un transfert réussi peut aussi provoquer un conflit de clé primaire. Pour recommencer cet exercice, utilisez les seuls objets jetables et la procédure de nettoyage finale après avoir conservé ce qui est utile.

Exporter un CSV sur le serveur avec un compte autorisé

Utilisez un administrateur approuvé ou un compte d’exportation disposant du privilège FILE. Dans cette connexion d’exportation, exécutez USE shopcourse; et contrôlez SELECT DATABASE(); avant de continuer : la base sélectionnée dans une autre connexion n’est pas partagée. Le privilège FILE n’est pas nécessaire à l’importation LOCAL précédente, mais il l’est pour OUTFILE. Vérifiez le répertoire autorisé et les droits d’écriture du compte système exécutant MariaDB :

SELECT @@secure_file_priv;

Si secure_file_priv désigne un répertoire non vide, la destination doit respecter cette restriction. Choisissez un nom de fichier encore inexistant. Sur un serveur Windows distant, le chemin C: ci-dessous appartient au serveur, pas à l’ordinateur depuis lequel vous utilisez le client :

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

L’export contient trois enregistrements et aucun en-tête. Un fichier existant n’est pas écrasé : choisissez un nouveau nom au lieu de supprimer un fichier dont vous ne connaissez pas l’usage. Si un en-tête est nécessaire à l’outil destinataire, ajoutez-le par une procédure d’exportation explicite. Renommer la sortie tabulée ordinaire de mariadb --batch avec une extension .csv ne la transforme pas en CSV à virgules.

Pour un serveur Ubuntu, contrôlez à nouveau SELECT @@secure_file_priv; et les permissions du compte de service. Si le répertoire autorisé est effectivement /var/lib/mysql-files/, un nouveau fichier côté serveur peut être créé ainsi :

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Remplacez ce répertoire par celui réellement approuvé et n’exécutez l’export qu’une fois avec le nouveau nom choisi. Ce chemin ne correspond pas au dossier personnel du client. Ne distribuez pas largement le privilège FILE et ne modifiez pas toutes les permissions du système pour contourner un refus.

Vérifier les champs et nettoyer les objets d’exercice

Ouvrez le fichier exporté avec un outil compatible avec le format choisi ou rechargez-le dans une table de contrôle distincte. Comparez chaque champ, pas seulement le nombre de lignes : virgules dans les noms, caractères Unicode, prix et ordre prévu. Cet export n’a pas d’en-tête ; ne sautez donc pas sa première ligne lors d’un contrôle par réimportation.

Les outils CSV n’utilisent pas tous la même convention pour NULL, valeur SQL absente. Définissez si un champ vide signifie texte vide ou donnée manquante, ainsi que la convention des guillemets, des échappements et des fins de ligne. Le jeu de données fourni utilise volontairement trois champs renseignés ; il ne démontre pas à lui seul tous les cas de données manquantes ou de champs multilignes.

Décidez séparément si les fichiers doivent être conservés. Après vérification de la base et sauvegarde de ce qui doit rester, supprimez uniquement les deux tables créées pour cet exercice :

DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;

Ce nettoyage ne supprime pas les fichiers CSV du client ou du serveur. DROP TABLE retire les données et les définitions et ne s’annule pas par un ROLLBACK ordinaire. Pour répéter l’exercice, vérifiez que les deux noms sont disponibles, recréez les tables, puis chargez une seule fois le fichier destiné à votre plateforme.