Sintaxe `LOAD DATA INFILE`

A instrução LOAD DATA INFILE lê linhas de uma arquivo texto para uma tabela em uma velocidade muito alta. Se a palavra-chave LOCAL é especificada, ela é interpretada com respeito ao fim da conexão do cliente. Quando LOCAL é especificado, o arquivo é lido pelo programa cliente na máquina cliente e enviada ao servidor. Se LOCAL não é especificada, o arquivo deve estar localizado na máquina servidora e é lida diretamente pelo servidor (LOCAL está disponível no MariaDB Versão 3.22.6 ou posterior).

Por razões de segurança, ao ler arquivos textos no servidor, os arquivos devem também estar no diretório de banco de dados ou serem lidos por todos. Também, para utilizar LOAD DATA INFILE em arquivos do servidor, você deve ter privilégio FILE na máquina servidora. Leia "Privilégios Fornecidos pelo MySQL".

A partir do MariaDB 3.23.49 e MariaDB 4.0.2 (4.0.13 no Windows) LOCAL só funcionará se o seu servidor e o seu cliente forem habilitados para permitir isto. Por exemplo so o mysqld foi iniciado com --local-infile=0, LOCAL não irá funcionar. Leia "Detalhes de Segurança com LOAD DATA LOCAL".

Se você especificar a palavra-chave LOW_PRIORITY, a execução da instrução LOAD DATA é atrasada até nenhum outro cliente estar lendo a tabela.

Se você especificar a palavra-chave CONCURRENT com uma tabela MyISAM, outras threads podem retornar dados da tabela enquanto LOAD DATA está executando. Utilizar esta opção irá afetar o desempenho de LOAD DATA um pouco, mesmo se nenhuma outra thread utilizar a tabela ao mesmo tempo.

Utilizar LOCAL será um pouco mais lento que deixar o servidor acessar os arquivos diretamente, pois o conteúdo do arquivo deve ser enviado pela conexão da máquina cliente até a máquina servidora. Por outro lado, você não precisa de ter o privilégio FILE para carregar arquivos locais.

Se você estiver utilizando uma versão do MariaDB anterior a 3.23.24, você não poderá ler de um FIFO com LOAD DATA INFILE. Se você precisar ler de um FIFO (por exemplo a saída de gunzip), utilize LOAD DATA LOCAL INFILE.

Você também pode carregar arquivo de dados utilizado o utilitário mysqlimport; ele opera enviando um comando LOAD DATA INFILE para o servidor. A opção --local faz com que mysqlimport leia ao arquivo de dados a partir da máquina cliente. Você pode especificar a opção --compress para conseguir melhor desempenho sobre redes lentas se o cliente e o servidor suportar protocolos compactados.

Ao localizar arquivos na máquina servidora, o servidor utiliza as segintes regras:

Se um caminho absoluto é dado, o servidor utiliza o caminho desta forma.
Se um caminho relativo com um ou mais componentes é dados, o servidor busca o arquivo em relação ao diretório de dados do servidor.
Se um nome de arquivo sem nenhum componente é dado, o servidor procura pelo arquivo no diretorio de banco de dados do banco de dados atual.

Note que estas regras significam que um arquivo chamado ./myfile.txt é lido no diretório de dados do servidor, enquanto um arquivo chamado myfile.txt lê o diretório de dados do naco de dados atual. Por exemplo, a seguinte instrução LOAD DATA lê o arquivo data.txt do diretório de dados de db1 pois db1 é o banco de dados atual, mesmo que a instrução carrega explicitamente o arquivo em uma tabela no banco de dados db2:

mysql> USE db1;
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

As palavras-chave REPLACE e IGNORE controlam o tratamento de entrada de registros que duplicam linhas existentes em valores de chave única.

Se você especificar REPLACE, as linhas inseridas substituirão as linhas existentes (em outras palavras, linhas que tiverem o mesmo valor de um índice primário ou único como linhas existentes). Leia "Sintaxe REPLACE".

Se você especificar IGNORE, registros inseridos que duplicam uma linha existente em um valor de chave única será ignorados. Se você não especificar nenhuma das opções, o comportamento depende de se a palavra chave LOCAL é especificada ou não. Sem LOCAL, um erro ocorre quando um valor de chave duplicada é encontrado, e o resto do arquivo texto é ignorado. Com LOCAL o comportamento padrão é o mesmo de quando IGNORE for especificado, isto é porque o servidor não tem como parar no meio da operação.

