XOOPS Brasil

 

A.5. Assuntos Relacionados a Consultas

A.5.1. Caso-Sensitivito em Pesquisas

Por padrão, as pesquisas no MySQL são caso-insensitivo (a menos que haja algum conjunto de caracter que nunca seja caso-insensitivo, com czech). Isto significa que se você buscar com nome_coluna LIKE 'a%', você obterá todos os valores de colunas que iniciam com A ou a. Se você quiser fazer esta busca caso-sensitivo, use algo como INSTR(nome_coluna, "A")=1 para verificar o prefixo. Ou use STRCMP(nome_coluna, "A") = 0 se o valor da coluna deve se exatamente "A".

Operações de comparações simples (>=, >, = , < , <=, ordenando e agrupando) são basedos em cada ``valor de ordenação'' do caracter. Caracteres com o mesmo valor de ordenação (como 'E', 'e' e 'é') são tratados como o mesmo caracter!

Em versões antigas do MySQL, comparações com LIKE eram feitas com o valor de letra maiúscula de cada caracter (E == e mas E <> é). Nas versões mais novas, LIKE funciona assim como os outros operadores de comparação.

Se você quiser que uma coluna sempre seja tratada de modo caso-sensitivo, declare a como BINARY. Veja mais informações sobre isto na Seção 6.5.3, “Sintaxe CREATE TABLE.

Se você está usando caracteres Chineses na codificação big5, você pode tornar todas as colunas de caracteres BINARY. Isto funciona porque a ordenação de caracteres de codificação big5 é baseada na ordem do código ASCII.

A.5.2. Problemas Usando Colunas DATE

O formato de um valor DATE é 'YYYY-MM-DD'. De acordo com o padrão SQL, nenhum outro formato é permitido. Você deve usar este formato em expressões UPDATE e na cláusula WHERE de insrtruções SELECT. Por exemplo:

mysql> SELECT * FROM nome_tabela WHERE date >= '1997-05-05';

Por conveniência, o MySQL converte automaticamente uma data em um número se a data é usada em um contexto numérico (e vice versa). Ele também é esperto o bastante para permitir uma forma de string ``relaxada'' em uma atualização e em uma cláusula WHERE que compara uma data a uma coluna TIMESTAMP, DATE, ou DATETIME. (Forma relaxada significa que qualquer caracter de pontuação pode seu usado como separador entre as partes. Por exemplo, '1998-08-15' e '1998#08#15' são equivalentes). O MySQL também pode converter uma string sem separadores (como '19980815'), desde que ela faça sentido como uma data.

A data especial '0000-00-00' pode ser armazenada e recuperada como '0000-00-00'. Ao usar uma data '0000-00-00' com o MyODBC, ele a converterá automaticamente em NULL em sua versão 2.50.12 e acima, porqie o ODBC não pode tratar este tipo de data.

Como o MySQL realiza a conversão descrita acima, a seguinte instrução funcionará:

mysql> INSERT INTO nome_tabela (idate) VALUES (19970505);
mysql> INSERT INTO nome_tabela (idate) VALUES ('19970505');
mysql> INSERT INTO nome_tabela (idate) VALUES ('97-05-05');
mysql> INSERT INTO nome_tabela (idate) VALUES ('1997.05.05');
mysql> INSERT INTO nome_tabela (idate) VALUES ('1997 05 05');
mysql> INSERT INTO nome_tabela (idate) VALUES ('0000-00-00');
mysql> SELECT idate FROM nome_tabela WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM nome_tabela WHERE idate >= 19970505;
mysql> SELECT MOD(idate,100) FROM nome_tabela WHERE idate >= 19970505;
mysql> SELECT idate FROM nome_tabela WHERE idate >= '19970505';

No entatnto o seguinte não funcionará:

mysql> SELECT idate FROM nome_tabela WHERE STRCMP(idate,'19970505')=0;

STRCMP() é uma função string, assim ela converte idate em uma string e realiza um comparação de string. Ela não converte '19970505' em uma datae e realiza uma comparaçãas de data.

