XOOPS Brasil

 

Manipulação de Dados: SELECT, INSERT, UPDATE e DELETE





6.4.1. Sintaxe SELECT

SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
expressão_select,...
[INTO {OUTFILE | DUMPFILE} 'nome_arquivo' opções_exportação]
[FROM tabelas_ref
[WHERE definição_where]
[GROUP BY {inteiro_sem_sinal | nome_col | formula} [ASC | DESC], ...
[WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {inteiro_sem_sinal | nome_coluna | formula} [ASC | DESC], ...]
[LIMIT [offset,] row_count | row_count OFFSET offset]
[PROCEDURE nome_procedimento(lista_argumentos)]
[FOR UPDATE | LOCK IN SHARE MODE]]

SELECT é utilizado para retornar registros selecionados de uma ou mais tabelas. Cada expressão_select indica as colunas que você deseja recuperar. SELECT tanbém pode ser utilizado para retornar registros calculados sem referência a nenhuma tabela. Por exemplo:

mysql> SELECT 1 + 1;
-> 2

Todas as cláusulas usada devem ser fornecidas exatamente na ordem mostrada na descrição da sintaxe. Por exemplo, uma cláusula HAVING deve vir depois de qualquer cláusula GROUP BY e antes de qualquer cláusula ORDER BY.

  • Uma expressão SELECT pode utilizar um alias usando AS nome_alias. O alias é usado como o nome da coluna da expressão e pode ser usado com cláusulas ORDER BY ou HAVING. Por exemplo:

    mysql> SELECT CONCAT(primeiro_nome,' ',ultimo_nome) AS nome_completo
    FROM minha_tabela ORDER BY nome_completo;
    

    A palavra chave AS é opcional quando se utiliza alias em uma expressão SELECT. O exemplo anterior poderia ser escrito assim:

    mysql> SELECT CONCAT(last_name,', ',first_name) full_name
    FROM mytable ORDER BY full_name;
    

    Como AS é opcional, pode ocorrer um problema se você esquecer a vírgula entre duas expressões SELECT: O MySQL interpretará o segundo como um nome de alias. Por exemplo, na seguinte instrução, columnb é tratada como um nome de alias:

    mysql> SELECT columna columnb FROM mytable;
    
  • Não é permitido utilizar um apelido de coluna em uma cláusula WHERE, pois o valor da coluna pode ainda não ter sido determinado quando a cláusula WHERE for executada. Para mais informações sobre isto, veja Seção A.5.4, "Problemas com alias".
  • A cláusula FROM table_references indica a tabela de onde os registros serão retornados. Se você indicar mais de uma tabela, você estará realizando uma join. Para informações sobre a sintaxe de join, veja Seção 6.4.1.1, "Sintaxe JOIN". Para cada tabela especificada, você pode, opcionalmente, especificar um alias.

    nome_tabela [[AS] alias] [[USE INDEX (lista_indice)] | [IGNORE INDEX (lista_indice)] | FORCE INDEX (lista_indice)]]
    

    Como na versão 3.23.12 do MySQL, você pode dar sugestões sobre qual índice o MySQL deve usar ao recuperar informações de uma tabela. Isto é útil se EXPLAIN mostrar que o MySQL esta utilizando o índice errado da lista de índices possíveis. Especificando USE INDEX (lista_indice) você pode dizer ao MySQL para usar somente um dos índices possíveis para encontrar registros em uma tabela. A sintaxe alternativa IGNORE INDEX (lista_indice) pode ser usada para dizer ao MySQL para não utilizar alguns índices particulares.

    Na versão 4.0.9 do MySQL você também pode usar FORCE INDEX. Ele funciona como USE INDEX (lista_indice) mas ele assume que uma varredura em uma tabelas é MUITO cara. Em outras palavras, uma varredura só será usada se não houver nenhum modo de utilizar um dos índices dados para encontrar registros nas tabelas.

    USE/IGNORE/FORCE KEY é sinônimo de USE/IGNORE/FORCE INDEX.

    Nota: USE/IGNORE/FORCE INDEX afeta apenas os índices usados quando o MySQL decide como encontrar registros na tabela e como fazer a ligação. Ele não tem efeito se um índice será usado ao resolver um ORDER BY ou GROUP BY.

    No MySQL 4.0.14 você pode usar SET MAX_SEEKS_FOR_KEY=# como um modo alternativo de forçar o MySQL a preferir a busca em chaves em vez de varrer a tabela.

  • Você pode se referir a uma tabela como nome_tabela (dentro do banco de dados atual) ou como nomebd.nome_tabela para especificar um banco de dados. Você pode se referir a um coluna como nome_coluna, nome_tabela.nome_coluna ou nomebd.nome_tabela.nome_coluna. Você não precisa especificar um prefixo nome_tabla ou nomebd.nome_tabela para referência a uma coluna em uma instrução SELECT a menos a referência seja ambígua. Veja Seção 6.1.2, "Nomes de Banco de dados, Tabela, Índice, Coluna e Alias", para exemplos de ambiguidade que exigem a forma mais explicita de referência a coluna.
  • A partir da versão 4.1.0, você pode especificar DUAL como um nome de tabela dummy, em situações onde nenhuma tabela for referênciada. Este é um recurso puramente para compatibilidade, alguns outros servidores exijem esta sintaxe.

    mysql> SELECT 1 + 1 FROM DUAL;
    -> 2
    
  • Pode se definir um apelido fazendo referência a uma tabela utilizando nome_tabela [AS] nome_alias:

    mysql> SELECT t1.nome, t2.salario FROM funcionarios AS t1, info AS t2
    -> WHERE t1.nome = t2.nome;
    mysql> SELECT t1.nome, t2.salario FROM funcionarios t1, info t2
    -> WHERE t1.nome = t2.nome;
    
  • Colunas selecionadas para saída podem ser referidas em cláusulas ORCER BY e GROUP BY usando nomes de colunas, alias de colunas ou posições de colunas. As posições de colunas começam com 1:
    mysql> SELECT college, region, seed FROM tournament
    -> ORDER BY region, seed;
    mysql> SELECT college, region AS r, seed AS s FROM tournament
    -> ORDER BY r, s;
    mysql> SELECT college, region, seed FROM tournament
    -> ORDER BY 2, 3;
    

    Para ordenar inversamente, adicione a palavra-chave DESC (descendente) ao nome da coluna na cláusula ORDER BY na qual você está ordenando. A ordem padrão é ascedente; ela pode ser especificada explicitamente usando a palavra-chave ASC.

  • Na cláusula WHERE, você pode usar qualquer uma das funções suportadas pelo MySQL. Exceto para funções de agruopamento (resumo) Veja mais informações sobre isto na Seção 6.3, "Funções para Uso em Cláusulas SELECT e WHERE".
  • A cláusula HAVING pode se referir a qualquer coluna ou alias definido na expressão_select. Ele é aplicado no final, pouco antes dos itens serem enviados ao cliente, sem otimização. LIMIT é aplicada depois de HAVING.) estar na cláusula WHERE. Por exemplo, não escreva isto:

    mysql> SELECT nome_col FROM nome_tabela HAVING nome_col > 0;
    

    Escreva assim:

    mysql> SELECT nome_col FROM nome_tabela WHERE nome_col > 0;
    

    Na versão 3.22.5 ou posterior, você também pode escrever consultar desta forma:

    mysql> SELECT usuario,MAX(salario) FROM usuarios
    -> GROUP BY usuario HAVING MAX(salario)>10;
    

    Em versões mais antigas, você pode escrever desta forma:

    mysql> SELECT usuario,MAX(salario) AS soma FROM usuarios
    -> group by usuario HAVING soma>10;
    
  • As opções DISTINCT, DISTINCTROW e ALL especificam quando registros duplicados devem ser retornados. O padrão é (ALL), todos os registros coincidentes são retornados. DISTINCT e DISTINCTROW são sinônimos e espcificam que registros duplicados no conjunto de resultados devem ser remopvidos.
  • STRAIGHT_JOIN, HIGH_PRIORITY e opções começando com SQL_ são extensões do MySQL para SQL-99.

    • No MySQL 4.1.1, GROUP BY permite um modificador WITH ROLLUP. Para mais informações sobre isto, veja Seção 6.3.7.2, "Modificadores GROUP BY".
    • HIGH_PRIORITY dará uma prioridade maior ao SELECT do que para uma instrução que atualizam uma tabela. Você só deve isto para consultas que sejam rápidas e devam ser feitas imediatamente. Uma consulta SELECT HIGH_PRIORITY retornará se a tabela está bloqueada para leitura memsmo se houver uma instrução de atualização que estiver esperando a liberação da tabela.
    • SQL_BIG_RESULT pode ser usado com GROUP BY ou DISTINCT para dizer ao otimizador que o conjunto de resultados terá muitas linhas. Neste caso, o MySQL usará diretamente tabelas temporarias em disco se necessário. O MySQL também irá, neste caso, preferir ordenar fazendo uma tabela temporária com um cahve nos elementos GROUP BY.
    • SQL_BUFFER_RESULT força para que o resultado seja colocado em uma tabela temporária. Isto ajuda o MySQL a liberar as travas de tabelas mais cedo e ajudará nos casos onde ele levá muito tempo para enviar o conjunto de resultado ao cliente.
    • SQL_SMALL_RESULT, uma opção especifica do MySQL, pode ser usada com GROUP BY ou DISTINCT para dizer ao otimizador que o conjunto de resultados será pequeno. Neste caso, o MySQL usa tabelas temporárias rápidas para armazenar a tabela resultante em vez de usar ordenação. Na versão 3.23 do MySQL isto não é necessário normalmente.
    • SQL_CALC_FOUND_ROWS (versão 4.0.0 e acima) diz ao MySQL para calcular quantas linhas haveriam no conjunto de resultados, desconsiderando qualquer cláusula LIMIT. O número de linhas pode ser recuperado com SELECT FOUND_ROWS(). Para mais informações sobre isto, veja Seção 6.3.6.2, "Funções Diversas".

      Por favor, note que em nversões anteriores a 4.1.0 isto não funciona com LIMIT 0, o qual é otimizado para retornar instantaneamente (resultando em 0 registros). Veja mais informações sobre isto na Seção 5.2.9, "Como o MySQL Otimiza Cláusulas LIMIT".

    • SQL_CACHE diz ao MySQL para armazenar o resultado da consulta em um cache de consultas se você estiver utilizando QUERY_CACHE_TYPE=2 (DEMAND). Para mais informações sobre isto, veja Seção 6.9, "Cache de Consultas do MySQL". No caso da consulta com UNIONs e/ou subqueries esta opção terá efeito se usada em qualquer SELECT da consulta.
    • SQL_NO_CACHE diz ao MySQL para não armazenar o resulado da consulta nesta cache de consultas. Veja mais informações sobre isto na Seção 6.9, "Cache de Consultas do MySQL". No caso da consulta com UNIONs e/ou subqueries esta opção terá efeito se usada em qualquer SELECT da consulta.
  • Se você utiliza GROUP BY, os registros de saída serão ordenados de acordo com o GROUP BY como se você tivesse um ORDER BY sobre todos os campos no GROUP BY. O MySQL tem expandido a cláusula GROUP BY para que você também possa especificar ASC e DESC depois das colunas chamadas na cláusula:

    SELECT a,COUNT(b) FROM tabela_teste GROUP BY a DESC
    
  • O MySQL tem extendido o uso do GROUP BY para lhe permitir selecionar campos que não estão mencionados na cláusula GROUP BY. Se você não está conseguindo os resultados esperados ara a sua consulta, leia a descrição de GROUP BY. Para mais informações sobre isto, veja Seção 6.3.7, "Funções e Modificadores para Usar com Cláusulas GROUP BY".
  • A partir do MySQL 4.1.1, GROUP BY permite um modificador WITH ROLLUP. Para mais informações sobre isto, veja Seção 6.3.7.2, "Modificadores GROUP BY".
  • A cláusula LIMIT pode ser usada para restringir o número de linhas retornadas pela instrução SELECT. LIMIT utiliza um ou dois agumebntos numéricos, que devem ser constantes inteiras.

    Com um argumento. o valor especifíca o número de linhas para retornar do início do resultado. Com dois argumentos, o primeiro especifica a posição do primeiro registro a ser retornado e o segundo especifica o número máximo de linhas a retornar. A posição do registro inicial é 0 (não 1):

    Para ser compatível com o PostgreeSQL, o MySQL suporta a sintaxe: LIMIT row_count OFFSET offset.

    mysql> SELECT * FROM tabela LIMIT 5,10; # Recupera linhas 6-15
    

    Para recuperar todos os registros de um determinado offset até um fim do resultado você pode usar um número grande como segundo parâmetro:

    mysql> SELECT * FROM tabela LIMIT 95,18446744073709551615; # Recupera linhas 96-ultima.
    

    Se um dos argumentos é dado, ele indica o número máximo de linhas a retornar:

    mysql> SELECT * FROM tabela LIMIT 5; # Recupera as primeiras 5 linhas
    

    Em outras palavras, LIMIT n é equivalente a LIMIT 0,n.

  • A forma SELECT ... INTO OUTFILE 'nome_arquivo' do SELECT grava os registros selecionados em um arquivo. O arquivo é criado na máquina servidora e não pode já existir (entre outras coisas, isto previne tabelas de banco de dados e arquivos tais como /etc/passwd de serem destruídos). Você deve ter o privilégio FILE na máquina servidora para utilizar esta forma de SELECT.

    A instrução SELECT ... INTO OUTFILE tem como intenção deixar que você descarregue rapidamente um tabela de uma máquina servidora. Se você quiser criar o arquivo resultante em outra máquina, diferente do servidor, você não deve usar SELECT ... INTO OUTFILE. Neste caso você deve usar algum programa cliente como mysqldump --tab ou mysql -e "SELECT..." > outfile para gerar o arquivo.

    SELECT ... INTO OUTFILE é o complemento de LOAD DATA INFILE; a sintaxe para a parte opções_exportação de uma instrução consiste das mesmas cláusulas CAMPOS e LINHAS que são usadas com a instrução LOAD DATA INFILE. Veja mais informações sobre isto na Seção 6.4.8, "Sintaxe LOAD DATA INFILE".

    No arquivo texto resultante, somente os seguintes coracteres são escritos com o caracter ESCAPE BY:

    • O caracter ESCAPE BY
    • O primeiro caracter em FIELDS TERMINATED BY
    • O primeiro caracter em LINES TERMINATED BY

    Adicionalmente, ASCII 0 é convertido para ESCAPE BY seguido por 0 (ASCII 48).

    A razão para o mostrado acima é que você deve escapar qualquer caracter FIELDS TERMINATED BY, ESCAPE BY, or LINES TERMINATED BY para termos a segurança que o arquivo poderá ser lido de volta. É feito escape de ASCII 0 para facilitar a visuzlização com alguns paginadores.

    Como o arquivo resultante não tem que estar em conformidade com a sintaxe SQL, nada mais precisa ser seguido de caraceres de escape.

    Aqui segue um exemplo de como se obter um arquivo no formato usado por muitos programas antigos.

    SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY "\n"
    FROM tabela_teste;
    
  • Se você utilizar INTO DUMPFILE em vez de INTO OUTFILE, o MySQL só irá escrever um linha no arquivo, sem nenhum terminador de linha ou colunas e sem realizar nenhum processo de escape. Ele é útil se você quiser armazenar um valor BLOB em um arquivo.
  • Note que qualuqer arquivo criado por INTO OUTFILE e INTO DUMPFILE serão escritos por todos os utilizadores no servidor! A razão é que o servidor MySQL não pode criar um arquivo que pertence a qualquer um além do utilizador que o está executando (você nunca deve executar mysqld como root). Assim o arquivo tem que poder ser gravado por todos para que você possa manipular o seu conteúdo.
  • Uma cláusula PROCEDURE chama um procedimento que devia processar os dados em um resultado. Para um exemplo, veja Seção 14.3.1, "Análise de Procedimento".
  • Se você utilizar FOR UPDATE em um mecanismo de armazenamento com locks de páginas ou registros, as linhas examinadas serão travadas para escrita até o fim da transação atual.