Se você quiser ignorar as restrições de chaves estrangeiras durante a carga você pode faze SET FOREIGN_KEY_CHECKS=0 antes de executar LOAD DATA.

Se você utiliza LOAD DATA INFILE em uma tabela MyISAM vazia, todos os índices não-únicos são criados em um batch separado (como em REPAIR). Isto normalmente torna LOAD DATA INFILE muito mais rápido quando você tem diversos índices. Normalmente isto é muito rápido mas em casos extremos você pode tornar o índice mais rápido ainda desligando-os com ALTER TABLE .. DISABLE KEYS e usando ALTER TABLE .. ENABLE KEYS para recriar os índices. Leia "Utilizando myisamchk para Manutenção de Tabelas e Recuperação em Caso de Falhas".

LOAD DATA INFILE é o complemento de SELECT ... INTO OUTFILE. Leia "Sintaxe SELECT". Para gravar dados de uma tabela em um arquivo, use SELECT ... INTO OUTFILE. Para ler o arquivo de volta em uma tabela, use LOAD DATA INFILE. A sintaxe das cláusulas FIELDS e LINES é a mesma para ambos os comandos. Ambas as cláusulas são opicionais, mas FIELDS deve preceder LINES se ambos são especificados.

Se você especificar uma cláusula FIELDS, cada uma das subcláusulas (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, e ESCAPED BY) também são opicionais, exceto pelo fato de que você deve especificar pelo menos uma delas.

Se você não especificar uma cláusula FIELDS, o padrão é o mesmo que se você tivesse escrito isto:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Se você não especificar uma cláusula LINES, o padrão é o mesmo que se você tivesse escrito isto:

LINES TERMINATED BY '\n'

Nota: Se você gerou o arquivo texto no Windows, você deve alterar o mostrado acima para: LINES TERMINATED BY '\r\n' já que o Windows utiliza dois caracteres como um terminador de linha. Alguns programas como wordpad, pode usar \r como terminador de linha.

Se todas as linas que você deseja ler tem um prefixo comum que você quer saltar, você pode usar LINES STARTING BY prefix_string.

Em outras palavras, o padrão faz com que LOAD DATA INFILE funcione da seguinte maneira ao se ler uma entrada:

Procure pelo limite da linha em linhas novas.
Se LINES STARTING BY prefix for usado, lê até que o prefixo seja encontrado e começa a ler o caracter seguinte ao prefixo. Se a linha não inclui o prefico e;a será saltada.
Quebre a linha em campos na tabulações.
Não espere que os campos estejam entre aspas.
Interprete a ocorrência de tabulações, novas linhas ou '\' precedidos por '\' como caracteres literias que são parte dos valores dos campos.

Inversamente, os padrões fazem SELECT ... INTO OUTFILE funcionar da seguinte forma ao escrever as saídas:

Escreva tabulações entre os campos.
Não coloque campos entre aspas.
Utilize '\' para considerar como parte dos campos instâncias de tabulação, nova linha ou '\' que estejam dentro dos valores dos campos.
Escreva novas linhas no fim de cada linha.

Note que para escrever FIELDS ESCAPED BY '\\', você deve especificar duas barras invertidas para que o valor seja lido como uma única barra invertida.

A opção IGNORE número LINES pode ser utilizado para ignorar linhas no inicio do arquivo. Por exemplo, você pode usar IGNORE 1 LINES para saltar uma linha de cabeçalho contendo nomes de colunas:

mysql> LOAD DATA INFILE '/tmp/file_name' INTO TABLE test IGNORE 1 LINES;

Quando você utiliza SELECT ... INTO OUTFILE em conjunto com LOAD DATA INFILE para escrever os dados de um banco de dados em um arquivo e então ler o arquivo de volta no banco de dados posteriormente, as opções para tratamento de linhas e campos para ambos os comandos devem coincidir. Senão, LOAD DATA INFILEnão irá interpretar o conteúdo do arquivo de forma apropriada. Suponha que você utilize SELECT ... INTO OUTFILE para escrever um arquivo com os campos separados por vírgulas:

