Включить заголовки при использовании SELECT INTO OUTFILE?

решение, предоставленное Джо Стинелли, работает, но составление списка столбцов неудобно, когда задействованы десятки или сотни столбцов. Вот как получить список столбцов таблицы таблицы my_table на 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

теперь вы можете скопировать и вставить полученную строку в качестве первого оператора в методе Джо.

для сложного выбора с порядком я использую следующее:

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

Это позволит вам упорядочить столбцы и / или ограничить

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

Я просто делаю 2 запроса, во-первых, чтобы получить вывод запроса (ограничение 1) с именами столбцов (без жесткого кода, без проблем с соединениями, порядком, пользовательскими именами столбцов и т. д.), а во-вторых, чтобы сделать сам запрос и объединить файлы в один файл CSV:

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

вы можете использовать подготовленный оператор с ответом lucek и экспортировать динамически таблицу с именем столбцов в 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;

спасибо lucek.

Это альтернативный чит если вы знакомы с Python или R, и ваш стол может поместиться в память.

импортируйте таблицу SQL в Python или R, а затем экспортируйте оттуда как CSV, и вы получите имена столбцов, а также данные.

вот как я это делаю с помощью R, требуется библиотека 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')

это немного чит, но я обнаружил, что это было быстрое решение, когда мое количество столбцов было слишком длинным, чтобы использовать метод concat выше. Отмечать: Р будет добавлена строка.столбец имен в начале CSV, поэтому вы захотите удалить его, если вам нужно полагаться на CSV для воссоздания таблицы.

и если все столбцы my_table являются символьным типом данных, мы можем объединить верхние ответы (Джо, Мэтт и evilguc) вместе, чтобы автоматически добавить заголовок в один "простой" SQL-запрос, например

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

где последние несколько строк делают вывод csv.

обратите внимание, что это может быть медленным, если my_table очень большой.

поскольку функциональность "include-headers", похоже, еще не встроена, и большинство "решений" здесь нужно вводить имена столбцов вручную и / или даже не учитывать соединения, я бы рекомендовал обойти проблему.

• лучшая альтернатива, которую я нашел до сих пор, - это использование достойного инструмента (я использую HeidiSQL).
  поместите свой запрос, выберите сетку, просто щелкните правой кнопкой мыши и экспортировать в файл. Он получил все необходимые опции для чистый экспорт, ans должен обрабатывать большинство потребностей.

• в той же идее, подход user3037511 отлично работает, и может быть легко автоматизируется.
  просто запустите свой запрос с помощью командной строки чтобы сделать свои заголовки. Вы можете получить данные с помощью SELECT в OUTFILE... или, запустив свой запрос без ограничения, ваш выбор.

  обратите внимание, что перенаправление вывода в файл работает как шарм на Linux и Окна.

Это заставляет меня подчеркнуть, что 80% времени, когда я хочу использовать SELECT FROM INFILE или SELECT INTO OUTFILE, я в конечном итоге использую что-то другое из-за некоторых ограничений (здесь, отсутствие "заголовков опций", на AWS-RDS, отсутствующие права и так далее.)

следовательно, я точно не отвечаю на ОП вопрос... но она должна ответить ему должен :)
правка : и на самом деле ответить на его вопрос : нет
начиная с 2017-09-07, вы просто не можете включать заголовки, если вы придерживаетесь команды SELECT INTO OUTFILE :/

я столкнулся с подобной проблемой при выполнении запроса mysql на больших таблицах в NodeJS. Подход, которому я следовал, чтобы включить заголовки в мой CSV-файл, выглядит следующим образом

1. используйте OUTFILE запрос для подготовки файла без заголовков

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

2. извлеките заголовки столбцов для таблицы, используемой в пункте 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. добавьте заголовки столбцов в файл, созданный на шаге 1 с помощью сохраняем-файл npm пакет

выполнение каждого шага контролировалось с помощью обещаний в NodeJS.

Я думаю, что если вы используете Союз, он будет работать:

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

Я не знаю способа указать заголовки с синтаксисом INTO OUTFILE напрямую.

на самом деле вы можете заставить его работать даже с заказом.

просто нужна некоторая хитрость в операторе order by-мы используем оператор case и заменяем значение заголовка некоторым другим значением, которое гарантированно сортируется первым в списке (очевидно, это зависит от типа поля и от того, сортируете ли вы ASC или DESC)

допустим, у вас есть три поля: name (varchar), is_active (bool), date_something_happens (date), и вы хотите отсортировать вторые два по убыванию:

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

я писал свой код на PHP, и у меня было немного проблем с использованием функций concat и union, а также не использовал переменные SQL, любыми способами я получил его для работы, вот мой код:

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

пример из моей базы данных имя таблицы датчик С colums (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

вот способ получить заголовки заголовков из имен столбцов динамически.

/* 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' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'