6.4.1.1. Sintaxe JOIN

O MySQL suporta as seguintes sintaxes JOIN para uso em instruções SELECT:

tabela_ref, tabela_ref
tabela_ref [INNER | CROSS] JOIN table_reference [join_condition]
tabela_ref STRAIGHT_JOIN tabela_ref
tabela_ref LEFT [OUTER] JOIN table_reference [join_condition]
tabela_ref NATURAL [LEFT [OUTER]] JOIN tabela_ref
{ OJ tabela_ref LEFT OUTER JOIN tabela_ref ON expr_condicional }
tabela_ref RIGHT [OUTER] JOIN table_reference [join_condition]
tabela_ref NATURAL [RIGHT [OUTER]] JOIN tabela_ref

Onde tabela_ref é definido como:

nome_tabela [[AS] alias] [[USE INDEX (lista_indice)] | [IGNORE INDEX (lista_indice)] | [FORCE INDEX (lista_indice)]]

a condição_join é definido como:

ON expr_condicional |
USING (lista_colunas)

Geralamente você não deverá ter nenhuma condição na parte ON que é usada para restringir quais registros você terá no seu resultado, mas ao invés disto, especificar estas condições na cláusula WHERE. Existem exceções para isto.

Note que a sintaxe INNER JOIN permite uma condição_join apenas a partir da versão 3.23.17. O mesmo acontece para JOIN e CROSS JOIN apenas a partir do MySQL 4.0.11.