mysql> SELECT * INTO OUTFILE 'data.txt'
 -> FIELDS TERMINATED BY ','
 -> FROM ...;

Para ler o arquivo delimitado com vírgula de volta, a instrução correta seria:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
 -> FIELDS TERMINATED BY ',';

Se você tentasse ler do arquivo com a instrução abaixo, não iria funcionar pois ela instrui LOAD DATA INFILE a procurar por tabulações entre campos:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
 -> FIELDS TERMINATED BY '\t';

O resultado desejado é que cada linha de entrada fosse interpretada como um único campo.

LOAD DATA INFILE pode ser usado para ler arquivos obtidos de fontes externas. Por exemplo, um arquivo no formato dBASE terá campos separados por vírgulas e entre aspas duplas. Se as linhas no arquivo são terminadas por com uma nova linha, o comando mostardo aqui ilustra as opções do tratamento de campos e linhas que você usaria pra carregar o arquivo. the file:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE nome_tabela
 -> FIELDS TERMINATED BY ',' ENCLOSED BY '''
 -> LINES TERMINATED BY '\n';

Qualquer uma das opções de tratamento de campos e linhas podem especificar uma string vazia (''). Se não for vazio, os valores de FIELDS [OPTIONALLY] ENCLOSED BY e FIELDS ESCAPED BY devem ser um caracter simples. Os valores de FIELDS TERMINATED BY e LINES TERMINATED BY podem ser mais de uma caracter. Por exemplo, para escrever linhas terminadas pelos par retorno de carro/alimentação de linha, ou para ler um arquivo contendo tais linhas, especifique uma cláusula LINES TERMINATED BY '\r\n'.

Por exemplo, para ler um arquivo de piadas, que são separadas com uma linha de %%, em uma tabela SQL, você pode fazer:

CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes FIELDS TERMINATED BY ''
LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY controla a citação dos campos. Para saida (SELECT ... INTO OUTFILE), se você omitir a palavra OPTIONALLY, todos os campos estarão entra o caracter ENCLOSED BY. Um exemplo de tal saída (usando vírgula como delimitador de campo) é mostrado abaixo:

'1','a string','100.20'
'2','a string containing a , comma','102.20'
'3','a string containing a \' quote','102.20'
'4','a string containing a \', quote and comma','102.20'

Se você especificar OPTIONALLY, o caracter ENCLOSED BY só é usados para delimitar campos CHAR e VARCHAR:

1,'a string',100.20
2,'a string containing a , comma',102.20
3,'a string containing a \' quote',102.20
4,'a string containing a \', quote and comma',102.20

Note que a ocorrência de caracter ENCLOSED BY dentro do valor do campo é indicado colocando um caracter ESCAPED BY antes dele. Note também que se você especificar um valor ESCAPED BY vazio, é possível gerar saídas que não poderão ser lidas aprorpiadamente por LOAD DATA INFILE. Por exemplo, a saída mostrada seria apareceria como a seguir se o caracter de escape fosse vazio. Observe que o segundo campo na quarta linha contém uma vírgula seguida de aspas, o que (erroneamente) parece terminar o campo:

1,'a string',100.20
2,'a string containing a , comma',102.20
3,'a string containing a ' quote',102.20
4,'a string containing a ', quote and comma',102.20

Para entrada, o caracter ENCLOSED BY, se presente, será eliminado do fim dos valores dos campos. (Isto é verdade se OPTIONALLY for especificado; OPTIONALLY não tem efeito na interpretação da entrada). A ocorrência de caracteres ENCLOSED BY precedido pelo caracter ESCAPED BY são interpretados como parte do campo atual.

Se o campo começa com o caracter ENCLOSED BY, instâncias daquele caracter são reconhecidos como terminação de um valor do campo apenas se seguido pelo campo ou sequência de linah TERMINATED BY. Para evitar ambiguidade, ocorrências do caracter ENCLOSED BY dentro de um valor de campo pode ser duplicado e será interpretado como uma única instância do caracter. Por exemplo, se ENCLOSED BY ''' for especificado, aspas serão tratadas como mostrado abaixo:

'The ''BIG'' boss' -> The 'BIG' boss The 'BIG' boss -> The 'BIG' boss The ''BIG'' boss -> The ''BIG'' boss

