Uwzględniać nagłówki podczas korzystania z SELECT INTO OUTFILE?

Czy można w jakiś sposób dołączyć nagłówki podczas korzystania z MySQL INTO OUTFILE?

Trzeba by było samodzielnie zakodować te nagłówki. Coś jak:

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

Rozwiązanie dostarczone przez Joe Steanelli działa, ale tworzenie listy kolumn jest niewygodne, gdy w grę wchodzą dziesiątki lub setki kolumn. Oto jak uzyskać listę kolumn tabeli my_table w 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

Teraz możesz skopiować i wkleić wynikowy wiersz jako pierwszą instrukcję w metodzie Joe.

Dla złożonego wyboru z ORDER BY używam:

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

Możesz użyć przygotowanego zestawienia z odpowiedzią lucka i dynamicznie wyeksportować tabelę z nazwami kolumn w 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;

Dziękuję Lucku.

Umożliwi to uporządkowanie kolumn i / lub ograniczenie

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

Po prostu wykonuję 2 zapytania, najpierw, aby uzyskać wynik zapytania (limit 1) z nazwami kolumn (bez kodu stałego, żadnych problemów z połączeniami, kolejnością, niestandardowymi nazwami kolumn itp.), A po drugie, aby wykonać zapytanie i połączyć pliki w jeden plik CSV plik:

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

Z podobnym problemem spotkałem się podczas wykonywania zapytania mysql na dużych tabelach w NodeJS. Podejście, które zastosowałem, aby dołączyć nagłówki do mojego pliku CSV, jest następujące

1. Użyj zapytania OUTFILE, aby przygotować plik bez nagłówków

       SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED 
       BY '\"' LINES TERMINATED BY '\n' FROM [TABLE_NAME]

2. Pobierz nagłówki kolumn dla tabeli użytej w punkcie 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. Dołącz nagłówki kolumn do pliku utworzonego w kroku 1 przy użyciu pakietu npm przedpend-file

Wykonanie każdego kroku było kontrolowane za pomocą obietnic w NodeJS.

Jest to alternatywny kod, jeśli znasz język Python lub R, a Twój stół mieści się w pamięci.

Zaimportuj tabelę SQL do języka Python lub R, a następnie wyeksportuj stamtąd jako plik CSV, a otrzymasz nazwy kolumn oraz dane.

Oto jak to robię używając R, wymaga biblioteki 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')

To trochę oszustwo, ale stwierdziłem, że było to szybkie obejście, gdy moja liczba kolumn była zbyt długa, aby użyć powyższej metody konkatacji. Uwaga: R doda kolumnę „row.names” na początku pliku CSV, więc zechcesz ją usunąć, jeśli musisz polegać na pliku CSV w celu odtworzenia tabeli.

Tak więc, jeśli wszystkie kolumny my_tablesą typu znakowego , możemy połączyć najlepsze odpowiedzi (Joe, Matt i evilguc) razem, aby nagłówek został dodany automatycznie w jednym „prostym” zapytaniu SQL, np.

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

gdzie ostatnie kilka wierszy tworzy wynik csv.

Zauważ, że może to być powolne, jeśli my_tablejest bardzo duże.

Myślę, że jeśli użyjesz UNION, to zadziała:

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

Nie znam sposobu bezpośredniego określenia nagłówków za pomocą składni INTO OUTFILE.

Właściwie możesz sprawić, by działało nawet z ORDER BY.

Potrzebuje tylko trochę sztuczek w kolejności według instrukcji - używamy instrukcji case i zastępujemy wartość nagłówka inną wartością, która gwarantuje pierwsze sortowanie na liście (oczywiście zależy to od typu pola i tego, czy sortujesz ASC, czy DESC)

Załóżmy, że masz trzy pola, name (varchar), is_active (bool), date_something_happens (date) i chcesz posortować dwa drugie malejąco:

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

Ponieważ funkcjonalność „nagłówków dołączania” nie wydaje się być jeszcze wbudowana, a większość „rozwiązań” wymaga ręcznego wpisywania nazw kolumn i / lub nawet nie bierze pod uwagę złączeń, radziłbym obejść problem .

• Najlepszą alternatywą, jaką do tej pory znalazłem, jest użycie przyzwoitego narzędzia (używam HeidiSQL ).
  Umieść swoje żądanie, wybierz siatkę, kliknij prawym przyciskiem myszy i wyeksportuj do pliku. Posiada wszystkie niezbędne opcje do czystego eksportu i powinien obsługiwać większość potrzeb.