Note que o MySQL faz uma verificação muito limitada da validade da data. Se você aramazenar uma data incorreto, tal como '1998-2-31', a data invalida será armazenada.

Como o MySQL empacota a data para armazenamento, ele não pode armazenar qualquer data dada como já que ela não caberia dentro do buffer de resultado. As regras de aceitação das datas são:

  • Se o MySQL pode armazenar e recuperar um data dada, a data errada é acieta para colunas DATE e DATETIME.

  • Todos os valores de dia entre 0-31 são aceitos para qualquer data. Isto torna muito conveniente para plicações web nas quais você pede ano, mês e dia em 3 campos diferentes.

  • O campo do dia ou mês pode ser zero. Isto é conveniente se você quiser armazenar uma data de aniversário em uma coluna DATE e você não sabea parte da data.

Se a data não pode ser convertida para qualquer valor razoável, um 0 é armazenado no campo DATE, o qual será recuperado como 0000-00-00. Isto é uma questão tanto de velocidade quanto de conveniência já que acreditamos que a responsabilidade do banco de dados é recuperar a mesma data que você armazenou (mesmo se a data não era logicamente correta em todos os casos). Nós pensamos que é papel da aplicação verificar as datas, e não do servidor.

A.5.3. Problemas com Valores NULL

O conceito do valor NULL é uma fonte comum de confusão para os iniciantes em SQL, que frequentemente pensa que NULL é a mesma coisa que uma string vazia "". Este não é o caso! Por exemplo, as seguintes intruções são completamente diferentes:

mysql> INSERT INTO minha_tabela (telefone) VALUES (NULL);
mysql> INSERT INTO minha_tabela (telefone) VALUES ("");

Ambas as intruções inserem um valor na coluna telefone, mas a primeira insere um valor NULL e a segunda insere uma string vazia. O significado do primeiro pode ser considerado como ``telefone não é conhecido'' e o significado da segunda pode ser considerado como ``ela não tem telefone''.

Em SQL, o valor NULL é sempre falso em coparação a qualquer outro valor, mesmo NULL. Uma expressão que contém NULL sempre produz um valor NULL a menos que seja indicado na documentação para os operadores e funções involvidos na expressão. Todas as colunas no seguinte exemplo retornam NULL:

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

Se você quiser procurar por uma coluna cujo valor é NULL, você nãp pode usar o teste =NULL. A seguinte instrução não retorna nenhuma linha, pois expr = NULL é FALSO, para qualquer expressão:

mysql> SELECT * FROM minha_tabala WHERE phone = NULL;

Para procurar por valores NULL, você deve usar o teste IS NULL. A seguir mostramos como encontrar o némuro de telefone NULL e o número de telefone vazio:

mysql> SELECT * FROM minha_tabela WHERE telefone IS NULL;
mysql> SELECT * FROM minha_tabela WHERE telefone = "";

Note que você pode adicionar um índice a uma coluna que tenha valores NULL apenas se você estiver usando o MySQL versão 3.23.2 ou mais novo e estiver usando tipos de tabelas NyISAM, InnoDB ou BDB. Em versões anteriores e com outros tipos de tabelas, você deve declara tais colunas como NOT NULL. Isto também significa que você então não poderá inserir NULL em uma coluna indexada.

Ao ler dados com LOAD DATA INFILE, colunas vazias são atualizadas com ''. Se você quiser um valor NULL em uma coluna, você deve usar \N no arquivo texto. A palavra literal 'NULL' também pode ser usada em algumas circunstâncias. Veja mais informações sobre isto na Seção 6.4.8, “Sintaxe LOAD DATA INFILE.

Ao usar ORDER BY, valores NULL são apresentados primeiro, ou por último se você especificar DESC para armazenar em ordem decrescente. Exceção: Nos MySQL 4.0.2 até 4.0.10, se você armazenar em ordem decrescente usando DESC, valores NULL são apresentados por último.

Ao usar GROUP BY, todos os valores NULL são considerados iguais.

Funções de agrupamento (resumo) como COUNT(), MIN() e SUM() ignoram valores NULL. A exceção a isto é COUNT(*), que conta linhas e não colunas individuais. Por exemplo, a seguinte instrução deve produzir duas contagens. A primeira é a contagem do número de linhas na tabela e a segunda é a contagem do número de valores diferentes de NULL na coluna age:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