A última sintaxe LEFT OUTER JOIN mostrada na lista anterior só existe para compatibilidade com ODBC:

  • Pode se usar um apelido para referência a tabelas com nome_tabela AS nome_alias ou nome_tabela nome_alias:

    mysql> SELECT t1.nome, t2.salario FROM funcionarios AS t1, info AS t2
    -> WHERE t1.nome = t2.nome;
    
  • A condicional ON é qualquer condição da forma que pode ser usada em uma cláusula WHERE.
  • Se não houver registros coincidentes para a tabela a direita da parte ON ou USING em um LEFT JOIN, uma linha com NULL atribuído a todas as colunas é usada para a tabela a direita. Você pode usar este fato para encontrar registro em uma tabela que não houver contrapartes em outra tabela

    mysql> SELECT tabela1.* FROM tabela1
    -> LEFT JOIN tabela2 ON tabela1.id=tabela2.id
    -> WHERE tabela2.id IS NULL;
    

    Este exemplo encontra todas as linhas em tabela1 com um valor id que não está presente em tabela2 (isto é, toda as linhas em tabela1 sem linha correspondente em tabela2). Assume-se que tabela2.id é declarada NOT NULL. Para mais informações sobre isto, veja Seção 5.2.7, "Como o MySQL Otimiza LEFT JOIN e RIGHT JOIN".

  • A cláusula USING (lista_colunas) nomeia uma lista de colunas que devem existir em ambas as tabelas. As seguintes duas cláusulas são semanticamente idênticas:

    a LEFT JOIN b USING (c1,c2,c3)
    a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
    
  • Um NATURAL [LEFT] JOIN de duas tabelas é definido para ser semanticamente equivalente a um INNER JOIN ou um LEFT JOIN com uma cláusula USING que nomeia todas as colunas que exitem em ambas as tabelas.
  • INNER JOIN e , (vírgula) são semanticamente equivalentes na ausência da condição join: ambos produzirão um produto Cartesiano entre as tabelas especificadas. (isto é, todos os registros na primeira tabela serão ligados com todos os registros na segunda tabela).
  • RIGHT JOIN funciona de forma análoga a um LEFT JOIN. Para manter o código portável entre banco de dados, é recomendado usar LEFT JOIN em vez de RIGHT JOIN.
  • STRAIGHT_JOIN é identico a JOIN, exceto pelo fato de que a tabela de esquerda sempre é lida antes da tabela da direita. Ele pode ser usado para aqueles casos (poucos) onde o otimizador join coloca as tabelas na ordem errada.
  • Como na versão 3.23.12, você pode dar sugestões sobre qual índice o MySQL deve us quando retornar informações de uma tabela. Isto é útil se EXPLAIN mostar que o MySQL está utilizando o índice errado da lista de índices possíveis. Especificando USE INDEX (lista_indice), você pode dizer ao MySQL para usar somente um dos índices possíveis para encontrar registros em uma tabela. A sintaxe alternativa IGNORE INDEX (lista_indice) pode ser usado para dizer ao MySQL para não utilizar índices particulares.

    Na versão 4.0.9 do MySQL você também pode utilizar FORCE INDEX. Ele funciona como USE INDEX (key_list) mas com assume que uma varredura na tabela é MUITO cara. Em outras palavras, uma varredura na tabela só será feita se não houver modo de uitlizar um dos índices fornecidos para se enecontrar registros no tabela.

    USE/IGNORE KEY são sinônimos de USE/IGNORE INDEX.

Nota: USE/IGNORE/FORCE INDEX afeta apenas os índices usados quando o MySQL decide como encontrar registros na tabela e como fazer a ligação. Ele não tem efeito se um índice será usado ao resolver um ORDER BY ou GROUP BY.

Alguns exemplos:

mysql> SELECT * FROM tabela1,tabela2 WHERE tabela1.id=tabela2.id;
mysql> SELECT * FROM tabela1 LEFT JOIN tabela2 ON tabela1.id=tabela2.id;
mysql> SELECT * FROM tabela1 LEFT JOIN tabela2 USING (id);
mysql> SELECT * FROM tabela1 LEFT JOIN tabela2 ON tabela1.id=tabela2.id
-> LEFT JOIN tabela3 ON tabela2.id=tabela3.id;
mysql> SELECT * FROM tabela1 USE INDEX (chave1,chave2)
-> WHERE chave1=1 AND chave2=2 AND chave3=3;
mysql> SELECT * FROM tabela1 IGNORE INDEX (chave3)
-> WHERE chave1=1 AND chave2=2 AND chave3=3;

Veja mais informações sobre isto na Seção 5.2.7, "Como o MySQL Otimiza LEFT JOIN e RIGHT JOIN".

6.4.1.2. Sintaxe UNION

SELECT ...
UNION [ALL]
SELECT ...
[UNION
SELECT ...]

UNION foi implementado no MySQL 4.0.0.

UNION é usado para combinar o resultado de muitas instruções SELECT em um único conjunto de resultados.

As colunas listadas na porção expressão_select de SELECT devem ter o mesmo tipo. Os nomes das colunas usadas na primeira consulta SELECT serão usadas como nomes de colunas para o resultado retornado.

Os comandos SELECT são comandos selects normais, mas com a seguinte restrição:

  • Somente o último comando SELECT pode ter INTO OUTFILE.

Se você não utilzar a palavra-chave ALL para o UNION, todas as linhas retornadas serão únicas, como se você tivesse utilizado um DISTINCT para o resultado final. Se você especificar ALL, você obterá todos os regitros encontrados em todas as instruções SELECT.

Se você quiser usar um ORDER BY para o resultado UNION final, você deve utilizar parenteses:

(SELECT a FROM nome_tabela WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM nome_tabela WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

6.4.2. Sintaxe de Subquery

Uma subquery é uma instrução SELECT dentro de outra instrução. Por exemplo:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

No exemplo acima, SELECT * FROM t1 ... é a consulta principal (ou instrução principal), e (SELECT column1 FROM t2) é a subquery. Dizemos que a subquery está aninhada na consulta principal, e de fato é possível aninhar subqueries dentro de outras subqueries, a uma grande profundidade. uma subquery deve estar sempres dentro de parenteses.

A partir da versão 4.1. o MySQL suporta todas as formas de subqueries e operações que o padrão SQL exige, assim como alguns recursos que são especificos do MySQL. A principal vantagem das subqueries são:

  • elas permitem consultas que estão estruturadas assim é possível isolar cada parte de uma instrução,
  • elas fornecem modos alternativos de realizar operações que, de outra forma, exigiriam joins e unions complexos,
  • elas são, na opinião de muitas pessoas, legíveis. De fato, foi a inovação das subqueries que deu às pessoas a idéia original do nome SQL ``Structured Query Language''.

Com versões MySQL anteriores era necessário evitar ou contornar as subqueries, mas as pessoas que começam a escrever código agora descobrirão que subqueries são uma parte muito útil do pacote de ferramentas.

Aqui está uma instrução exemplo que mostra o ponto principal sobre a sintaxe de subquery como especificado pelo SQL padrão e suportado no MySQL.

DELETE FROM t1
WHERE s11 > ANY
(SELECT COUNT(*) /* no hint */ FROM t2
WHERE NOT EXISTS
(SELECT * FROM t3
WHERE ROW(5*t2.s1,77)=
(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
(SELECT * FROM t5) AS t5)));

Para as versões do MySQL anteriores a 4.1, a maioria da subqueries podem ser reescritas com sucesso usando join e outros métodos. Veja mais informações sobre isto na Seção 6.4.2.11, "Rewriting Subqueries for Earlier MySQL Versions".

6.4.2.1. A Subquery como um Operando Escalar

Na sua forma mais simples (a subquery scalar é o oposto das subqueries de row ou table que será discutido posteriormente), uma subqquery é um opernado simples. Assim você pode usá-la se um valor de uma coluna ou literal é permitido, e você pode esperar que eles tenham certas características que todos os operandos possuem: um tipo de dados, um tamanho, um indicador para informar se ele pode ser NULL, etc. Por exemplo:

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
SELECT (SELECT s2 FROM t1);

The subquery in the above SELECT has a data type of CHAR, a length of 5, a character set and collation equal to the defaults in effect at CREATE TABLE time, and an indication that the value in the column can be NULL. In fact almost all subqueries can be NULL, because if the table is empty -- as in the example -- then the value of the subquery will be NULL. There are few restrictions.

  • A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.
  • A subquery can contain any of the keywords or clauses that an ordinary SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, hints, UNIONs, comments, functions, and so on.

So, when you see examples in the following sections that contain the rather Spartan construct (SELECT column1 FROM t1), imagine that your own code will contain much more diverse and complex constructions.

For example, suppose we make two tables:

CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

Then perform a SELECT:

SELECT (SELECT s1 FROM t2) FROM t1;

The result will be 2 because there is a row in t2, with a column s1, with a value of 2.

The subquery may be part of an expression. If it is an operand for a function, don't forget the parentheses. For example:

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

6.4.2.2. Comparações Usando Subquery

The most common use of a subquery is in the form:

<non-subquery operand> <comparison operator> (<subquery>)

Where <comparison operator> is one of:

= > < >= <= <>

For example:

... 'a' = (SELECT column1 FROM t1)

At one time the only legal place for a subquery was on the right side of a comparison, and you might still find some old DBMSs which insist on that.

Here is an example of a common-form subquery comparison which you can't do with a join: find all the values in table t1 which are equal to a maximum value in table t2.

SELECT column1 FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);

