Resumo : neste tutorial você aprenderá diversas técnicas para exportar uma tabela MySQL para um arquivo CSV.
O CSV significa valores separados por vírgula. O formato de arquivo CSV é frequentemente usado para trocar dados entre aplicativos como Microsoft Excel, Open Office, Google Docs e assim por diante.
Ter dados do banco de dados MySQL em formato de arquivo CSV será útil porque você pode analisar e formatar os dados da maneira que desejar.
MySQL fornece uma maneira fácil de exportar o resultado da consulta para um arquivo CSV que reside no servidor de banco de dados.
1) Exportando uma tabela para um arquivo CSV usando a instrução SELECT… INTO OUTFILE
Ilustraremos como exportar a tabela de pedidos do classicmodels banco de dados de exemplo para um arquivo CSV localizado no servidor MySQL.
Primeiro, abra o Prompt de Comando no Windows ou Terminal em sistemas do tipo Unix e conecte-se ao servidor MySQL:
mysql -u root -pLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, altere o banco de dados atual para classicmodels:
USE classicmodels;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, mostre o valor da secure_file_privvariável:
SHOW VARIABLES LIKE "secure_file_priv";Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )No servidor Windows, a saída é semelhante a esta:
+------------------+------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.02 sec)Linguagem de código: JavaScript ( javascript )No Ubuntu, a saída será semelhante a:
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)Linguagem de código: JavaScript ( javascript )A secure_file_privconfiguração indica o diretório onde você tem permissão para armazenar o arquivo de saída.
Por fim, recupere os dados da orderstabela e exporte o conjunto de resultados para o orders.csvarquivo:
SELECT * FROM orders
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orders.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Observe que o orders.csvarquivo não deve existir no diretório C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/. Caso contrário, a instrução emitirá um erro:
ERROR 1086 (HY000): File 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orders.csv' already existsLinguagem de código: JavaScript ( javascript )Como funciona:
Primeiro, recupere os dados da orderstabela:
SELECT * FROM ordersLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, especifique o arquivo de saída CSV (orders.csv) armazenado no diretório permitido (C:/ProgramData/MySQL/MySQL Server 8.0/Uploads) na cláusula INTO OUTFILE:
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orders.csv' Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Observe que você precisa substituir a barra invertida \pela barra /no diretório do Windows para que funcione.
Terceiro, defina o formato do arquivo de saída como CSV especificando como os campos e linhas na saída são formatados:
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui está o detalhe de cada opção:
- FIELDS ENCLOSED BY ‘”’: especifica que cada campo no arquivo de saída será colocado entre aspas duplas (
"). - TERMINATED BY ‘,’: especifica que os campos no arquivo de saída são separados (terminados) por uma vírgula (
,). - ESCAPED BY ‘”’: especifica o caractere usado para escapar de caracteres especiais. Neste caso, são aspas duplas (
"). - LINHAS TERMINADAS POR ‘\r\n’: especifica que cada linha no arquivo de saída é finalizada por um retorno de carro (\r) seguido por um caractere de nova linha (\n), que é uma sequência de final de linha comum em ambientes Windows.
Aqui está o trecho do orders.csvarquivo:
"10100","2003-01-06","2003-01-13","2003-01-10","Shipped","N,"363"
"10101","2003-01-09","2003-01-18","2003-01-11","Shipped","Check on availability.","128"
"10102","2003-01-10","2003-01-18","2003-01-14","Shipped","N,"181"
...Linguagem de código: PHP ( php )Adicionando um carimbo de data/hora ao arquivo de saída CSV
Freqüentemente, você precisa exportar dados para um arquivo CSV cujo nome contém um carimbo de data/hora que indica quando o arquivo foi criado. Para conseguir isso, você precisa usar uma instrução preparada pelo MySQL .
Os comandos a seguir exportam a orderstabela inteira para um arquivo CSV com um carimbo de data/hora como parte do nome do arquivo:
set @ts = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
set @filename = concat(replace(@@secure_file_priv,'\\','/'), 'orders', @ts, '.csv');
select @filename;
set @cmd = CONCAT("SELECT * FROM orders INTO OUTFILE '",
@filename,
"' FIELDS ENCLOSED BY '\"' TERMINATED BY ',' ESCAPED BY '\"'",
" LINES TERMINATED BY '\r\n';");
prepare statement from @cmd;
execute statement;
deallocate prepare statement;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nestas declarações:
- Primeiro, construa uma consulta com o carimbo de data/hora atual como parte do nome do arquivo.
- Segundo, prepare a instrução para execução usando uma
PREPAREinstrução. - Terceiro, execute a instrução usando o
EXECUTEcomando.
Criando um procedimento armazenado que exporta um resultado de consulta para um arquivo CSV
Para simplificar o processo de exportação do conjunto de resultados de uma consulta para um arquivo CSV, podemos criar um procedimento armazenado que gera um arquivo CSV a partir do conjunto de resultados de uma consulta:
DELIMITER //
CREATE PROCEDURE ExportToCSV(
IN query_text TEXT,
IN filename VARCHAR(255)
)
BEGIN
DECLARE ts VARCHAR(20);
DECLARE cmd VARCHAR(1000);
-- Construct the full file name
SET filename = CONCAT(REPLACE(@@secure_file_priv, '\\', '/'), filename);
-- Construct the SQL command
SET @cmd = CONCAT(
query_text,
" INTO OUTFILE '", filename,
"' FIELDS ENCLOSED BY '\"' TERMINATED BY ',' ESCAPED BY '\"'",
" LINES TERMINATED BY '\r\n';"
);
-- Prepare and execute the statement
PREPARE statement FROM @cmd;
EXECUTE statement;
DEALLOCATE PREPARE statement;
SELECT filename;
END //
DELIMITER ;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Por exemplo, você pode exportar a employeestabela para employees.csvarquivo da seguinte maneira:
CALL ExportToCSV(
'select * from employees',
'employees.csv'
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+--------------------------------------------------------------+
| filename |
+--------------------------------------------------------------+
| C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employees.csv |
+--------------------------------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)Linguagem de código: JavaScript ( javascript )Você pode recuperar o arquivo CSV na saída: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employees.csv
Adicionando cabeçalho de coluna ao arquivo de saída CSV
Seria conveniente se o arquivo CSV contivesse a primeira linha como cabeçalho das colunas para que o arquivo fosse mais compreensível.
Para adicionar os títulos das colunas, você pode usar um operador UNION da seguinte maneira:
SELECT 'Order no', 'Order date', 'Required Date', 'Shipped Date', 'Status', 'Comments', 'Customer No'
UNION
SELECT * FROM orders
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orders_heading.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Mapeando NULL para outros valores
Se os valores no conjunto de resultados contiverem valores NULL , o arquivo de destino conterá "Nem vez de NULL.
Para corrigir esse problema, você precisa substituir o NULLvalor por outro valor, por exemplo, não disponível ( N/A) usando a função IFNULL conforme mostrado na consulta a seguir:
SELECT 'Order no', 'Order date', 'Required Date', 'Shipped Date', 'Status', 'Comments', 'Customer No'
UNION
SELECT orderNumber, orderDate, requiredDate, IFNULL(shippedDate,"N/A"), status, IFNULL(comments, "N/A"), customerNumber
FROM orders
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orders_full.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Na SELECTcláusula, substituímos NULLas colunas shippedDatee commentspela string "N/A".
2) Exportando dados para arquivo CSV usando MySQL Workbench
Se você não tiver acesso ao servidor MySQL para recuperar o arquivo CSV exportado, poderá usar o MySQL Workbench para exportar o conjunto de resultados de uma consulta para um arquivo CSV em seu computador local da seguinte maneira:
- Primeiro, execute uma consulta para obter seu conjunto de resultados.
- Segundo, no painel de resultados, clique em “exportar conjunto de registros para um arquivo externo”. Observe que um conjunto de resultados também é conhecido como conjunto de registros.
- Terceiro, uma nova caixa de diálogo é exibida e solicita um nome e formato de arquivo. Insira o nome do arquivo, escolha CSV como formato de arquivo e clique no botão Salvar.
O arquivo CSV exportado pelo MySQL Workbench oferece suporte a títulos de colunas, NULLvalores e outros recursos.
Resumo
- Use a
SELECT ... INTO OUTFILEinstrução para exportar uma tabela para um arquivo CSV no servidor MySQL. - Use o MySQL Workbench para exportar uma tabela para um arquivo CSV em seu computador local.
