Como exportar um arquivo CSV da linha de comando do MySQL

Arquivos de valores separados por vírgula (CSV) são uma forma de transferir dados entre aplicativos. Bancos de dados como MySQL e software de planilha como Excel oferecem suporte à importação e exportação por meio de CSV, portanto, você pode usar arquivos CSV para trocar dados entre os dois.

Os arquivos CSV são em texto simples, portanto, são naturalmente leves e fáceis de exportar do MySQL.

Do servidor de banco de dados

Se você tiver acesso ao servidor em que o MySQL está sendo executado, pode exportar uma seleção com o comando INTO OUTFILE.

 SELECT id, coluna1, coluna2 FROM tabela INTO OUTFILE '/tmp/mysqlfiles/table. csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\ n' 

Isso irá gerar um arquivo CSV para /tmp/mysqlfiles/table. csv, ou onde quer que você o tenha configurado. Você precisará certificar-se de que o usuário que está executando o MySQL (geralmente mysql ou root) possui propriedade e acesso de gravação ao diretório.

Você também precisa se certificar de que a configuração secure_file_priv permite que o MySQL acesse esse diretório. Isso, por padrão, bloqueia o acesso de leitura e gravação de consultas SQL. Isto é uma coisa boa; se o seu código for vulnerável à injeção de SQL, qualquer invasor em potencial terá acesso apenas ao MySQL, e não ao resto do sistema de arquivos.

Publicidade

Você pode colocar diretórios específicos na lista de permissões editando seu arquivo de configuração do MySQL (geralmente localizado em /etc/my. cnf) para incluir:

 [mysqld] secure-file-priv = "/ tmp / mysqlfiles" 

O que permitirá ao MySQL ler e gravar em / tmp / mysqlfiles / (que você &’ terá que criar com mkdir). Depois que o MySQL puder exportar arquivos, você poderá executar a consulta e gerar arquivos CSV.

Com a configuração ENCLOSED BY, as vírgulas terão escape adequado, por exemplo:

 "3", "Escape, este", "também, este" 

Que você pode pegar e importar diretamente para qualquer programa de planilha ou outro software.

Lembre-se de que o arquivo CSV exportado não inclui cabeçalhos de coluna, mas as colunas estarão na mesma ordem que a instrução SELECT. Além disso, os valores nulos serão exportados como \ N, que é o comportamento esperado, mas se você &’ desejar alterar isso, pode modificar a seleção envolvendo ifnull (campo, "") em torno de seus campos em sua instrução SELECT.

Da linha de comando do MySQL

Se você só tiver acesso de linha de comando à instância do MySQL, e não ao próprio servidor (como quando ele não é gerenciado por você, no caso do Amazon RDS), o problema é um pouco mais complicado . Embora você possa usar FIELDS TERMINATED BY ',' no servidor para gerar uma lista separada por vírgulas, a CLI do MySQL se separará com guias por padrão.

Publicidade

Basta inserir uma consulta na linha de comando e canalizá-la para um arquivo:

 mysql -u root -e "select * from database;" > output. tsv 

Como a saída do MySQL é separada por tabulações, isso é chamado de arquivo TSV, para “ valores separados por tabulação, ” e pode funcionar no lugar do arquivo CSV em alguns programas, como importações de planilhas. Mas não é um arquivo CSV e convertê-lo em um é complicado.

Você poderia simplesmente substituir cada guia por uma vírgula, o que funcionaria, mas faria com que falhasse se houvesse vírgulas nos dados de entrada. Se você estiver 100% certo de que não há vírgulas em seu arquivo TSV (verifique com grep), você pode substituir as guias por sed:

 sed "s / \ t /, / g" output. tsv > output. csv 

Mas se houver vírgulas em seus dados, você terá que usar um regex muito mais longo:

 sed "s / '/ \' /; s / \ t / \", \ "/ g; s / ^ / \" /; s / $ / \ "/; s / \ n // g" output. tsv > output. csv 

Isso escapará corretamente os campos com aspas, o que resolverá o problema de vírgula.

Nota: o caractere de tabulação \ t não é padrão. No macOS e no BSD, ele não está disponível, o que causa uma confusão de todas as letras minúsculas “ t ” fazendo com que o sed insira vírgulas erradas. Para resolver isso, você precisará usar um caractere de tabulação literal no lugar de \ t:

 sed "s / /, / g" output. tsv > output. csv 

Publicidade

Se os seus dados de entrada contiverem guias, você está sem sorte e terá que gerar um arquivo CSV com uma linguagem de script.

Faça isso manualmente com uma linguagem de programação real

O MySQL (e a maioria dos bancos de dados) são projetados para interagir, então provavelmente você já tem algum tipo de linguagem de programação conectada ao MySQL. A maioria das linguagens também pode gravar no disco, então você pode criar seus próprios scripts de saída CSV lendo campos do banco de dados diretamente, escapando-os corretamente e gravando um arquivo delimitado por vírgulas.

Um exemplo em Python.