Here is another example, which again is impossible with a join because it involves aggregating for one of the tables: find all rows in table t1 which contain a value which occurs twice.

SELECT * FROM t1
WHERE 2 = (SELECT COUNT(column1) FROM t1);

6.4.2.3. Subqueries with ANY, IN, and SOME

Syntax:

<operand> <comparison operator> ANY (<subquery>)
<operand> IN (<subquery>)
<operand> <comparison operator> SOME (<subquery>)

The word ANY, which must follow a comparison operator, means ``return TRUE if the comparison is TRUE for ANY of the rows that the subquery returns.'' For example,

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing {10}. The expression is TRUE if table t2 contains {21,14,7} because there is a value in t2 -- 7 -- which is less than 10. The expression is FALSE if table t2 contains {20,10}, or if table t2 is empty. The expression is UNKNOWN if table t2 contains {NULL,NULL,NULL}.

The word IN is an alias for = ANY. Thus these two statements are the same:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

The word SOME is an alias for ANY. Thus these two statements are the same:

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

Use of the word SOME is rare, but the above example shows why it might be useful. The English phrase ``a is not equal to any b'' means, to most people's ears, ``there is no b which is equal to a'' -- which isn't what is meant by the SQL syntax. By using <> SOME instead, you ensure that everyone understands the true meaning of the query.

6.4.2.4. Subqueries with ALL

Syntax:

<operand> <comparison operator> ALL (<subquery>)

The word ALL, which must follow a comparison operator, means ``return TRUE if the comparison is TRUE for ALL of the rows that the subquery returns''. For example,

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing {10}. The expression is TRUE if table t2 contains {-5,0,+5} because all three values in t2 are less than 10. The expression is FALSE if table t2 contains {12,6,NULL,-100} because there is a single value in table t2 -- 12 -- which is greater than 10. The expression is UNKNOWN if table t2 contains {0,NULL,1}.

Finally, if table t2 is empty, the result is TRUE. You might think the result should be UNKNOWN, but sorry, it's TRUE. So, rather oddly,

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

is TRUE when table t2 is empty, but

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

is UNKNOWN when table t2 is empty. In addition,

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

is UNKNOWN when table t2 is empty. In general, tables with NULLs and empty tables are edge cases -- when writing subquery code, always consider whether you have taken those two possibilities into account.

6.4.2.5. Correlated Subqueries

A correlated subquery is a subquery which contains a reference to a column which is also in the outer query. For example:

SELECT * FROM t1 WHERE column1 = ANY
(SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

Notice, in the example, that the subquery contains a reference to a column of t1, even though the subquery's FROM clause doesn't mention a table t1. So MySQL looks outside the subquery, and finds t1 in the outer query.

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example the WHERE clause within the subquery is FALSE (because 7 <> 5), so the subquery as a whole is FALSE.

Scoping rule: MySQL evaluates from inside to outside. For example:

SELECT column1 FROM t1 AS x
WHERE x.column1 = (SELECT column1 FROM t2 AS x
WHERE x.column1 = (SELECT column1 FROM t3 WHERE x.column2 = t3.column1));

In the above, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ... renames t2. It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query which is further out.

For subqueries in HAVING or ORDER BY clauses, MySQL also looks for column names in the outer select list.

MySQL's unofficial recommendation is: avoid correlation because it makes your queries look more complex, and run more slowly.

6.4.2.6. EXISTS and NOT EXISTS

If a subquery returns any values at all, then EXISTS <subquery> is TRUE, and NOT EXISTS <subquery> is FALSE. For example:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Traditionally an EXISTS subquery starts with SELECT * but it could begin with SELECT 5 or SELECT column1 or anything at all -- MySQL ignores the SELECT list in such a subquery, so it doesn't matter.

For the above example, if t2 contains any rows, even rows with nothing but NULL values, then the EXISTS condition is TRUE. This is actually an unlikely example, since almost always a [NOT] EXISTS subquery will contain correlations. Here are some more realistic examples.

Example: What kind of store is present in one or more cities?

SELECT DISTINCT store_type FROM Stores
WHERE EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);

Example: What kind of store is present in no cities?

SELECT DISTINCT store_type FROM Stores
WHERE NOT EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);

Example: What kind of store is present in all cities?

SELECT DISTINCT store_type FROM Stores S1
WHERE NOT EXISTS (
SELECT * FROM Cities WHERE NOT EXISTS (
SELECT * FROM Cities_Stores
WHERE Cities_Stores.city = Cities.city
AND Cities_Stores.store_type = Stores.store_type));

The last example is a double-nested NOT EXISTS query -- it has a NOT EXISTS clause within a NOT EXISTS clause. Formally, it answers the question ``does a city exist with a store which is not in Stores?''. But it's easier to say that a nested NOT EXISTS answers the question ``is x TRUE for all y?''.

6.4.2.7. Row Subqueries

The discussion to this point has been of column (or scalar) subqueries -- subqueries which return a single column value. A row subquery is a subquery variant that returns a single row value -- and may thus return more than one column value. Here are two examples:

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

The queries above are both TRUE if table t2 has a row where column1 = 1 and column2 = 2.

The expression (1,2) is sometimes called a row constructor and is legal in other contexts too. For example

SELECT * FROM t1 WHERE (column1,column2) = (1,1);

is equivalent to

SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

The normal use of row constructors, though, is for comparisons with subqueries that return two or more columns. For example, this query answers the request: ``find all rows in table t1 which are duplicated in table t2'':

SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);

6.4.2.8. Subqueries in the FROM clause

Subqueries are legal in a SELECT statement's FROM clause. The syntax that you'll actually see is:

SELECT ... FROM (<subquery>) AS <name> ...

The AS <name> clause is mandatory, because any table in a FROM clause must have a name. Any columns in the <subquery> select list must have unique names. You may find this syntax described elsewhere in this manual, where the term used is ``derived tables''.

For illustration, assume you have this table:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

Here's how to use the Subqueries in the FROM clause feature, using the example table:

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

Result: 2, '2', 4.0.

Here's another example: Suppose you want to know the average of the sum for a grouped table. This won't work:

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

But this query will provide the desired information:

SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;

Notice that the column name used within the subquery (sum_column1) is recognized in the outer query.

At the moment, subqueries in the FROM clause cannot be correlated subqueries.

6.4.2.9. Subquery Errors

There are some new error returns which apply only to subqueries. This section groups them together because reviewing them will help remind you of some points.

  • ERROR 1235 (ER_NOT_SUPPORTED_YET)
    SQLSTATE = 42000
    Message = "This version of MySQL doesn't yet support
    'LIMIT & IN/ALL/ANY/SOME subquery'"
    

    This means that

    SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)
    

    will not work, but only in some early versions, such as MySQL 4.1.1.

  • ERROR 1240 (ER_CARDINALITY_COL)
    SQLSTATE = 21000
    Message = "Operand should contain 1 column(s)"
    

    This error will occur in cases like this:

    SELECT (SELECT column1, column2 FROM t2) FROM t1;
    

    It's okay to use a subquery that returns multiple columns, if the purpose is comparison. Para mais informações sobre isto, veja Seção 6.4.2.7, "Row Subqueries". But in other contexts the subquery must be a scalar operand.

  • ERROR 1241 (ER_SUBSELECT_NO_1_ROW)
    SQLSTATE = 21000
    Message = "Subquery returns more than 1 row"
    

    This error will occur in cases like this:

    SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
    

    but only when there is more than one row in t2. That means this error might occur in code that has been working for years, because somebody happened to make a change which affected the number of rows that the subquery can return. Remember that if the object is to find any number of rows, not just one, then the correct statement would look like this:

    SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
    
  • Error 1093 (ER_UPDATE_TABLE_USED)
    SQLSTATE = HY000
    Message = "You can't specify target table 'x' for update in FROM clause"
    

    This error will occur in cases like this:

    UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
    