FIELDS ESCAPED BY controla como escrever ou ler caracteres especiais. Se o caracter FIELDS ESCAPED BY não estivaer vazio, ele será usado para preceder o seguinte caracter de saída:

O caracter FIELDS ESCAPED BY
O caracter FIELDS [OPTIONALLY] ENCLOSED BY
O primeiro caracter dos valores FIELDS TERMINATED BY e LINES TERMINATED BY
ASCII 0 (o que é escrito seguido de um caracter de escape é ASCII '0', não o byte de valor zero).

Se o caracter FIELDS ESCAPED BY estiver vazio, nenhum caracter será escapado. Provavelmente não é uma boa idéia especificar um caracter de escape vazio, principalmente se os valores dos campos em seus conter qualquer caracter na lista dada.

Para entradas, se o caracter FIELDS ESCAPED BY não estiver vazio, as ocorrências daquele caracter são eliminadas e o caracter seguinte é tomado como parte do valor do campo. As exceções são um '0' ou 'N' escapado (por exemplo, \0 ou \N se o caracter de escape for '\'). Estas sequencias são interpretadas como os ASCII 0 (um byte de valor zero) e NULL. Veja abaixo as regras no tratamento de NULL.

Para maiores informações sobre a sintaxe '\'-escape, veja "Literais: Como Gravar Strings e Numerais".

Em certos casos, as opções de tratamento de campoe e linhas se interagem:

Se LINES TERMINATED BY é uma string vazia e FIELDS TERMINATED BY não é vazio, as linhas também serão terminadas com FIELDS TERMINATED BY.
Se os valores FIELDS TERMINATED BY e FIELDS ENCLOSED BY são ambos vazios (''), um formato de linha de tamanhos fixos (sem delimitadores) é utilizada. Com formato de linhas de tamanho fixo, nenhum deliitador é usado entre os campos (mas você ainda pode ter um terminador de linha). Valores de colunas são escritos e lidos usando o tamanho definido das colunas. Por exemplo, se uma coluna é declarada como INT(7), os valores das colunas são escritos utilizando campos de 7 caracteres. Na saída, os valores das colunas são obtidos lendo 7 caracteres.
LINES TERMINATED BY ainda é usado para separar linhas. Se uma linha não contém todos os campos, o resto dos campos serão configurados com o seu valor padrão. Se você não tiver um terminador de linha, você deve defini-lo com ''. Neste caso o arquivo texto deve conter todos os campos para cada linha.
O formato de linhas de tamanho fixo também afetam o tratamento de valores NULL; veja abixo. Note que este formato não funciona se você estiver utilizando um conjunto de caracteres mulyi-byte.

O tratamento do valor NULL varia, dependendo das opções de FIELDS e LINES que voce usar:

Para os valores FIELDS e LINES padrões, NULL é escrito como \N para saída e \N é lido como NULL para as entradas (assumindo que o caracter ESCAPED BY é '\').
Se FIELDS ENCLOSED BY não for vazio, um campo contendo a palavra literal NULL como seu valor é lido como um valor NULL (isto difere da palavra NULL entre os caracteres FIELDS ENCLOSED BY, a qual é lida como a string 'NULL').
Se FIELDS ESCAPED BY for vazio, NULL é escrito como a palavra NULL.
Com os formatos de tamanho fixos (que acontecem quando FIELDS TERMINATED BY e FIELDS ENCLOSED BY estiverem ambos vazios), NULL é escrito como uma string vazia. Note que isto faz com que os valores NULL e uma string vazia na tabela serão indistingüíveis quando escritas no arquivo pois elas são ambas escritas como strings vazias. Se você precisar estar saber diferenciar as duas ao ler o arquivo de volta, você não deve utilizar o formato de tamanho fixo.

Alguns casos não são suportados por LOAD DATA INFILE:

Linhas de tamanho fixo (FIELDS TERMINATED BY e FIELDS ENCLOSED BY vazios) e colunas BLOB ou TEXT.
Se você especificar um separador que é igual ao prefixo do outro, LOAD DATA INFILE não poderá interpretar a entratada apropriadamente. Por exemplo, a seguinte cláusula FIELDS causaria problemas:
```
FIELDS TERMINATED BY ''' ENCLOSED BY '''
```
Se FIELDS ESCAPED BY estiver vazio, um valor de campo que contém uma ocorrência de FIELDS ENCLOSED BY ou LINES TERMINATED BY seguido por valores FIELDS TERMINATED BY fará com que LOAD DATA INFILE pare de ler um campo ou linha antes do esperado. Isto ocorre porque LOAD DATA INFILE não pode determinar apropriadamente onde o valor de campo ou linha acaba.

A oseguinte exemplo carrega todas as colunas da tablea persondata:

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Nenhuma lista de campo é especificada, assim LOAD DATA INFILE espera linhas de entradas que contenha um campo para cada coluna da tabela. Os valores padrões de FIELDS e LINES são usados.

Se você deseja carregar somente algumas das colunas das tabelas, especifique uma lista de campos:

mysql> LOAD DATA INFILE 'persondata.txt'
 -> INTO TABLE persondata (col1,col2,...);

Você deve especificar uma lista de campos se a ordem dos campos no arquivo de entrada diferem da ordem das colunas na tabela. Senão o MariaDB não poderá dizer como combinar os campos da entrada nas colunas da tabela.

Se uma linha tiver poucos campos, as colunas para os quais o campo de entrada não estiverem presentes serão definidas com o valor padrão. Atribuição de valor padrão é descrito em "Sintaxe CREATE TABLE".

Um valor de campo vazio é interpretado de forma diferente de que se o valor do campo estiiver faltando:

Para tipos string, a coluna é definida com uma string vazia.
Para tipos numéricos, a coluna é definida com 0.
Para tipos de data e hora, a coluna é definida com o valor zero apropriado para o tipo. Leia "Tipos de Data e Hora".

Note que estes são os mesmos valores que resultam se você atribuir uma string vazia explicitamente a um tipo string, numérico, de data ou de hora em uma instrução INSERT ou UPDATE.

Colunas TIMESTAMP só são definidas com a hora e data atual se houver um valor NULL para a coluna (isto é, \N), ou (apenas para a primeira coluna TIMESTAMP) se a coluna TIMESTAMP esta a esquerda da lista de campos quando esta for especificada.

Se uma linha de entrada tiver muitos campos, os campos extras serão ignorados e o número de avisos é incrementado. Note que antes do MariaDB o aviso é apenas um número que indica que alguma coisa deu errado. No MariaDB você pode fazer SHOW WARNINGS para obter mais informações sobre o que deu errado.

LOAD DATA INFILE considera todas as entradas como strings, assim você não pode utiliar valores numéricos para colunas ENUM ou SET do mesmo modo que você pode com instruções INSERT. Todos os valores ENUM e SET devem ser específicados como strings!

Se você estiver usando a API C, você pode obter informações sobre a consulta chamando a função mysql_info() da API C quando a consulta LOAD DATA INFILE terminar. O formato da string de informação é mostrado aqui:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

Avisos ocorrem sob as mesmas circuntâncias que quando são inseridos via instrução INSERT (see "Sintaxe INSERT"), exceto que LOAD DATA INFILE também gera avisos quando houver poucos ou muitos campos na linha de entrada. Os avisos não são armazenados em nenhum local; o número de avisos só pode ser utilizado como uma indicação se tudo correr bem.

Se você obter avisos e quiser saber exatamente porque eles ocorreram, um modo de se fazer isto é utilizar SELECT ... INTO OUTFILE em outro arquivo e camporá-lo ao arquivo de entrada original.

Se você precisar que LOAD DATA leia de um pipe, você pode utilizar o seguinte truque:

mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x mysql -e 'LOAD DATA INFILE 'x' INTO TABLE x' x

Se você estiver usando uma versão do MariaDB a anterior a 3.23.25 você só poderá fazer o descrito acima com LOAD DATA LOCAL INFILE.

No MariaDB você pode usar SHOW WARNINGS para conseguir a lista do primeiros max_error_count avisos. Leia "SHOW WARNINGS | ERRORS".

Para mais informações sobre a eficiência de INSERT versus LOAD DATA INFILE e a melhora na velocidade de LOAD DATA INFILE, Leia "Performance das Consultas que Utilizam INSERT".

Sintaxe LOAD DATA INFILE

Retornar

Sintaxe `LOAD DATA INFILE`