MySQL - Gerando arquivos CSV incluindo cabeçalho de colunas da tabela

Olá,

No artigo de Janeiro/2019 eu demonstrei como carregar um arquivo CSV para uma tabela no MySQL. Agora irei demonstrar como gerar um arquivo CSV a partir de uma tabela, incluindo também as colunas da tabela como cabeçalho.

mysql> select * from customer;
+------+------+
| id   | name |
+------+------+
|    1 | Joe  |
|    2 | Jack |
|    3 | Zack |
+------+------+
3 rows in set (0.00 sec)

Utilizarei o template abaixo para geração de um arquivo CSV.

SET @table_name = 'nome_da_tabela';
SET @table_schema = 'nome_do_banco_de_dados';
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/nome_do_arquivo.csv\'
  FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\')');
SET @sql = CONCAT(@cols, ' UNION ALL ', @query);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Segue exemplo abaixo na qual irei gerar um arquivo CSV da tabela customer.

mysql> SET @table_name = 'customer';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @table_schema = 'bd01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION group_concat_max_len = 1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> 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);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cols = CONCAT('(SELECT ', @col_names, ')');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @query = CONCAT('(SELECT * FROM ', @table_schema, '.', @table_name,
    ->   ' INTO OUTFILE \'/tmp/customer.csv\'
    '>   FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\')');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@cols, ' UNION ALL ', @query);
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 4 rows affected (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> system cat /tmp/customer.csv;
"id","name"
"1","Joe"
"2","Jack"
"3","Zack"