It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and in DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target.

Usually, failure of the subquery causes the entire statement to fail.

6.4.2.10. Optimizing Subqueries

Development is ongoing, so no optimization tip is reliable for the long term. Some interesting tricks that you might want to play with are:

  • Using subquery clauses which affect the number or order of the rows in the subquery, for example

    SELECT * FROM t1 WHERE t1.column1 IN
    (SELECT column1 FROM t2 ORDER BY column1);
    SELECT * FROM t1 WHERE t1.column1 IN
    (SELECT DISTINCT column1 FROM t2);
    SELECT * FROM t1 WHERE EXISTS
    (SELECT * FROM t2 LIMIT 1);
    
  • Replacing a join with a subquery, for example

    SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
    SELECT column1 FROM t2);
    

    instead of

    SELECT DISTINCT t1.column1 FROM t1, t2
    WHERE t1.column1 = t2.column1;
    
  • Moving clauses from outside to inside the subquery, for example:

    SELECT * FROM t1
    WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
    

    instead of

    SELECT * FROM t1
    WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
    

    Para outro exemplo:

    SELECT (SELECT column1 + 5 FROM t1) FROM t2;
    

    em vez de:

    SELECT (SELECT column1 FROM t1) + 5 FROM t2;
    
  • Using a row subquery instead of a correlated subquery, for example:

    SELECT * FROM t1
    WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
    

    instead of

    SELECT * FROM t1
    WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
    AND t2.column2=t1.column2);
    
  • Using NOT (a = ANY (...)) rather than a <> ALL (...).
  • Using x = ANY (table containing {1,2}) rather than x=1 OR x=2.
  • Using = ANY rather than EXISTS

The above tricks may cause programs to go faster or slower. Using MySQL facilities like the BENCHMARK() function, you can get an idea about what helps in your own situation. Don't worry too much about transforming to joins except for compatibility with older versions.

Some optimizations that MySQL itself will make are:

  1. MySQL will execute non-correlated subqueries only once, (use EXPLAIN to make sure that a given subquery really is non-correlated),
  2. MySQL will rewrite IN/ALL/ANY/SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed,
  3. MySQL will replace subqueries of the form

    ... IN (SELECT indexed_column FROM single_table ...)
    

    with an index-lookup function, which EXPLAIN will describe as a special join type,

  4. MySQL will enhance expressions of the form

    value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)
    

    with an expression involving MIN or MAX (unless NULLs or empty sets are involved). For example,

    WHERE 5 > ALL (SELECT x FROM t)
    

    might be treated as

    WHERE 5 > (SELECT MAX(x) FROM t)
    

There is a chapter titled ``How MySQL Transforms Subqueries'' in the MySQL Internals Manual, which you can find by downloading the MySQL source package and looking for a file named internals.texi.

6.4.2.11. Rewriting Subqueries for Earlier MySQL Versions

Up to version 4.0, only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... are supported. The IN() construct can be used in other contexts.

It is often possible to rewrite a query without a subquery:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

This can be rewritten as:

SELECT t1.* FROM t1,t2 WHERE t1.id=t2.id;

The queries:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

Can be rewritten as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimise it better -- a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things in those bygone days. Today, MySQL Server and many other modern database systems offer a whole range of outer joins types.

For more complicated subqueries you can often create temporary tables to hold the subquery. In some cases, however, this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in subqueries). For this situation there are three options available:

  • The first option is to upgrade to MySQL version 4.1.
  • The second option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...)).
  • The third option is to use interactive SQL to construct a set of DELETE statements automatically, using the MySQL extension CONCAT() (in lieu of the standard || operator). For example:

    SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', "'", tab1.pkid, "'", ';')
    FROM tab1, tab2
    WHERE tab1.col1 = tab2.col2;
    

    You can place this query in a script file and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter:

    shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
    

MySQL Server 4.0 supports multiple-table DELETEs that can be used to efficiently delete rows based on information from one table or even from many tables at the same time. Multiple-table UPDATEs are also supported from version 4.0.

6.4.3. Sintaxe INSERT

 INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] nome_tabela [(nome_coluna,...)]