Para ajudar com o tratamento de NULL, você pode usar os operadores IS NULL e IS NOT NULL e a função IFNULL().

Para alguns tipos de colunas, valores NULL são tratados de forma especial, Se você inserir NULL na primeira coluna TIMESTAMP de uma tabela, a data e hora atual serão inseridos. Se você isere NULL em uma coluna AUTO_INCREMENT, o próximo número na sequência é inserida.

A.5.4. Problemas com alias

Você pode usar um apelido para referir a uma coluna no GROUP BY, ORDER BY, ou na parte HAVING. Aliases podem ser usados para dar as colunas nomes melhores:

SELECT SQRT(a*b) as rt FROM nome_tabela GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM nome_tabela GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM nome_tabela;

Note que o padrão SQL não permite que você se refira a uma alias na cláusula WHERE. Isto é porque quando o código WHERE é executado o valor da coluna ainda não pode ser determinado. Por exemplo, a seguinte consulta é ilegal:

SELECT id,COUNT(*) AS cnt FROM nome_tabela WHERE cnt > 0 GROUP BY id;

A instrução WHERE é executada para determinar quais linhas devem ser incluídas na parte GROUP BY enquanto HAVING é usado para decidir quais linhas o conjunto de resultados deve usar.

A.5.5. Deletando Linhas de Tabelas Relacionadas

Como o MySQL não suporta subconsultas (antes da versão 4.1), enm o uso de mais de uma tabela na instruçao DELETE (antes da versão 4.0), você deve usar a seguinte abordagem para deletar linhas de 2 tabelas relacionadas:

  1. SELECT as linhas baseado em alguma condição WHERE na tabela principal.

  2. DELETE as linhas da tabela princiapl basada nas mesmas condições.

  3. DELETE FROM tabela_relacionada WHERE coluna_relacionada IN (linhas_selecionadas).

