Includere le intestazioni quando si utilizza SELECT INTO OUTFILE?

È ansible includere le intestazioni in qualche modo quando si utilizza MySQL INTO OUTFILE ?

Dovresti codificare queste intestazioni da solo. Qualcosa di simile a:

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

La soluzione fornita da Joe Steanelli funziona, ma creare un elenco di colonne è scomodo quando sono coinvolte dozzine o centinaia di colonne. Ecco come ottenere l’elenco delle colonne della tabella my_table in 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 

Ora puoi copiare e incollare la riga risultante come prima affermazione nel metodo di Joe.

Per la selezione complessa con ORDINARE io uso quanto segue:

 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'; 

Questo ti consentirà di avere colonne ordinate e / o un 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'; 

Faccio semplicemente 2 query, in primo luogo per ottenere l’output della query (limite 1) con i nomi delle colonne (nessun hardcode, nessun problema con Joins, Ordina per, nomi di colonne personalizzate, ecc.) E in secondo luogo per creare la query stessa e combinare i file in un unico CSV file:

 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 

Puoi usare l’istruzione preparata con la risposta di lucek ed esportare dynamicmente la tabella con il nome delle colonne in 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; 

Grazie lucek.

Questo è un trucco alternativo se hai familiarità con Python o R, e il tuo tavolo può adattarsi alla memoria.

Importa la tabella SQL in Python o R, quindi esporta da lì come CSV e otterrai i nomi delle colonne e i dati.

Ecco come lo faccio usando R, richiede la libreria 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') 

È un po 'un trucco, ma ho trovato che questa soluzione è stata rapida quando il mio numero di colonne era troppo lungo per utilizzare il metodo concat sopra. Nota: R aggiungerà una colonna 'row.names' all'inizio del CSV, quindi ti consigliamo di eliminarla se devi fare affidamento sul CSV per ricreare la tabella.

Quindi, se tutte le colonne di my_table sono di tipo carattere , possiamo combinare insieme le risposte migliori (di Joe, Matt e evilguc) per ottenere l’intestazione aggiunta automaticamente in una query SQL “semplice”, ad es.

 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'; 

dove l’ultimo paio di linee rende l’output CSV.

Nota che questo potrebbe essere lento se my_table è molto grande.

Dal momento che la funzionalità “include-headers” non sembra essere ancora incorporata, e la maggior parte delle “soluzioni” qui devono digitare manualmente i nomi delle colonne e / o non prendono nemmeno in considerazione i join, ti consiglio di aggirare il problema

  • L’alternativa migliore che ho trovato finora è l’utilizzo di uno strumento decente (utilizzo HeidiSQL ).
    Inserisci la tua richiesta, seleziona la griglia, fai clic con il pulsante destro del mouse ed esporta in un file. Ha tutte le opzioni necessarie per un’esportazione pulita, ans dovrebbe gestire la maggior parte delle esigenze.

  • Nella stessa idea, l’approccio di user3037511 funziona bene e può essere facilmente automatizzato .
    Basta lanciare la richiesta con qualche riga di comando per ottenere le intestazioni. Puoi ottenere i dati con SELECT INTO OUTFILE … o eseguendo la tua query senza limiti, a tua scelta.

    Nota che l’output reindirizza a un file funziona come un incantesimo sia su Linux che su Windows.

Questo mi fa venire voglia di evidenziare quell’80% delle volte, quando voglio usare SELECT FROM INFILE o SELECT INTO OUTFILE, mi ritrovo usando qualcos’altro a causa di alcune limitazioni (qui, l’assenza di “opzioni di intestazioni”, su un AWS-RDS, i diritti mancanti e così via.)

Quindi, non rispondo esattamente alla domanda dell’opico … ma dovrebbe rispondere ai suoi bisogni 🙂
EDIT: e per rispondere in realtà alla sua domanda: no
A partire dal 2017-09-07, non è ansible includere le intestazioni se si mantiene il comando SELECT INTO OUTFILE : |

Penso che se usi UNION funzionerà:

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

Non conosco un modo per specificare direttamente le intestazioni con la syntax INTO OUTFILE.

In realtà puoi farlo funzionare anche con ORDER BY.

Solo bisogno di alcuni trucchi nell’ordine per dichiarazione – usiamo un’istruzione case e sostituiamo il valore dell’intestazione con qualche altro valore che è garantito per ordinare prima nella lista (ovviamente questo dipende dal tipo di campo e se si sta ordinando ASC o DESC)

Supponiamo che tu abbia tre campi, nome (varchar), is_active (bool), date_something_happens (date), e vuoi ordinare i secondi due discendenti:

 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 

Stavo scrivendo il mio codice in PHP, e ho avuto un po ‘di problemi nell’usare le funzioni concat e union, e inoltre non ho usato le variabili SQL, in nessun modo ho potuto farlo funzionare, ecco il mio codice:

 //first I connected to the information_scheme DB $headercon=mysqli_connect("localhost", "USERNAME", "PASSWORD", "information_schema"); //took the healders out in a string (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); 

Ho affrontato un problema simile durante l’esecuzione della query mysql su tabelle di grandi dimensioni in NodeJS. L’approccio che ho seguito per includere le intestazioni nel mio file CSV è il seguente

  1. Utilizzare la query OUTFILE per preparare il file senza intestazioni

      SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM [TABLE_NAME] 
  2. Recupera intestazioni di colonna per la tabella utilizzata nel punto 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. Aggiungi le intestazioni delle colonne al file creato nel passaggio 1 utilizzando il pacchetto npm del file predefinito

L’esecuzione di ogni passaggio è stata controllata utilizzando le promesse in NodeJS.

un esempio dal mio sensore di nome tabella del database con colonne (id, tempo, 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 

Ecco un modo per ottenere dynamicmente i titoli delle intestazioni dai nomi delle colonne.

 /* 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; 
  SELEZIONA "ColName1", "ColName2", "ColName3"
 UNIONE TUTTI
 SELEZIONA ColName1, ColName2, ColName3
     Da YourTable
     INTO OUTFILE 'c: \\ datasheet.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 'LINEE TERMINATE DA' \ n '