VALUES ((expressão | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE nome_coluna=expressão, ... ]
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] nome_tabela [(nome_coluna,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] nome_tabela
SET nome_coluna=(expressão | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE nome_coluna=expressão, ... ]

INSERT insere novos registros em uma tabela existente. A forma INSERT ... VALUES da instrução insere registros baseado em valores especificados explicitamente. A forma INSERT ... SELECT insere linhas selecionadas de outra(s) tabela(s). A forma INSERT ... VALUES com listas de múltiplos valores é suportado a partir da versão 3.22.5. A sintaxe nome_coluna=expressão é suportada a partir da verão 3.22.10 do MySQL.

nome_tabela é a tabela na qual as linhas serão inseridas. A lista de nome das colunas ou a cláusula SET indica para quais colunas a instrução especifica valor:

  • Se você não especificar a lista de colunas para INSERT ... VALUES ou INSERT ... SELECT, os valores para todas as colunas na tabela devem ser fornecidos na lista VALUES() ou pelo SELECT. Se você não souber a ordem das colunas nas tabelas, use DESCRIBE nome_tabela para descobrir.
  • Qualquer coluna que não tiver o valor fornecido explicitamente assumirá o seu valor padrão. Por exemplo, se você especificar uma lista de colunas que não definem todas as coolunas na tabela, às colunas não definidas serão atribuídos o seu valor padrão. Atribuição de valor padrão é definido em Seção 6.5.3, "Sintaxe CREATE TABLE".

    Você também pode utilizar a palavra-chave DEFAULT para atribuir o valor padrão a uma coluna (Novo na versão 4.0.3. do MySQL). Fica mais fácil de se escrever instruções INSERT que atribuem valor a apenas algumas colunas porque ele permite que você evite escrever uma lista VALUES() incompleta (uma lista que não inclu um valor para cada coluna da tabela). De outa forma, você teria que escrever a lista de nomes de colunas correspondentes a cada valor na lista VALUES().

    MySQL sempre tem uma valor padrão para todos os campos. Isto é algo imposto pelo MySQL para estar apto a funcionar com tabelas transacionais e não transcaionais.

    Nossa visão é que a verificação do conteúdo dos campos deve ser feita pela application and not in the database server.

  • Uma expressão pode se referir a qualquer coluna que tenha sida definaida anteriormente na lista de valores. Por exemplo, você pode dizer:

    mysql> INSERT INTO nome_tabela (col1,col2) VALUES(15,col1*2);
    

    Mas não:

    mysql> INSERT INTO nome_tabela (col1,col2) VALUES(col2*2,15);
    
  • Se você especificar a palavra chave DELAYED, o servidor coloca a linha ou linhas a serem inseridas em um buffer, e o cliente que envia a instrução INSERT DELAYED então pode contiuar. Se a tabela está ocupada, o servidor guarda a linha. Quando a tabela fica livre, ele começa a inserir linhas, verificando periódicamente para ver se há novos pedidos de leitura para a tabela. Se houver, a fila de linhas atrasadas é suspensa até que a tabela fique livre de novo.
  • Se você especificar a palavra-chave LOW_PRIORITY, a execução do INSERT é atrasada até que nenhum outro cliente esteja lendo a tabela. Isto inclui outros clientes que começam a ler enquanto clientes existentes já estão lendo e enquanto a instrução INSERT LOW_PRIORITY está esperando. É possível, consequentemente, para um cliente que envia uma instrução INSERT LOW_PRIORITY esperar por um tempo muito longo (ou mesmo para sempre) em um ambiente de muita leitura. (É diferente de INSERT DELAYED, que deixa o cliente continuar de uma vez. Para mais informações sobre isto, veja Seção 6.4.3.2, "Sintaxe INSERT DELAYED". Note que LOW_PRIORITY não deve normalmente ser usado com tabelas MyISAM ja que elas disabilitam inserções concorrentes. Para mais informações sobre isto, veja Seção 7.1, "Tabelas MyISAM".
  • Se você especificar a palavra-chave IGNORE em um INSERT com muitas linhas, quqlquer linha que duplicar uma chave PRIMARY ou UNIQUE existente em uma tabela são ignorados e não são inseridos. Se você não especificar IGNORE, a inserção é abortada se houver quqlquer linhq que duplique um valor de uma chave existente. Você pode determinar com função mysql_info() da API C quantas linhas foram inseridas nas tabelas.
  • Se você especificar se uma cláusula ON DUPLICATE KEY UPDATE (noca no MySQL 4.1.0), e uma linha que causasse a duplicação de um valor fosse inserida em uma chave PRIMARY ou UNIQUE, um UPDATE da linha antiga seria realizado. Por exemplo, o comando:

    mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;
    

    no caso da coluna a ser declarada como UNIQUE e ja existir o valor 1, o exemplo acima seria idêntico a

    mysql> UPDATE table SET c=c+1 WHERE a=1;
    

    Nota: se a coluna b também for única, o comando UPDATE seria escrito como

    mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
    

    e se a=1 OR b=2 casasse com diversas linhas, somente uma linha será atualizada! em geral, deve-se tentar evitar utilizar a cláusula ON DUPLICATE KEY em tabelas com múltiplas chaves UNIQUE.

    Desde o MySQL 4.1.1 pode-se utilizar a função VALUES(nome_coluna) para se referir ao valor da coluna na parte INSERT do comando INSERT ... UPDATE - que é o valor que seria inserido se não houvesse conflitos de chaves duplicadas. Esta função é especialmente útil em inserções de múltiplas linhas. Naturalmente a função VALUES() só tem sentido em um comando INSERT ... UPDATE e retorna NULL no caso de outros comandos.

    Exemplo:

    mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
    

    O camondo acima e idêntico a

    mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=3;
    mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
    -> ON DUPLICATE KEY UPDATE c=9;
    

    A utilizar ON DUPLICATE KEY UPDATE, a opção DELAYED é ignorada.

  • Se MySQL foi configurado usando a opção DONT_USE_DEFAULT_FIELDS, instruções INSERT geram um erro a menos que você especifique valores explicitamete para todas as colunas que exigem um valor não-NULL. Para mais informações sobre isto, veja Seção 2.3.3, "Opções típicas do configure".
  • Você pode encontrar o valor usado por uma coluna AUTO_INCREMENT com a função mysql_insert_id. Para mais informações sobre isto, veja Seção 12.1.3.32, "mysql_insert_id()".

Se você utilizar instruções INSERT ... SELECT ou INSERT ... VALUES com lista de valores múltiplos, você pode utilizar a função mysql_info() da API C para obter informação sobre a consulta. O formato da string de informação é mostrado aqui:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indica o número de linhas que não puderam ser inseridas porque duplicariam alguns valores de índices únicos existentes. Warnings indica o número de tentativas de inserção de um valor em uma coluna que de alguma forma estava problematico. Avisos (Warnings) podem ocorrer sob qualquer uma das seguintes condições:

  • Inserir NULL em uma coluna declarada com NOT NULL. A coluna é definida com o seu valor padrão.
  • Definir uma coluna numérica com um valor que esteja fora da faixa permitida. O valor é revertido para final apropriado da faixa.
  • Definir uma coluna numérica com um valor como '10.34 a'. O lixo no final é eliminado e a parte numérica restante é inserida. Se o valor não fizer sentido como um número, é atribuido 0 a coluna.
  • Inserir uma string em uma coluna CHAR, VARCHAR, TEXT, ou BLOB e que exceda o tamanho máximo da coluna. O valor é truncado para o tamanho máximo da coluna.
  • Inserir um valor em uma coluna date ou time e que seja inválido para o tipo da coluna. A coluna é preenchida com o valor de zero apropriado para o tipo.

6.4.3.1. Sintaxe INSERT ... SELECT

INSERT [LOW_PRIORITY] [IGNORE] [INTO] nome_tabela [(column list)] SELECT ...

Com a instrução INSERT ... SELECT você pode inserir muitas linhas rapidamente em uma tabela a partir de outras tabelas

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

As seguintes condições servem para uma instrução INSERT ... SELECT:

  • Antes do MySQL 4.0.1, INSERT ... SELECT operava implicitamente em modo IGNORE. A partir do MySQL 4.0.1, você deve especificar IGNORE explicitamente para ignorar registros que causaria violação de chave duplicada.
  • Antes do MySQL 4.0.14, a tabela alvo da instrução INSERT não pode aparecer na cláusula FROM da parte SELECT da consulta. Esta limitação é deixada na versão 4.0.14.
  • Colunas AUTO_INCREMENT funcionam da mesma forma.
  • Em programas C, Você pode usar a função mysql_info() da API C para obter informação sobre a consulta. Para mais informações sobre isto, veja Seção 6.4.3, "Sintaxe INSERT".
  • Para assegurar que o log binário possa ser usado para re-criar a tabela original, MySQL não permitirá inserções concorrentes em um INSERT ... SELECT.

Você também pode utilizar REPLACE em vez de INSERT para sobrescrever linhas antigas. REPLACE é a contra parte para INSERT IGNORE no tratamento de novas linhas contendo valores de chave únicos que duplicam linhas antigas: As novas linhas são usadas para substituir as linhas antigas em vez de descartá-las.

6.4.3.2. Sintaxe INSERT DELAYED

INSERT DELAYED ...

A opção DELAYED para a instrução INSERT é um opção específica do MySQL que é muito útil se você tiver clientes que não possam esperar que o INSERT se complete. Este é um problema comum quando você utiliza o MySQL para fazer log e você também execute periodicamente instruções SELECT e UPDATE que levem muito tempo para completar. DELAYED foi intriduzido no MySQL versão 3.22.15. Ela é uma extensão do MySQL ao SQL-92.

INSERT DELAYED só funciona com tabelas ISAM e MyISAM. Note que como tabelas MyISAM suportam SELECT e INSERT concorrentes, se não houver blocos livres no meio do arquivo de dados, você raramente precisará utilizar INSERT DELAYED com MyISAM. Para mais informações sobre isto, veja Seção 7.1, "Tabelas MyISAM".

Quando você utiliza INSERT DELAYED, o cliente irá obter um OK de uma vez e a linha será inserida quando a tabela não estiver sendo usada por outra thread.

Outro grande benefício do uso de INSERT DELAYED e que inserções de muitos clientes são empacotados juntos e escritos em um bloco. Isto é muito mais rápido que se fazer muitas inserções seperadas.

Note que atualmente as linhas enfileirdas só são armazenadas em memória até que elas sejam inseridas na tabela. Isto significa que se você matar o mysqld com kill -9 ou se o mysqld finalizar inesperadamente, as linhas enfileiradas que não forma escritas em disco são perdidas.

A seguir temos uma descrição em detalhes do que acontece quando você utiliza a opção DELAYED com INSERT ou REPLACE. Nesta descrição, a ``thread'' e a thread que recebe um comando INSERT DELAYED e ``handler'' é a thread que trata todas as instruções INSERT DELAYED de uma tabela particular.

  • Quando uma thread executa uma instrução DELAYED em uma tabela, uma thread handler é criada para processar todas as instruções DELAYED para a tabela, se tal handler ainda não existir.
  • A thread verifica se o handler já adquiriu uma trava DELAYED; se não, ele diz a thread handler para fazê-lo. A trava DELAYED pode ser obtida mesmo se outras threads tiver uma trava de LEITURA ou ESCRITA na tabela. De qualquer forma, o handler irá esperar por todas as travas ALTER TABLE ou FLUSH TABLES para se assegurar que a estrutura da tabela está atualizada.
  • A thread executa a instrução INSERT, mas em vez de escrever a linha na tabela, ela põe uma cópia da linha final na fila que é gerenciada pela thread handler. Quaisquer erros de sintaxe são notificados pela thread e relatadas ao programa cliente.
  • O cliente não pode relatar o número de duplicatas ou o valor AUTO_INCREMENT para a linha resultante; ele não pode obtê-los do servidor, pois o INSERT retorna antes da operação de inserção ser completada. Se você utiliza a API C, a função mysql_info() não irá retornar nada significante, pela mesma razão.
  • O log binário é atualizado pela thread handler quando a linha é inserida na tabela. No caso de inserção de múltiplas linhas, o log binário é atualizado quando a primeira linha é inserida.
  • Depois que todas as linhas delayed_insert_limit são escrita, o handle verifica se alguma instrução SELECT está pendente. Se estiver, ele permite que ela seja executada antes de continuar.

  • Quando o handler não tiver mais linhas na fila, a tabela é destravada. Se nunhum comando INSERT DELAYED novo é recebido dentro de delayed_insert_timeout segundos, o handler termina.
  • Se mais que delayed_queue_size estão pendentes em uma fila handler específica, a thread requisitando INSERT DELAYED espera até que haja espaçõ na fila. Isto é feito para assegurar que o servidor mysqld não utilize toda a memória área de memória de atraso.
  • A thread handler irá aparecer na lista de processos do MySQL process list com delayed_insert na coluna Command. Ela será finalizada se você executar um comando FLUSH TABLES ou matá-la com KILL thread_id. No entanto, primeiro ela armazenará todas as linhas enfileiradas na tabela antes de sair. Durante este tempo ela não aceitará nenhum comando INSERT novo da outra thread. Se você executar um comando INSERT DELAYED depois disto, uma nova thread handler será criada.

    Note que o mostrado acima significa que o comando INSERT DELAYED tem prioridade maior que um comando INSERT normal se já houver um handler INSERT DELAYED em execução! Outro comando de atualização terá que esperar até que a fila INSERT DELAYED esteja vazia, alguém finalize a thread handler (com KILL thread_id), ou alguém execute FLUSH TABLES.

  • As seguintes variáveis de estado fornecem informção sobre comandos INSERT DELAYED:

    VariávelSignificado
    Delayed_insert_threadsNúmero de threads handler
    Delayed_writesNúmeros de linhas escrita com INSERT DELAYED
    Not_flushed_delayed_rowsNúmero de linhas esperando para serem escritas

    Você pode visualizar estas variáveis com a instrução SHOW STATUS ou executando um comando mysqladmin extended-status.

Note que INSERT DELAYED é mais lento que um INSERT normal se a tabela não estiver em uso. Também há uma sobrecarga adicional para o servidor tratar um thread separada para cada tabela na qual você utiliza INSERT DELAYED. Isto significa que você só deve usar INSERT DELAYED quando você estiver certo de necessita dele!

6.4.4. Sintaxe UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] nome_tabela
SET nome_coluna1=expr1 [, nome_coluna2=expr2 ...]
[WHERE definição_where]
[ORDER BY ...]
[LIMIT row_count]
ou
UPDATE [LOW_PRIORITY] [IGNORE] nome_tabela [, nome_tabela ...]
SET nome_coluna1=expr1 [, nome_coluna2=expr2 ...]
[WHERE definição_where]

UPDATE atualiza uma coluna em registros de tabelas existentes com novos valores. A cláusula SET indica quais colunas modificar e os valores que devem ser dados. A cláusula WHEREi, se dada, especifica quais linhas devem ser atualizadas. Senão todas as linhas são atualizadas. Se a cláusula ORDER BY é especificada, as linhas serão atualizada na ordem especificada.

Se você especificar a palavra-chave LOW_PRIORITY, a execução de UPDATE e atrasada até que nenhum outro cliente esteja lendo da tabela.

Se você especificar a palavra-chave IGNORE, a instrução não será abortada memso se nós obtermos erros de chaves duplicadas durante a atualização. Linhas que causem conflitos não serão atualizadas.

Se você acessa um coluna de nome_tabela em uma expressão, UPDATE utiliza o valor atual da coluna. Por exemplo, a seguinte instrução define a coluna age com o valor atual mais um:

mysql> UPDATE persondata SET age=age+1;

Atribuiçãoes UPDATE são avaliadas da esquerda para a direitat. Por exemplo, a seguinte instrução dobra a coluna age e então a incrementa:

mysql> UPDATE persondata SET age=age*2, age=age+1;

Se você define uma coluna ao valor que ela possui atualmente, o MySQL notará isto é não irá atualizá-la.

UPDATE retorna o número de linhas que forma realmente alteradas. No MySQL Versão 3.22 ou posterior, a função mysql_info() da API C retorna o número de linhas encontradas e atualizadas e o número de avisos que ocorreram durante o UPDATE.

A partir do MySQL versão 3.23, você pode utilizar LIMIT row_count para restringir o escopo do UPDATE. Uma cláusula LIMIT funciona da seguinte forma:

  • Antes do MySQL 4.0.13, LIMIT é uma restrição que afeta as linhas. A instrução para assim que altera row_count linhas que satisfaçam a cláusula WHERE.
  • Da versão 4.0.13 em diante, LIMIT é uma restrição de linhas correspondentes. A instrução para assim que ela encontrar row_count linhas que satisfaçam a cláusula WHERE, tendo elas sido alteradas ou não.

Se uma cláusula ORDER BY é utilizada (disponível no MySQL 4.0.0), as linhas serão atualizadas nesta ordem. Isto só é util em conjunto com LIMIT.

A partir da MySQL Versão 4.0.4, você também pode realizar operações UPDATE que cobrem múltiplas tabelas:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

O exemplo mostra um inner join usando o operador de vírgula, mas instruções UPDATE multi-tabelas podem usar qualquer tipo de join permitida na instrução SELECT, como LEFT JOIN.

Nota: você não pode utilizar ORDER BY ou LIMIT com multi-tabelas UPDATE.

6.4.5. Sintaxe DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
[WHERE definição_where]
[ORDER BY ...]
[LIMIT row_count]
ou
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.*] [, table_name[.*] ...]
FROM tabelas-referentes
[WHERE definição_where]
ou
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM nome_tabela[.*] [, nome_tabela[.*] ...]
USING tabelas-referentes
[WHERE definição_where]