Se o número total de caracteres na consulta com colunas_relacionadas é maior que 1,048,576 (o valor padrão de max_allowed_packet, você deve separá-lo em duas partes menores e executar múltiplas instruções DELETE. Você provavelmente obterá o DELETE mais rápido apenas delatando 100-1000 ids de colunas_relacionadas por consulta se colunas_relacionadas é um índice. Se colunas_relacionadas não é um índice, a velocidadi é independente do número de argumentos na cláusula IN.

A.5.6. Resolvendo Problemas Com Registros Não Encontrados

If you have a complicated query that has many tables and that doesn't return any rows, you should use the following procedure to find out what is wrong with your query:

  1. Teste a consulta com EXPLAIN e verifique se você pode encontrar alguma coisa que está errada. Veja mais informações sobre isto na Seção 5.2.1, “Sintaxe de EXPLAIN (Obter informações sobre uma SELECT)”.

  2. Selcione apenas aqueles campos que são usados na cláusula WHERE.

  3. Remova uma tabela por vez da consulta até que ela retorne alguns registros. Se as tabelas são grandes, é uma boa idéia usar LIMIT 10 com a consulta.

  4. Faça um SELECT da coluna encontrou um registro com a tabela que foi removido por última da consulta.

  5. Se você estiver comparando colunas FLOAT ou DOUBLE com números que tenham decimais, você não pode usar '='. Este problema é comum na maioria das linguagens de computadores porque valores de ponto-flutuante não são valores exatos. Na maioria dos casos, alterar o FLOAT por DOUBLE corrigirá isto. Veja mais informações sobre isto na Seção A.5.7, “Problemas com Comparação de Ponto Flutuante”.

  6. Se você ainda não pode imaginar o que está errado, crie um teste mínimo que possa ser executado com mysql test < query.sql e possa mostrar seus problemas. Você pode criar um arquivo de teste com mysqldump --quick banco_de_dados tabela > query.sql. Abra o arquivo em um editor, remova algumas linhas inseridas (se houver muitas) e adicione sua instrução select no fim do arquivo.

    Teste se você ainda está tendo problemas fazendo:

    shell> mysqladmin create test2
    shell> mysql test2 < query.sql
    

    Envie o arquivo de teste usando mysqlbug para lista de email gerais do MySQL. Veja mais informações sobre isto na Seção 1.7.1.1, “As Listas de Discussão do MySQL”.

A.5.7. Problemas com Comparação de Ponto Flutuante

Números de ponto flutuante geram confusões algumas vezes, pois estes números não são armazenados como valores exatos dentro da arquitetura dos computadores. O que pode ser ver na tela não é o valor exato do número.

Tipos de campos FLOAT, DOUBLE e DECIMAL são assim.

CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
(6, 0.00, 0.00), (6, -51.40, 0.00);
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
-> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i | a | b |
+------+--------+-------+
| 1 | 21.40 | 21.40 |
| 2 | 76.80 | 76.80 |
| 3 | 7.40 | 7.40 |
| 4 | 15.40 | 15.40 |
| 5 | 7.20 | 7.20 |
| 6 | -51.40 | 0.00 |
+------+--------+-------+

O resultado está correto. Embora pareça que os primeiros cinco registros não devessem passar no teste de comparação, eles deviam porque a diferença entre o número mostrado está na décima casa decimal ou depende da arquitetura do computador.

O problema não pode ser resolvido usando ROUND() (ou função similar), porque o resultado ainda é um número de ponto flutuante. Exemplo:

mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b
-> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i | a | b |
+------+--------+-------+
| 1 | 21.40 | 21.40 |
| 2 | 76.80 | 76.80 |
| 3 | 7.40 | 7.40 |
| 4 | 15.40 | 15.40 |
| 5 | 7.20 | 7.20 |
| 6 | -51.40 | 0.00 |
+------+--------+-------+

É assim que o número da coluna 'a' se parece:

mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a,
-> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b;
+------+----------------------+-------+
| i | a | b |
+------+----------------------+-------+
| 1 | 21.3999999999999986 | 21.40 |
| 2 | 76.7999999999999972 | 76.80 |
| 3 | 7.4000000000000004 | 7.40 |
| 4 | 15.4000000000000004 | 15.40 |
| 5 | 7.2000000000000002 | 7.20 |
| 6 | -51.3999999999999986 | 0.00 |
+------+----------------------+-------+

Dependendo da arquitetura do computador você pode ou não ver resultados similares. Cada CPU pode avaliar um númere de ponto flutuante de forma diferente. Por exemplo, em alguma máquinas você pode obter resultados 'corretos' multiplicando ambos argumentos por 1, como no exemplo a seguir.

AVISO: NUNCA CONFIE NESTE MÉTODO EM SUAS APLICAÇÕES, ESTE É UM EXEMPLO DE UM MÉTODO ERRADO!!!

mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b
-> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+------+
| i | a | b |
+------+--------+------+
| 6 | -51.40 | 0.00 |
+------+--------+------+

A razão pela qual o método acima parece funcionar é que na máquina onde o teste foi realizado, a CPU de aritimética de ponto flutuante é realizada arredondando números para serem iguais, mas não há nenhuma regra que qualquer CPU deva fazer assim, então isto não é confiável.

O modo correto de fazermos comparações de ponto flutuante é primeiro decidir qual é a tolerância desejada entre os números e então fazer a comparação com o número tolerado. Por exemplo, se nós concordarmos que números de ponto flutuante devem ser considerados o mesmo, se eles forem o mesmo com precisão de quatro casas deciamis (0.0001), a comparação deve ser feita assim:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+--------+------+
| i | a | b |
+------+--------+------+
| 6 | -51.40 | 0.00 |
+------+--------+------+
1 row in set (0.00 sec)

E vice-versa, se nós quisermos obter registros onde os números são o mesmo, o teste seria:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) < 0.0001;
+------+-------+-------+
| i | a | b |
+------+-------+-------+
| 1 | 21.40 | 21.40 |
| 2 | 76.80 | 76.80 |
| 3 | 7.40 | 7.40 |
| 4 | 15.40 | 15.40 |
| 5 | 7.20 | 7.20 |
+------+-------+-------+