• W tym samym pomyśle podejście user3037511 działa dobrze i można je łatwo zautomatyzować .
  Po prostu uruchom swoje żądanie za pomocą wiersza poleceń, aby uzyskać nagłówki. Możesz uzyskać dane za pomocą SELECT INTO OUTFILE ... lub uruchamiając zapytanie bez ograniczeń, do wyboru.

  Zauważ, że przekierowanie wyjścia do pliku działa jak urok zarówno w systemie Linux, jak i Windows.

To sprawia, że ​​chcę podkreślić, że w 80% przypadków, gdy chcę użyć SELECT FROM INFILE lub SELECT INTO OUTFILE, w końcu używam czegoś innego z powodu pewnych ograniczeń (tutaj brak opcji nagłówków, na AWS-RDS, brakujące prawa i tak dalej.)

Stąd, nie dokładnie odpowiedzieć na PO za pytanie ... ale powinien odpowiadać jego potrzeb :)
EDIT: i rzeczywiście odpowiedzieć na jego pytanie: no
jak z 2017-09-07, po prostu nie może zawierać nagłówki jeśli ciebie trzymaj się polecenia SELECT INTO OUTFILE : |

przykład z czujnika nazwy tabeli w mojej bazie danych z kolumnami (id, czas, jednostka)

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

Pisałem swój kod w PHP i miałem trochę problemów z używaniem funkcji concat i union, a także nie korzystałem ze zmiennych SQL, jakikolwiek sposób, w jaki go uruchomiłem, oto mój kod:

//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);

Oto sposób na dynamiczne pobieranie tytułów nagłówków z nazw kolumn.

/* 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;

Chciałbym dodać do odpowiedzi udzielonej przez Sangama Belose. Oto jego kod:

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

Jeśli jednak nie ustawiłeś swojego "secure_file_priv"w zmiennych, może to nie działać. W tym celu sprawdź folder ustawiony dla tej zmiennej przez:

SHOW VARIABLES LIKE "secure_file_priv"

Wynik powinien wyglądać następująco:

mysql> show variables like "%secure_file_priv%";
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Możesz zmienić tę zmienną lub zmienić zapytanie, aby wyprowadzało plik do pokazanej domyślnej ścieżki.

Sam MySQL nie wystarczy, aby zrobić to po prostu. Poniżej znajduje się skrypt PHP, który wyświetla kolumny i dane w formacie CSV.

Wprowadź nazwę bazy danych i tabele u góry.

<?php

set_time_limit( 24192000 );
ini_set( 'memory_limit', '-1' );
setlocale( LC_CTYPE, 'en_US.UTF-8' );
mb_regex_encoding( 'UTF-8' );

$dbn = 'DB_NAME';
$tbls = array(
'TABLE1',
'TABLE2',
'TABLE3'
);

$db = new PDO( 'mysql:host=localhost;dbname=' . $dbn . ';charset=UTF8', 'root', 'pass' );

foreach( $tbls as $tbl )
{
    echo $tbl . "\n";
    $path = '/var/lib/mysql/' . $tbl . '.csv';

    $colStr = '';
    $cols = $db->query( 'SELECT COLUMN_NAME AS `column` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "' . $tbl . '" AND TABLE_SCHEMA = "' . $dbn . '"' )->fetchAll( PDO::FETCH_COLUMN );
    foreach( $cols as $col )
    {
        if( $colStr ) $colStr .= ', ';
        $colStr .= '"' . $col . '"';
    }

    $db->query(
    'SELECT *
    FROM
    (
        SELECT ' . $colStr . '
        UNION ALL
        SELECT * FROM ' . $tbl . '
    ) AS sub
    INTO OUTFILE "' . $path . '"
    FIELDS TERMINATED BY ","
    ENCLOSED BY "\""
    LINES TERMINATED BY "\n"'
    );

    exec( 'gzip ' . $path );

    print_r( $db->errorInfo() );
}

?>

Będziesz potrzebował tego katalogu, do którego chcesz wyprowadzać. MySQL musi mieć możliwość zapisu w katalogu.

$path = '/var/lib/mysql/' . $tbl . '.csv';

Możesz edytować opcje eksportu CSV w zapytaniu:

INTO OUTFILE "' . $path . '"
FIELDS TERMINATED BY ","
ENCLOSED BY "\""
LINES TERMINATED BY "\n"'

Na końcu jest wywołanie exec do GZipa CSV.

WYBIERZ „ColName1”, „ColName2”, „ColName3”
UNIA WSZYSTKO
Wybierz opcję ColName1, ColName2, ColName3
    Z YourTable
    INTO OUTFILE 'c: \\ datasheet.csv' POLA ZAKOŃCZONE PRZEZ ',' OPCJONALNIE ZAŁĄCZONE PRZEZ '"' LINIE ZAKOŃCZONE PRZEZ '\ n'