Inclure les en-têtes lors de l’utilisation de SELECT INTO OUTFILE?

Est-il possible d’inclure les en-têtes d’une manière ou d’une autre lorsque vous utilisez MySQL INTO OUTFILE ?

Vous devrez coder ces en-têtes vous-même. Quelque chose comme:

 SELECT 'ColName1', 'ColName2', 'ColName3' UNION ALL SELECT ColName1, ColName2, ColName3 FROM YourTable INTO OUTFILE '/path/outfile' 

La solution proposée par Joe Steanelli fonctionne, mais créer une liste de colonnes est peu pratique lorsque des dizaines ou des centaines de colonnes sont impliquées. Voici comment obtenir la liste des colonnes de la table my_table dans my_schema .

 -- override GROUP_CONCAT limit of 1024 characters to avoid a truncated result set session group_concat_max_len = 1000000; select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table' AND TABLE_SCHEMA = 'my_schema' order BY ORDINAL_POSITION 

Vous pouvez maintenant copier et coller la ligne résultante en tant que première instruction dans la méthode de Joe.

Pour la sélection complexe avec ORDER BY, j’utilise les éléments suivants:

 SELECT * FROM ( SELECT 'Column name #1', 'Column name #2', 'Column name ##' UNION ALL ( // complex SELECT statement with WHERE, ORDER BY, GROUP BY etc. ) ) resulting_set INTO OUTFILE '/path/to/file'; 

Cela vous permettra d’avoir des colonnes ordonnées et / ou une limite

 SELECT 'ColName1', 'ColName2', 'ColName3' UNION ALL SELECT * from (SELECT ColName1, ColName2, ColName3 FROM YourTable order by ColName1 limit 3) a INTO OUTFILE '/path/outfile'; 

Je fais simplement 2 requêtes, d’abord pour obtenir une sortie de requête (limite 1) avec des noms de colonnes (pas de code, pas de problèmes de jointures, Order by, noms de colonnes personnalisés, etc.) et fichier:

 CSVHEAD=`/usr/bin/mysql $CONNECTION_STRING -e "$QUERY limit 1;"|head -n1|xargs|sed -e "s/ /'\;'/g"` echo "\'$CSVHEAD\'" > $TMP/head.txt /usr/bin/mysql $CONNECTION_STRING -e "$QUERY into outfile '${TMP}/data.txt' fields terminated by ';' optionally enclosed by '\"' escaped by '' lines terminated by '\r\n';" cat $TMP/head.txt $TMP/data.txt > $TMP/data.csv 

Vous pouvez utiliser l’instruction préparée avec la réponse de lucek et exporter dynamicment la table avec le nom des colonnes dans le fichier CSV:

 --If your table has too many columns SET GLOBAL group_concat_max_len = 100000000; --Prepared statement SET @SQL = ( select CONCAT('SELECT * INTO OUTFILE \'YOUR_PATH\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'\' LINES TERMINATED BY \'\\n\' FROM (SELECT ', GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")),' UNION select * from YOUR_TABLE) as tmp') from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YOUR_TABLE' AND TABLE_SCHEMA = 'YOUR_SCHEMA' order BY ORDINAL_POSITION ); --Execute it PREPARE stmt FROM @SQL; EXECUTE stmt; 

Merci lucek.

Ceci est une solution de rechange si vous êtes familier avec Python ou R, et que votre table peut tenir dans la mémoire.

Importez la table SQL en Python ou en R, puis exportez-la en tant que CSV et vous obtiendrez les noms de colonne ainsi que les données.

Voici comment je le fais en utilisant R, nécessite la bibliothèque RMySQL:

 db <- dbConnect(MySQL(), user='user', password='password', dbname='myschema', host='localhost') query <- dbSendQuery(db, "select * from mytable") dataset <- fetch(query, n=-1) write.csv(dataset, 'mytable_backup.csv') 

C'est un peu sortingcheur mais j'ai trouvé que c'était une solution rapide quand mon nombre de colonnes était trop long pour utiliser la méthode de concat ci-dessus. Remarque: R appenda une colonne 'row.names' au début du fichier CSV, vous devrez donc la supprimer si vous devez vous fier au CSV pour recréer la table.

Donc, si toutes les colonnes de my_table sont un type de données de caractères , nous pouvons combiner les meilleures réponses (par Joe, matt et evilguc) pour obtenir l’en-tête ajouté automatiquement dans une requête SQL “simple”, par exemple

 select * from ( (select column_name from information_schema.columns where table_name = 'my_table' and table_schema = 'my_schema' order by ordinal_position) union all (select * // potentially complex SELECT statement with WHERE, ORDER BY, GROUP BY etc. from my_table)) as tbl into outfile '/path/outfile' fields terminated by ',' optionally enclosed by '"' escaped by '\\' lines terminated by '\n'; 

où les deux dernières lignes font la sortie csv.

Notez que cela peut être lent si my_table est très volumineux.

Puisque la fonctionnalité ‘include-headers’ ne semble pas encore être intégrée, et que la plupart des “solutions” ici doivent taper les noms des colonnes manuellement, et / ou ne prennent même pas en compte les jointures, je vous recommande de contourner le problème .

  • La meilleure alternative que j’ai trouvée jusqu’à présent est d’utiliser un outil décent (j’utilise HeidiSQL ).
    Mettez votre demande, sélectionnez la grid, cliquez avec le bouton droit de la souris et exportez dans un fichier. Il a toutes les options nécessaires pour une exportation propre, et devrait gérer la plupart des besoins.

  • Dans la même idée, l’approche de user3037511 fonctionne bien et peut être facilement automatisée .
    Lancez simplement votre requête avec une ligne de commande pour obtenir vos en-têtes. Vous pouvez obtenir les données avec un SELECT INTO OUTFILE … ou en exécutant votre requête sans la limite, à vous de choisir.

    Notez que la redirection de sortie vers un fichier fonctionne comme un charme sous Linux ET Windows.

Cela me donne envie de souligner que 80% du temps, quand je veux utiliser SELECT FROM INFILE ou SELECT INTO OUTFILE, je finis par utiliser quelque chose d’autre à cause de certaines limitations (ici, l’absence d’une “options d’en-têtes”, sur un AWS-RDS, les droits manquants, etc.

Par conséquent, je ne réponds pas exactement à la question de l’op … mais cela devrait répondre à ses besoins 🙂
EDIT: et effectivement répondre à sa question: non
En date du 2017-09-07, vous ne pouvez pas inclure les en-têtes si vous maintenez la commande SELECT INTO OUTFILE : |

Je pense que si vous utilisez un UNION, cela fonctionnera:

 select 'header 1', 'header 2', ... union select col1, col2, ... from ... 

Je ne sais pas comment spécifier les en-têtes avec la syntaxe INTO OUTFILE directement.

En fait, vous pouvez le faire fonctionner même avec un ORDER BY.

Il suffit d’une astuce dans l’instruction order – nous utilisons une instruction case et remplaçons la valeur de l’en-tête par une autre valeur garantie pour être sortingée en premier dans la liste (cela dépend évidemment du type de champ et du sorting de ASC ou DESC)

Disons que vous avez trois champs, name (varchar), is_active (bool), date_something_happens (date) et que vous voulez sortinger les deux autres décroissants:

 select 'name' , 'is_active' as is_active , date_something_happens as 'date_something_happens' union all select name, is_active, date_something_happens from my_table order by (case is_active when 'is_active' then 0 else is_active end) desc , (case date when 'date' then '9999-12-30' else date end) desc 

J’écrivais mon code en PHP, et j’ai eu un peu de mal à utiliser les fonctions de concat et d’union, et je n’utilisais pas non plus de variables SQL, de quelque manière que ce soit, voici mon code:

 //first I connected to the information_scheme DB $headercon=mysqli_connect("localhost", "USERNAME", "PASSWORD", "information_schema"); //took the healders out in a ssortingng (I could not get the concat function to work, so I wrote a loop for it) $headers = ''; $sql = "SELECT column_name AS columns FROM `COLUMNS` WHERE table_schema = 'YOUR_DB_NAME' AND table_name = 'YOUR_TABLE_NAME'"; $result = $headercon->query($sql); while($row = $result->fetch_row()) { $headers = $headers . "'" . $row[0] . "', "; } $headers = substr("$headers", 0, -2); // connect to the DB of interest $con=mysqli_connect("localhost", "USERNAME", "PASSWORD", "YOUR_DB_NAME"); // export the results to csv $sql4 = "SELECT $headers UNION SELECT * FROM YOUR_TABLE_NAME WHERE ... INTO OUTFILE '/output.csv' FIELDS TERMINATED BY ','"; $result4 = $con->query($sql4); 

J’ai rencontré un problème similaire lors de l’exécution de la requête mysql sur de grandes tables dans NodeJS. L’approche que j’ai suivie pour inclure les en-têtes dans mon fichier CSV est la suivante

  1. Utilisez la requête OUTFILE pour préparer un fichier sans en-tête

      SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM [TABLE_NAME] 
  2. Récupère les en-têtes de colonne pour la table utilisée au point 1

      select GROUP_CONCAT(CONCAT(\"\",COLUMN_NAME,\"\")) as col_names from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = [TABLE_NAME] AND TABLE_SCHEMA = [DATABASE_NAME] ORDER BY ORDINAL_POSITION 
  3. Ajouter les en-têtes de colonne au fichier créé à l’étape 1 à l’aide du package npm de fichiers prépend

L’exécution de chaque étape était contrôlée à l’aide de promesses dans NodeJS.

un exemple tiré de mon capteur de nom de table contenant des colonnes (id, time, unit)

 select ('id') as id, ('time') as time, ('unit') as unit UNION ALL SELECT * INTO OUTFILE 'C:/Users/User/Downloads/data.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM sensor 

Voici un moyen d’obtenir les titres des en-têtes des noms de colonne de manière dynamic.

 /* Change table_name and database_name */ SET @table_name = 'table_name'; SET @table_schema = 'database_name'; SET @default_group_concat_max_len = (SELECT @@group_concat_max_len); /* Sets Group Concat Max Limit larger for tables with a lot of columns */ SET SESSION group_concat_max_len = 1000000; SET @col_names = ( SELECT GROUP_CONCAT(QUOTE(`column_name`)) AS columns FROM information_schema.columns WHERE table_schema = @table_schema AND table_name = @table_name); SET @cols = CONCAT('(SELECT ', @col_names, ')'); SET @query = CONCAT('(SELECT * FROM ', @table_schema, '.', @table_name, ' INTO OUTFILE \'/tmp/your_csv_file.csv\' FIELDS ENCLOSED BY \'\\\'\' TERMINATED BY \'\t\' ESCAPED BY \'\' LINES TERMINATED BY \'\n\')'); /* Concatenates column names to query */ SET @sql = CONCAT(@cols, ' UNION ALL ', @query); /* Resets Group Contact Max Limit back to original value */ SET SESSION group_concat_max_len = @default_group_concat_max_len; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 
  SELECT 'ColName1', 'ColName2', 'ColName3'
 UNION ALL
 SELECT ColName1, ColName2, ColName3
     FROM YourTable
     INTO OUTFILE 'c: \\ datasheet.csv' CHAMPS TERMINÉS PAR ',' OPTIONALLY ENCLOSED BY '"' LIGNES TERMINÉES PAR '\ n'