10 Exemplos de Carregar Dados em Ficheiro MySQL para Carregar Dados em Ficheiro de Texto para Tabelas

LOAD DATA INFILE 'employee1.txt' INTO TABLE employee;

Nota: No exemplo acima, o comando assume que o ficheiro employee1.txt localizado sob o directório da base de dados. Por exemplo, se estiver a executar o comando acima na base de dados dogeekstuff, então coloque o ficheiro sob: /var/lib/mysql/thegeekstuff/

A seguinte será a saída do comando acima.

Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

No exemplo acima:

• A primeira linha “Query OK” diz que a consulta foi executada sem qualquer erro. Também diz que no total de 5 linhas foram carregadas na tabela. Isto também mostra o tempo necessário para carregar os dados do ficheiro de texto para a tabela em segundos.
• A segunda linha mostra o total de linhas carregadas, quantos foram pulados e quantos registos exibiram avisos durante o carregamento.

Após os dados serem carregados, o seguinte é o que veremos na tabela de empregados.

Nota: Se quiser fazer cópias de segurança e restaurar toda a sua base de dados MySQL, use o comando mysqldump.

Carregar Dados Usando a Opção “Campos terminados por”

No exemplo seguinte, no ficheiro de entrada employee2.txt, os valores dos campos são separados por vírgulas.

Para carregar os registos acima para a tabela de empregados, use o seguinte comando.

Durante o carregamento, usando a opção “FIELDS TERMINATED BY”, pode especificar o delimitador de campo por vírgula como mostrado abaixo.

LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',';

Again, use esta opção apenas quando os valores do campo forem separados por qualquer outra coisa que não TAB. Se os campos forem terminados por dois pontos, utilizará a seguinte opção no comando acima:

FIELDS TERMINATED BY ':';

Se for novo no MySQL leia isto: Tutorial do MySQL: Instalar, Criar BD e Tabela, Inserir e Seleccionar Registos

Os seguintes são alguns erros básicos que podem acontecer durante o upload do MySQL

Error 1: Se o ficheiro de texto não estiver localizado sob o directório apropriado, pode obter a seguinte mensagem de erro “ERROR 13 (HY000) Can’t get stat of (Errcode: 2)”.

Também, pode especificar o caminho completo para o ficheiro no comando load data infile como se mostra abaixo. Se o fizer, certifique-se de que o ficheiro pode ser acedido pelo mysql. Caso contrário, altere a propriedade para mysql apropriadamente. Se não, receberá a mensagem de erro de permissão de carregamento de dados no ficheiro.

MariaDB > LOAD DATA INFILE '/data/employee2.txt' INTO TABLE employee;

Error 2: Se não especificar os campos correctos terminados por, então verá alguns problemas no carregamento. Neste exemplo, apenas o primeiro campo “id” foi carregado. O valor de todos os outros campos é NULL. Isto porque, o seguinte comando não especifica o campo terminado por opção, uma vez que o ficheiro de entrada tem vírgula como delimitador de campo.

Carregar Dados Usando a opção “Encerrado por”

No exemplo seguinte, o ficheiro de texto de entrada tem os valores do campo de texto delimitado por aspas duplas. i.e Os valores do nome e do departamento têm aspas duplas à sua volta.

Neste caso, use a opção “Encerrado por” como mostrado abaixo.

LOAD DATA INFILE 'employee3.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"';

O comando acima irá carregar os registos correctamente como mostrado abaixo pelo comando select mysql select:

Por favor note que quando estiver a combinar campos terminados por e campos delimitados por, não tem de usar a palavra-chave “CAMPOS” duas vezes como mostrado abaixo, o que irá exibir a seguinte mensagem de erro:

FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';

O acima mostrará o seguinte erro “ERROR 1064 (42000)”:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS ENCLOSED BY '"'' at line 4

Usando Caracteres de Fuga em Dados de Ficheiro de Texto

Deixe-nos dizer que tem vírgula no valor de um determinado campo.

Por exemplo, no exemplo seguinte, o nome do 2º campo tem o valor neste formato: “firstname, lastname”.

Se carregar o ficheiro acima usando o seguinte comando, verá que ele exibirá “10 avisos”

Os registos também não são carregados correctamente porque existe uma vírgula no valor de um dos campos.

Arquivo correcto: Para resolver o problema acima, use a barra invertida (\) na frente da vírgula no valor do campo do nome, como mostrado abaixo.

O seguinte funcionará desta vez sem qualquer erro, pois temos \ como caractere de fuga.

Também pode usar um caractere de fuga diferente, como mostrado abaixo. Neste exemplo, estamos a usar ^ como caracter de escape em vez do defunto \.

Neste caso, use a opção “ESCAPADO POR” como mostrado abaixo.

LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\^'

Por favor note que alguns dos caracteres não podem ser usados como caracter de escape. Por exemplo, se usar % como caracter de fuga, receberá a seguinte mensagem de erro.

LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\%'ERROR 1083 (42000): Field separator argument is not what is expected; check the manual

Carregar Dados Usando a Opção “Linhas terminadas por”

Em vez de ter todos os registos numa linha separada, também os pode ter na mesma linha.