DELETE deleta linhas de nome_tabela que satisfaçam a condição dada por definição_where, e retorna o número de registros deletados.

Se você exeecutar um DELETE sem cláusula WHERE, todas as linhas são deletadas. Se você o fizer no modo AUTOCOMMIT, isto irá funcionar como TRUNCATE. Para mais informações sobre isto, veja Seção 6.4.6, "Sintaxe TRUNCATE". No MySQL 3.23, DELETE sem uma cláusula WHERE retornará zero como o número de registros afetados.

Se você realmente quiser saber quantos registros são deletados quando você deletar todas as linhas mesmo sofrendo uma com a queda da velocidade, você pode utilizar uma instrução DELETE desta forma:

mysql> DELETE FROM nome_tabela WHERE 1>0;

Note que isto é muito mais lento que DELETE FROM nome_tabela sem cláusula WHERE, pois ele deleta uma linha de cada vez.

Se você especificar a palavra-chave LOW_PRIORITY, a execução do DELETE é atrasda até que nenhum outro cliente esteja lendo da tabela.

Para tabelas MyISAM, Se você especificar a palavra QUICK, o mecanismo de armazenamento não irá fundir os índices excluídos durante a deleção, o que pode aumentar a velocidade de certos tipos de deleção.

A velocidade das operações de deleção também pode ser afetadas pelos fatores discutidos em Seção 5.2.12, "Performance das Consultas que Utilizam DELETE".

A opção IGNORE faz com que o MySQL ignore todos os erros durente o processo de deleção dos registros. Erros encontrados durante o estágio de análise são processados da maneira comum. Erros que são ignorados devido ao uso desta opção são retornados como aviso. Esta opção aparece pela primeira vez na versão 4.1.1.

Em tabelas MyISAM, registros deletados são mantidos em uma lista encadeada e operções INSERT subsequentes reutilizam posições de registros antigos. Para recuperar espeços não utilizados e reduzir o tamanho do arquivo, utilize a instrução OPTIMIZE TABLE ou o utiliztário myisamchk para reorganizar as tabelas. OPTIMIZE TABLE é mais fácil, mas myisamchk é mais rápido. Veja Seção 4.6.1, "Sintaxe de OPTIMIZE TABLE" e Seção 4.5.6.10, "Otimização de Tabelas".

O primeiro formato de delção de multi-tabelas é suportado a partir do MySQL 4.0.0. O segundo formato de deleção multi-tabelas é suportado a partir do MySQL 4.0.2.

A idéia é que apenas linhas coincidentes da tabelas listadas antes de FROM ou antes da cláusula USING são deletadas. O efeito é que você pode deletar l;inhas de muitas tabelas ao mesmo tempo e também ter tabelas adicionais que são utilizadas para busca.

O .* depois do nome da tabela existe apenas para ser compatível com o Access:

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
ou
DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

No cso acima nós deletamos linhas coincidente apenas na tabela t1 e t2.

O exemplo mostra um inner join usando o operador de vírgula, mas instruções UPDATE multi-tabelas podem usar qualquer tipo de join permitida na instrução SELECT, como LEFT JOIN.

Se uma cláusula ORDER BY é utilizada (disponível no MySQL 4.0.0), as linhas serão deletadas naquela ordem. Isto só é útil se usado em conjunto com LIMIT. Por exemplo:

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

Isto irá deletar as entradas antigas (por timestamp) onde as linhas casam com a cláusula WHERE.

A opção específica do MySQL LIMIT row_count para DELETE diz ao servidor o número máximo de linhas a serem deletadas antes do controle retornar ao cliente. Isto pode ser usado para assegurar que uma comando DELETE específico mão tomará muito tempo, Você pode simplesmente repetir o comando DELETE até que o número de linhas afetadas seja menor que o valor LIMIT.

No MySQL 4.0, você pode especificar múltiplas tabelas na instrução DELETE para deletar linhas de uma ou mais tabelas dependendo de uma condição particular em várias tabelas. No entanto você não pode utilizar ORDER BY ou LIMIT em uma multi-tabela DELETE.

6.4.6. Sintaxe TRUNCATE

TRUNCATE TABLE nome_tabela

Na versão 3.23 TRUNCATE TABLE é mapeada para COMMIT; DELETE FROM table_name. Para mais informações sobre isto, veja Seção 6.4.5, "Sintaxe DELETE".

