Exportation de données MySQL
Dans MySQL, vous pouvez utiliser l'instructionSELECT...INTO OUTFILEpour exporter facilement des données vers un fichier texte.
Utilisation de l'instruction SELECT ... INTO OUTFILE pour exporter des données
SELECT...INTO OUTFILEest la syntaxe dans MySQL utilisée pour exporter les résultats d'une requête vers un fichier.
SELECT...INTO OUTFILEpermet d'écrire les résultats de la requête dans un fichier texte. Utilisation de base :
SELECT column1, column2, ... INTO OUTFILE 'file_path' FROM your_table WHERE your_conditions;
Description du paramètre :
column1, column2, ...: Les colonnes à sélectionner.'file_path': Spécifiez le chemin et le nom du fichier de sortie.your_table: La table à interroger.your_conditions: La condition de requête.
Voici un exemple simple :
Exemple
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
Dans l'instruction SQL ci-dessus, nous avons sélectionné les colonnes id, name et email de la table users et avons écrit les résultats dans le fichier /tmp/user_data.csv. FIELDS TERMINATED BY ',' spécifie le séparateur entre les colonnes (virgule), et LINES TERMINATED BY '\n' spécifie le séparateur entre les lignes (nouvelle ligne).
Notez que l'exécution deSELECT...INTO OUTFILEnécessite l'autorisation correspondante, et le répertoire du fichier de sortie doit être un emplacement où le serveur MySQL peut écrire.
Dans l'exemple suivant, nous exportons les données de la table example_tbl vers le fichier /tmp/example.txt :
mysql> SELECT * FROM example_tbl
-> INTO OUTFILE '/tmp/example.txt';
Vous pouvez utiliser des options de commande pour définir le format spécifique de sortie des données. L'exemple suivant exporte les données au format CSV :
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/example.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Dans l'exemple suivant, un fichier est généré avec des valeurs séparées par des virgules. Ce format peut être utilisé par de nombreux programmes.
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
L'instruction SELECT ... INTO OUTFILE possède les propriétés suivantes :
- LOAD DATA INFILE est l'opération inverse de SELECT ... INTO OUTFILE, en utilisant la syntaxe SELECT. Pour écrire des données d'une base de données vers un fichier, utilisez SELECT ... INTO OUTFILE; pour relire le fichier dans la base de données, utilisez LOAD DATA INFILE.
- Un SELECT sous la forme SELECT ... INTO OUTFILE 'file_name' peut écrire les lignes sélectionnées dans un fichier. Le fichier est créé sur l'hôte du serveur, vous devez donc disposer du privilège FILE pour utiliser cette syntaxe.
- La sortie ne peut pas être un fichier existant. Cela empêche toute altération des données du fichier.
- Vous avez besoin d'un compte connecté au serveur pour récupérer le fichier. Sinon, SELECT ... INTO OUTFILE n'aura aucun effet.
- Sous UNIX, le fichier est lisible après sa création, avec des autorisations appartenant au serveur MySQL. Cela signifie que même si vous pouvez lire le fichier, vous pouvez ne pas être en mesure de le supprimer.
mysqldump Exporter des tables en tant que données brutes
mysqldump est un outil en ligne de commande fourni par MySQL pour sauvegarder et exporter des bases de données.
mysqldumpC'est un utilitaire utilisé par mysql pour exporter des bases de données. Il produit principalement un script SQL qui contient les commandes nécessaires pour recréer la base de données à partir de zéro, telles que CREATE TABLE, INSERT, etc.
En utilisantmysqldumppour exporter des données, vous devez utiliser l'option--tabpour spécifier le répertoire où les fichiers exportés seront enregistrés. Cette cible doit être accessible en écriture.
Utilisation de base de mysqldump :
mysqldump -u username -p password -h hostname database_name > output_file.sql
Description du paramètre :
-u: Spécifiez le nom d'utilisateur MySQL.-p: Demander le mot de passe.-h: Spécifiez le nom d'hôte MySQL.database_name: Le nom de la base de données à exporter.output_file.sql: Le fichier dans lequel les données exportées sont enregistrées.
L'exemple suivant exporte la table example_tbl vers le répertoire /tmp :
$ mysqldump -u root -p --no-create-info \
--tab=/tmp EXAMPLE example_tbl
password ******
Exemple de mysqldump
1. Exporter toute la base de données
Exporter la base de données mydatabase vers le fichier mydatabase_backup.sql :
mysqldump -u root -p mydatabase > mydatabase_backup.sql
2. Exporter une table spécifique
Si vous souhaitez uniquement exporter une certaine table de la base de données, vous pouvez utiliser la commande suivante :
mysqldump -u username -p password -h hostname database_name table_name > output_file.sql
Ou :
mysqldump -u root -p mydatabase mytable > mytable_backup.sql
3. Exporter la structure de la base de données
Si vous souhaitez uniquement exporter la structure de la base de données sans inclure les données, vous pouvez utiliser l'option--no-data :
mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql
4. Exporter un fichier compressé
Vous pouvez compresser les données exportées pour réduire la taille du fichier. Par exemple, en utilisant gzip :
mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz
Exporter les données au format SQL
Exporter les données au format SQL vers un fichier spécifié, comme indiqué ci-dessous :
$ mysqldump -u root -p EXAMPLE example_tbl > dump.txt password ******
Le contenu du fichier créé par la commande ci-dessus est le suivant :
-- MySQL dump 8.23
--
-- Host: localhost Database: EXAMPLE
---------------------------------------------------------
-- Server version 3.23.58
--
-- Table structure for table `example_tbl`
--
CREATE TABLE example_tbl (
example_id int(11) NOT NULL auto_increment,
example_title varchar(100) NOT NULL default '',
example_author varchar(40) NOT NULL default '',
submission_date date default NULL,
PRIMARY KEY (example_id),
UNIQUE KEY AUTHOR_INDEX (example_author)
) TYPE=MyISAM;
--
-- Dumping data for table `example_tbl`
--
INSERT INTO example_tbl
VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO example_tbl
VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO example_tbl
VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');
Si vous devez exporter les données de l'ensemble de la base de données, vous pouvez utiliser la commande suivante :
$ mysqldump -u root -p EXAMPLE > database_dump.txt password ******
Si vous devez sauvegarder toutes les bases de données, vous pouvez utiliser la commande suivante :
$ mysqldump -u root -p --all-databases > database_dump.txt password ******
L'option --all-databases a été ajoutée dans MySQL 3.23.12 et les versions ultérieures.
Cette méthode peut être utilisée pour mettre en œuvre une stratégie de sauvegarde de base de données.
Copie de tables et de bases de données vers d'autres hôtes
Si vous devez copier des données vers un autre serveur MySQL, vous pouvez spécifier le nom de la base de données et la table dans la commande mysqldump.
Exécutez la commande suivante sur l'hôte source pour sauvegarder les données dans le fichier dump.txt :
$ mysqldump -u root -p database_name table_name > dump.txt password *****
Si vous sauvegardez l'ensemble de la base de données, il n'est pas nécessaire d'utiliser des noms de tables spécifiques.
Si vous devez importer la base de données sauvegardée dans le serveur MySQL, vous pouvez utiliser la commande suivante. Lors de l'utilisation de la commande suivante, vous devez confirmer que la base de données a déjà été créée :
$ mysql -u root -p database_name < dump.txt password *****
Vous pouvez également utiliser la commande suivante pour importer directement les données exportées vers un serveur distant, mais veuillez vous assurer que les deux serveurs sont connectés et peuvent accéder l'un à l'autre :
$ mysqldump -u root -p database_name \
| mysql -h other-host.com database_name
La commande ci-dessus utilise un pipe pour importer les données exportées vers l'hôte distant spécifié.
Autres extensions