No exemplo seguinte, cada registo é separado por | símbolo.

Para carregar o ficheiro acima, utilize as linhas terminadas por opção como mostrado abaixo.

LOAD DATA INFILE 'employee5.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '|';

O comando acima irá carregar os registos de empregado5.txt como mostrado abaixo.

Os seguintes são alguns pontos a ter em mente:

• Se o ficheiro de entrada vem de uma máquina Windows, então poderá querer utilizar isto: LINHAS TERMINADAS POR ‘\r\n’
• Se estiver a utilizar um ficheiro CSV para carregar dados para a tabela, depois tente uma destas 1) LINHAS TERMINADAS POR ‘\r’ 2) LINHAS TERMINADAS POR ‘\r\n’

Ignorar Prefixo de Linha no Ficheiro de Carregamento Usando a Opção “Iniciar por”

P>Pode também ter algum prefixo para os registos no seu ficheiro de texto de entrada que pode ser ignorado durante o carregamento.

Por exemplo, no seguinte ficheiro empregado6.txt, para o 1º, 2º e 5º registo, temos “Dados:” no início da linha. Pode carregar apenas estes registos ignorando o prefixo da linha.

Para ignorar o prefixo da linha e carregar estes registos, (por exemplo: “Data:” no ficheiro acima), utilize a opção “lines starting by” como se mostra abaixo.

LOAD DATA INFILE 'employee6.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES STARTING BY 'Data:';

O seguinte é a saída do comando acima:

Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

Como vê abaixo, o comando acima carregou apenas os registos que começaram com o prefixo “Data:”. Isto é útil para carregar selectivamente apenas os registos que têm determinado prefixo.

Ignorar linha de cabeçalho do ficheiro Upload

No ficheiro de texto de entrada seguinte, a primeira linha é a linha de cabeçalho, que tem o nome das colunas.

Durante o carregamento, queremos ignorar a primeira linha de cabeçalho lien do ficheiro employee7.txt. Para isso, utilizar a opção IGNORE 1 linhas como mostrado abaixo.

LOAD DATA INFILE 'employee7.txt' INTO TABLE employee FIELDS TERMINATED BY ',' IGNORE 1 LINES;

Como se vê na saída seguinte, embora o ficheiro de entrada tenha 6 linhas, ignorou a 1ª linha (que é a linha de cabeçalho) e carregou as 5 linhas restantes.

Carregar apenas coluna específica (e ignorar outras colunas) de Upload File

No exemplo seguinte, temos valores apenas para três campos. Não temos coluna de departamento neste ficheiro de exemplo.

# cat employee8.txt 100,Thomas,5000200,Jason,5500300,Mayla,7000400,Nisha,9500500,Randy,6000

Para carregar valores do registo de entrada para uma coluna específica na tabela, especificar os nomes das colunas durante a carga de dados no ficheiro, como se mostra abaixo. A última linha no comando seguinte tem os nomes das colunas que devem ser utilizados para carregar os registos do ficheiro de texto de entrada.

LOAD DATA INFILE 'employee8.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, salary);

P>Desde que não especificámos a coluna “dept” no comando acima, veremos que esta coluna é NULL como mostrado abaixo.

Again, tenha em mente que quando não especificar a lista de colunas, o comando esperará que todas as colunas estejam presentes no ficheiro de entrada.

Também, se não especificar a lista de colunas na última linha, obterá erro de sintaxe como mostrado abaixo.

Utilizar variáveis durante o carregamento com a opção “Set”

Para este exemplo, utilizemos o seguinte ficheiro employee2.txt.

Neste exemplo, queremos aumentar o salário em 500 antes de o carregarmos para a tabela. Por exemplo, o salário para Thomas (que é o 1º registo) é de 5000. Mas, durante o carregamento, queremos aumentá-lo em 500 a 5500 e actualizar este valor aumentado na tabela.

Para isso, usar o comando SET e usar o salário como variável e fazer o incremento como se mostra abaixo.

LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @salary) SET salary = @salary+500;

Como se vê na saída seguinte, a coluna de salário em incrementos de 500 para todos os registos durante o carregamento de dados do ficheiro de texto.

Escrever Shell Script para carregar dados do ficheiro de texto

Por vezes pode querer carregar os dados de um ficheiro de texto automaticamente sem ter de iniciar sessão no prompt mysql de cada vez.

Deixe-nos dizer que queremos colocar o seguinte comando dentro de um script de shell e executá-lo automaticamente na base de dados do thegeekstuff.

LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','

Para executar a carga a partir da linha de comando, irá usar a opção -e no comando mysql e executá-la a partir do prompt do linux, como mostrado abaixo.

# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \ -u root -pMySQLPassword thegeekstuff

Or, pode colocar isso dentro de um script shell, como mostrado abaixo. Neste exemplo, o script load-data.sh shell tem o comando acima mysql.

Dê a permissão de execução a este script load-data.sh, e execute-o a partir da linha de comando, o que carregará os dados automaticamente para a tabela. Também pode agendar isto como cronjob para carregar os dados do ficheiro automaticamente para a tabela num intervalo agendado.

# chmod u+x load-data.sh# ./load-data.sh