Les fichiers de valeurs séparées par des virgules (CSV) sont un moyen de transférer des données entre les applications. Les bases de données comme MySQL et les tableurs comme Excel prennent en charge l'importation et l'exportation via CSV, vous pouvez donc utiliser des fichiers CSV pour échanger des données entre les deux.
Les fichiers CSV sont en texte brut, ils sont donc naturellement légers et faciles pour exporter depuis MySQL.
Depuis le serveur de base de données
Si vous avez accès au serveur sur lequel MySQL s'exécute, vous pouvez exporter une sélection avec la commande INTO OUTFILE.
SELECT id, column1, column2 FROM table INTO OUTFILE '/tmp/mysqlfiles/table.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '”' LINES TERMINATED BY 'n'
Cela produira un fichier CSV dans /tmp /mysqlfiles/table.csv, ou partout où vous l'avez configuré. Vous devez vous assurer que l'utilisateur exécutant MySQL (généralement mysql ou root) a la propriété et l'accès en écriture au répertoire.
Vous devrez également vous assurer que le paramètre secure_file_priv autorise MySQL à accéder à ce répertoire. Ceci, par défaut, bloque l'accès en lecture et en écriture à partir des requêtes SQL. C'est une bonne chose; si votre code est vulnérable à l'injection SQL, tout attaquant potentiel n'aurait accès qu'à MySQL, et pas au reste du système de fichiers.
Publicité
Vous pouvez ajouter à la liste blanche des répertoires spécifiques en éditant votre fichier de configuration MySQL ( généralement situé dans /etc/my.cnf) pour inclure :
[mysqld] secure-file-priv = “/tmp/mysqlfiles”
Ce qui permettra à MySQL de lire et d'écrire dans /tmp/mysqlfiles/(que vous devrez créer avec mkdir). Une fois que MySQL peut exporter des fichiers, vous devriez pouvoir exécuter la requête et générer des fichiers CSV.
Avec le paramètre ENCLOSED BY, les virgules seront correctement échappées, par exemple :
“3”,” Escape, this”,”aussi, this”
Que vous pouvez prendre et importer directement dans n'importe quel tableur ou autre logiciel.
Gardez à l'esprit que le fichier CSV exporté n'inclut pas les en-têtes de colonne, mais les colonnes seront dans le même ordre que l'instruction SELECT. De plus, les valeurs nulles seront exportées en tant que N, ce qui est un comportement attendu, mais si vous souhaitez modifier cela, vous pouvez modifier la sélection en enveloppant ifnull(field, “”) autour de vos champs dans votre instruction SELECT.< /p>
Depuis la ligne de commande MySQL
Si vous n'avez qu'un accès en ligne de commande à l'instance MySQL et non au serveur lui-même (par exemple, lorsqu'il n'est pas géré par vous, dans le cas d'Amazon RDS), le problème est un peu plus délicat. Bien que vous puissiez utiliser FIELDS TERMINATED BY ',' sur le serveur pour générer une liste séparée par des virgules, la CLI MySQL se séparera par des onglets par défaut.
Publicité
Entrez simplement une requête à partir de la ligne de commande , et dirigez-le vers un fichier :
mysql -u root -e “select * from database;” > output.tsv
Parce que la sortie MySQL est séparée par des tabulations, cela s'appelle un fichier TSV, pour les “valeurs séparées par des tabulations,” et peut fonctionner à la place de votre fichier CSV dans certains programmes comme les importations de feuilles de calcul. Mais ce n'est pas un fichier CSV, et le convertir en un est compliqué.
Vous pouvez simplement remplacer chaque onglet par une virgule, ce qui fonctionnerait mais entraînerait son échec s'il y a des virgules dans les données d'entrée. Si vous êtes sûr à 100 % qu'il n'y a pas de virgules dans votre fichier TSV (vérifiez avec grep), vous pouvez remplacer les onglets par la sortie sed :
sed “s/t/,/g”. tsv > output.csv
Mais si vous avez des virgules dans vos données, vous devrez utiliser une expression rationnelle beaucoup plus longue :
sed “s/'/'/;s/t/”, “/g;s/^/”/;s/$/”/;s/n//g” output.tsv > sortie.csv
Cela échappera correctement aux champs avec des guillemets, ce qui résoudra le problème des virgules.
Remarque : le caractère de tabulation t n'est pas standard. Sur macOS et BSD, il n'est pas disponible, ce qui entraîne un désordre de chaque minuscule “t” sed insère des virgules erronées. Pour résoudre ce problème, vous devrez utiliser un caractère de tabulation littéral à la place de t :
sed “s//,/g” output.tsv > Publicité output.csv
Si vos données d'entrée contiennent des onglets, vous n'avez pas de chance et devrez générer vous-même un fichier CSV avec un langage de script.
Faites-le manuellement avec un vrai langage de programmation
MySQL (et la plupart des bases de données) sont conçus pour interagir, vous avez donc probablement déjà une sorte de langage de programmation connecté à MySQL . La plupart des langages peuvent également écrire sur le disque, vous pouvez donc créer vos propres scripts de sortie CSV en lisant directement les champs de la base de données, en les échappant correctement et en écrivant un fichier délimité par des virgules.
Un exemple en Python.< /p>