TRUNCATE TABLE difere de DELETE FROM ... do seguinte modo:

  • Operações truncate apagam e recriam a tabela, o que é muito mais rápido que deletar registros um a um.
  • Operações truncate não são seguras a transação; você iráobter um erro se você tiver uma transação ativa ou ativar um travamento de tabela.
  • O número de linhas apagadas não é retornado.
  • Uma vez que o arquivo de definição nome_tabela.frm deja válido, a tabela pode ser recriada deta forma, mesmo se o arquivo de dados ou de índice estiver corrompido.

TRUNCATE é uma extensão Oracle SQL. Esta instrução foi adicionada no MySQL 3.23.28, embora da versão 3.23.28 a 3.23.32, a palavra chave TABLE deva ser omitida.

6.4.7. Sintaxe REPLACE

 REPLACE [LOW_PRIORITY | DELAYED]
[INTO] nome_tabela [(nome_coluna,...)]
VALUES (expressão,...),(...),...
ou REPLACE [LOW_PRIORITY | DELAYED]
[INTO] nome_tabela [(nome_coluna,...)]
SELECT ...
ou REPLACE [LOW_PRIORITY | DELAYED]
[INTO] nome_tabela
SET nome_coluna=expressão, nome_coluna=expressão,...

REPLACE funciona exatamente como o INSERT, exceto que se um registro antigo na tabela tem o mesmo valor que um novo registro em um índice UNIQUE ou PRIMARY KEY, o registro antigo é deletado antes que o novo registro seja inserido. Veja mais informações sobre isto na Seção 6.4.3, "Sintaxe INSERT".

Em outras palavras, você não pode acessar os valores do registro antigo em uma instrução REPLACE. Em algumas versões antigas do MySQL aparentemente você podia fazer isto, mas era um bug que já foi arrumado.

Par aestar apto a utilizar REPLACE você deve ter privilégios INSERT e DELETE para a tabela.

Quando você utilizar um comando REPLACE, mysql_affected_rows() retornará 2 se a nova linha substituir uma linha antiga. Isto é porque uma linha foi inserida depois que a linha duplicada foi deletada.

Este fato torna fácil determinar se REPLACE adicionou ou subsitituiu uma linha: verifique se o valor de linhas afetadas é 1 (adicionado) ou 2 (substituido).

Note que a menos que a tabela utilize índices UNIQUE ou PRIMARY KEY, utilizar um comando REPLACE replace não faz sentido. Ele se torna equivalente a um INSERT, porque não existe índice a ser usado para determinar se uma nova linha duplica outra.

Seqgue aqui o algoritmo usado em mais detalhes: (Ele também é usado com LOAD DATA ... REPLACE.

- Insere a linha na tabela
- Enquanto ocorrer erro de chave duplicada para chaves primária ou única
- Reverte as chaves alteradas
- Le as linha conflitantes da tabela através do valor da chave duplicada
- Deleta as linhas conflitantes
- Tenta inserir o chave primária e única original na árvore

6.4.8. Sintaxe LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE nome_tabela
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE número LINES]
[(nome_coluna,...)]

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 MySQL 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. Para mais informações sobre isto, veja Seção 4.3.7, "Privilégios Fornecidos pelo MySQL".

A partir do MySQL 3.23.49 e MySQL 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. Veja mais informações sobre isto na Seção 4.3.4, "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 MySQL 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). Para mais informações sobre isto, veja Seção 6.4.7, "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. Veja mais informações sobre isto na Seção 4.5.6, "Utilizando myisamchk para Manutenção de Tabelas e Recuperação em Caso de Falhas".

LOAD DATA INFILE é o complemento de SELECT ... INTO OUTFILE. Para mais informações sobre isto, veja Seção 6.4.1, "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 Seção 6.1.1, "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 indistinguí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 MySQL 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 Seção 6.5.3, "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. Para mais informações sobre isto, veja Seção 6.2.2, "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 MySQL 4.1.1 o aviso é apenas um número que indica que alguma coisa deu errado. No MySQL 4.1.1 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 Seção 6.4.3, "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 MySQL a anterior a 3.23.25 você só poderá fazer o descrito acima com LOAD DATA LOCAL INFILE.

No MySQL 4.1.1 você pode usar SHOW WARNINGS para conseguir a lista do primeiros max_error_count avisos. Para mais informações sobre isto, veja Seção 4.6.8.9, "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, Veja mais informações sobre isto na Seção 5.2.10, "Performance das Consultas que Utilizam INSERT".

6.4.9. Sintaxe HANDLER

HANDLER nome_tabela OPEN [ AS alias ]
HANDLER nome_tabela READ nome_indice { = | >= | <= | < } (valor1,valor2,...)
[ WHERE ... ] [LIMIT ... ]
HANDLER nome_tabela READ nome_indice { FIRST | NEXT | PREV | LAST }
[ WHERE ... ] [LIMIT ... ]
HANDLER nome_tabela READ { FIRST | NEXT }
[ WHERE ... ] [LIMIT ... ]
HANDLER nome_tabela CLOSE

A instrução HANDLER fornece acesso direto a interface do mecanismo de armazenamento de tabelas MyISAM.

A primeira forma da instrução HANDLER abre uma tabela, tornando a acessível através de subsequentes instruções HANDLER ... READ. Este objeto de tabela não é copartilhada com outras threads e não serão fechadas até que as chamadas de thread HANDLER nome_tabela CLOSE ou a thread termine.

A segunda forma busca um registro (ou mais, especificado pela cláusula LIMIT) onde o índice especificado satisfaz os valores dados e a condição WHERE é encontrada. Se você tiver um índice multi-coluna, especifique as colunas do índice como uma lista separadas por vírgulas. Especifique o valor de todas as colunas no índice, ou especifique valores para o prefixo mais a esquerda das colunas índices. Suponha que um índice inclui três colunas chamadas col_a, col_b, e col_c, nesta ordem. A instrução HANDLER pode especificar valores para todas as três colunas no índice, ou para as colunas no prefixo mais a esquerda. Por exemplo:

HANDLER ... index_name = (col_a_val,col_b_val,col_c_val) ...
HANDLER ... index_name = (col_a_val,col_b_val) ...
HANDLER ... index_name = (col_a_val) ...

A terceira forma busca uma linha (ou mais, especificado pela cláusula LIMIT) da tabela na ordem do índice, correspondendo a condição WHERE.

A quarta forma (sem especificação de índice) busca um registro (ou mais, especificado pela cláusula LIMIT) da tabela na ordem natural da linhas (como armazenado no arquivo de dados) de acordo com a condição WHERE é mais rápido que HANDLER nome_tabela READ nome_indice quando é necessária uma varredura completa da tabela.

HANDLER ... CLOSE fecha uma tabela que foi aberta com HANDLER ... OPEN.

Nota: Se você estiver utilizando a interface HANDLER para PRIMARY KEY você deve se lembrar de colocar o nome entre aspas: HANDLER tbl READ `PRIMARY` > (...)

HANDLER é uma instrução de baixo nível. Por exemplo, ela não fornece consitência. Isto é, HANDLER ... OPEN NÃO pega uma imagem instântanea da tabela, e NÃO trava a tabela. Isto significa que depois que um HANDLER ... OPEN é feito, os dados da tabela podem ser modificados (por esta ou outra thread) e estas modificações podem aparecer apenas parcialmente nas buscas HANDLER ... NEXT ou HANDLER ... PREV.

As razões para se utilizar esta interface em vez do SQL normal são:

  • Ela é mais rápida que SELECT porque:

    • Um mecanismo de armazenamento designado é alocado pela thread em HANDLER OPEN.
    • Existe menos análise envolvida.
    • Nào existe sobrecaga de otimização e verificação de consultas.
    • A tabela utilizada não precisa estar travada em pedidos de dois handlers.
    • A interface handler não precisa fornecer uma aprência consistente dos dados (por exemplo, dirty-reads são permitidas), assim o mecanismo de armazenamento pode fazer otimizações que o SQL normalmente não permite.
  • É muito mais fácil portar aplicações que usam interface como ISAM para o MySQL.
  • Ele permite se fazer uma travessia em um banco de dados de uma maneira que não é facil (em alguns casos impossível) de fazer com SQL. A interface handler é um modo mais natural de mostrar dados ao trabalhar com aplicações que fornecem uma interface interativa com o utilizador para o banco de dados.

6.4.10. Sintaxe DO

DO expressão, [expressão, ...]

Executa a expressão mas não retorna nenhum resultado. Este é um modo curto de SELECT expressão, expressão, mas tem a vantagem de ser rápida quando você não se preocupa com o resultado.

Ele é útil principalmente com funções que tem efeitos em um dos lados, como RELEASE_LOCK.