XOOPS Brasil

 

Funções para Uso em Cláusulas SELECT e WHERE





Um select_expression ou where_definition em uma instrução SQL pode consistir de qualquer expressão utilizando as funções descritas abaixo.

Uma expressão que contém NULL sempre produz um valor NULL a menos que esteja indicado na dodumentação para os operandos e funções envolvidos na expressão.

Nota: Não deve haver nenhum espaço em branco entre um nome de função e os parentesis que a seguem. Isto ajuda o analizador MySQL a distinguir entre chamadas de funções e referências a tabelas ou colunas que possuem o mesmo nome de uma função. Espaços entre argumentos são permitidos.

Você pode forçar o MySQL a aceitar espaços depois do nome de funções iniciando o mysqld com a opção --ansi ou usando o CLIENT_IGNORE_SPACE no mysql_connect(), mas neste caso nome de funções se tornarão palavras reservadas. Veja mais informações sobre isto na Seção 1.8.2, “Executando o MySQL no modo ANSI”.

Para sermos breve, exemplos mostram a saida do programa mysql na forma abreviada. Então isto:

mysql> SELECT MOD(29,9);
1 rows in set (0.00 sec)
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+

é mostrado desta forma:

mysql> SELECT MOD(29,9);
-> 2

6.3.1. Operadores e Funções de Tipos não Especificados

6.3.1.1. Parenteses

( ... )

Use parenteses para forçar a ordem em que as expressões serão avaliadas. Por exemplo:

mysql> SELECT 1+2*3;
-> 7
mysql> SELECT (1+2)*3;
-> 9

6.3.1.2. Operadores de Comparação

Operações de comparação resultam em um valor 1 (VERDADEIRO), 0 (FALSO), ou NULL. Estas funções funcionam tanto para tipos numéricos quanto para tipos strings. Strings são convertidas automaticamente para números e números para strings quando necessário (como em Perl).

MySQL realiza comparações de acordo com as seguintes regras:

  • Se um ou ambos os argumentos são NULL, o resultado da comparação é NULL, exceto para o operador <=>.

  • Se ambos os argumentos em uma comparação são strings, eles são comparados como strings.

  • Se ambos os argumentos são inteiros, eles são comparados como inteiros.

  • Valores hexadecimais são tratados como strings binárias se não comparadas a um número.

  • Se uma dos argumentos é uma coluna TIMESTAMP ou DATETIME e o outro argumento é uma constante, a constante é convertida para um timestamp antes da comparação ser realizada. Isto ocorre para ser mais amigável ao ODBC.

  • Em todos os outros casos, os argumentos são coparados como números de ponto flutuante (real).

Por padrão, comparações de string são feita de modo independente do caso, usando o conjunto de caracteres atual (ISO-8859-1 Latin1 por padrão, o qual também funciona de forma excelente para o Inglês).

Se você está comparando strings em caso insensitivo com qualquer dos operadores padrões (=, <>..., mas não o LIKE) espaços em branco no fim da string (espaços, tabs e quebra de linha) serão ignorados.

mysql> SELECT "a" ="A \n";
-> 1

Os seguintes exemplos ilustram a conversão de strings para números para operações de comparação:

mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1

Note que quando você está comparando uma coluna string com um número, o MySQL não pode usar índices para encontrar o valor rapidamente:

SELECT * FROM table_name WHERE string_key=1

A razão para isto é que existem muitas strings diferentes que podem retornar o valor 1: "1", " 1", "1a" ...

  • =

    Igual:

    mysql> SELECT 1 = 0;
    -> 0
    mysql> SELECT '0' = 0;
    -> 1
    mysql> SELECT '0.0' = 0;
    -> 1
    mysql> SELECT '0.01' = 0;
    -> 0
    mysql> SELECT '.01' = 0.01;
    -> 1
    

  • <>, !=

    Diferente:

    mysql> SELECT '.01' <> '0.01';
    -> 1
    mysql> SELECT .01 <> '0.01';
    -> 0
    mysql> SELECT 'zapp' <> 'zappp';
    -> 1
    

  • <=

    Menor que ou igual:

    mysql> SELECT 0.1 <= 2;
    -> 1
    

  • <

    Menor que:

    mysql> SELECT 2 < 2;
    -> 0
    

  • >=

    Maior que ou igual:

    mysql> SELECT 2 >= 2;
    -> 1
    

  • >

    Maior que:

    mysql> SELECT 2 > 2;
    -> 0
    

  • <=>

    Igual para NULL:

    mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
    -> 1 1 0
    

  • IS NULL, IS NOT NULL

    Teste para saber se um valor é ou não NULL:

    mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
    -> 0 0 1
    mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
    -> 1 1 0
    

    Para estar apto a funcionar bem com outros programas, MySQL suporta os seguintes recursos extras quando utiliza-se IS NULL:

    • Você pode encontrar o último registro inserido com:

      SELECT * FROM nome_tabela WHERE auto_col IS NULL
      

      Isto pode ser desabilitado configurando SQL_AUTO_IS_NULL=0. Veja mais informações sobre isto na Seção 5.5.6, “Sintaxe de SET.

    • Para colunas DATE e DATETIME NOT NULL você pode encontrar a data especial 0000-00-00 utilizando:

      SELECT * FROM nome_tabela WHERE coluna_data IS NULL
      

      Isto é necessário para que algums aplicações ODBC funcionem (já que ODBC não tem suporte a data 0000-00-00)

  • expr BETWEEN min AND max

    Se expr é maior que ou igual a min e expr é menor que ou igual a max, BETWEEN retorna 1, senão é retornado 0. Isto é equivalente a expressão (min <= expr AND expr <= max) se todos os argumentos são do mesmo tipo. Senão os tipos são convertidos, conforme as regras acima, e aplicadas a todos os três argumentos. Note que antes da versão 4.0.5 argumentos eram convertidos para o tipo da expr.

    mysql> SELECT 1 BETWEEN 2 AND 3;
    -> 0
    mysql> SELECT 'b' BETWEEN 'a' AND 'c';
    -> 1
    mysql> SELECT 2 BETWEEN 2 AND '3';
    -> 1
    mysql> SELECT 2 BETWEEN 2 AND 'x-3';
    -> 0
    

  • expr NOT BETWEEN min AND max

    O mesmo que NOT (expr BETWEEN min AND max).

  • expr IN (valor,...)

    Retorna 1 se expr é qualquer dos valores na lista IN, senão retorna 0. Se todos os valores são constantes, então os valores são avaliados de acordo com o tipo da expr e ordenado. A busca do item é então feita usando pesquisa binária. Isto significa que IN é muito rápido se os valores da lista IN forem todos contantes. Se expr é uma expressão strig em caso-sensitivo, a comparação é realizadas no modo caso-sensitvo:

    mysql> SELECT 2 IN (0,3,5,'wefwf');
    -> 0
    mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
    -> 1
    

    O número de valores na lista IN é limitada apenas pelo valor max_allowed_packet.

    Na versão 4.1 (para se adequar ao padrão SQL-99), IN returna NULL não apeans se a expressão a sua esquerda é NULL, mas também se nenhuma correspondência é encontrada na lista e uma de suas expressões é NULL.

    A partir do MySQL versão 4.1, uma cláusula IN() também pode conter uma subquery. Veja mais informações sobre isto na Seção 6.4.2.3, “Subqueries with ANY, IN, and SOME.

  • expr NOT IN (valor,...)

    O mesmo que NOT (expr IN (valor,...)).

  • ISNULL(expr)

    Se expr é NULL, ISNULL() retorna 1, senão retorna 0:

    mysql> SELECT ISNULL(1+1);
    -> 0
    mysql> SELECT ISNULL(1/0);
    -> 1
    

    Note que a compração de valores NULL usando = sempre será falso!

  • COALESCE(lista)

    Retorna o primeiro elemento não NULL na lista:

    mysql> SELECT COALESCE(NULL,1);
    -> 1
    mysql> SELECT COALESCE(NULL,NULL,NULL);
    -> NULL
    

  • INTERVAL(N,N1,N2,N3,...)

    Retorna 0 se N < N1, 1 se N < N2 e assim por diante ou -1 se N é NULL. Todos os argumentos são tratados como inteiros. Isto exige que N1 < N2 < N3 < ... < Nn para que esta função funcione corretamente. Isto ocorre devido a utilização pesquisa binária (muito rápida):

    mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
    -> 3
    mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
    -> 2
    mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
    -> 0
    

6.3.1.3. Operadores Logicos

Em SQL, todos os operadores logicos avaliam TRUE (VERDADEIRO), FALSE (FALSO) ou NULL (DESCONHECIDO). No MySQL, esta implementação é como 1 (TRUE), 0 (FALSE), e NULL. A maioria deles é comum entre diferentes bancos de dados SQL. no entanto alguns podem retonar qualquer valor diferente de zero para TRUE.

  • NOT, !

    NOT logico. Avalia como 1 se o operador é 0, como 0 se o operador é diferente de zero, e NOT NULL retorna NULL.

    mysql> SELECT NOT 10;
    -> 0
    mysql> SELECT NOT 0;
    -> 1
    mysql> SELECT NOT NULL;
    -> NULL
    mysql> SELECT ! (1+1);
    -> 0
    mysql> SELECT ! 1+1;
    -> 1
    

    O último exemplo produz 1 pois a a expressão é avaliada como (!1)+1.

  • AND, &&

    AND lógico. Avalia como 1 se todos os operandos são diferentes de zero e não é NULL, como 0 se um ou mais operandos são 0, senão retorna NULL.

    mysql> SELECT 1 && 1;
    -> 1
    mysql> SELECT 1 && 0;
    -> 0
    mysql> SELECT 1 && NULL;
    -> NULL
    mysql> SELECT 0 && NULL;
    -> 0
    mysql> SELECT NULL && 0;
    -> 0
    

    Por favor note que as versões do MySQL anteriores a versão 4.0.5 param a avaliação quando um valor NULL é encontrado, e não continua o processo buscando por possíveis 0s. Isto significa que nessa versão, SELECT (NULL AND 0) retorna NULL ao invés de 0. Na versão 4.0.5 o código tem sido re-elaborado para que o resultado sempre seja como prescrito pelo padrão SQL utilizando a otimização sempre que possível.

  • OR, ||

    OR lógico. Avalia como 1 se algum operando é diferente de zero e como NULL se algum operando for NULL, senão 0 é retornado.

    mysql> SELECT 1 || 1;
    -> 1
    mysql> SELECT 1 || 0;
    -> 1
    mysql> SELECT 0 || 0;
    -> 0
    mysql> SELECT 0 || NULL;
    -> NULL
    mysql> SELECT 1 || NULL;
    -> 1
    

  • XOR

    XOR lógico. Retorna NULL se o operando também é NULL. Para operandos não NULL, avalia como 1 se um número ímpar de operandos é diferente de zero, senão 0 é retornado.

    mysql> SELECT 1 XOR 1;
    -> 0
    mysql> SELECT 1 XOR 0;
    -> 1
    mysql> SELECT 1 XOR NULL;
    -> NULL
    mysql> SELECT 1 XOR 1 XOR 1;
    -> 1
    

    a XOR b é matematicamente igual a (a AND (NOT b)) OR ((NOT a) and b).

    XOR foi adicionado na versão 4.0.2.

6.3.1.4. Funções de Fluxo de Controle

  • CASE valor WHEN [valor comparado] THEN resultado [WHEN [valor comparado] THEN resultado ...] [ELSE resultado] END, CASE WHEN [condição] THEN result [WHEN [condição] THEN resultado ...] [ELSE resultado] END

    A primeira expressão retorna o resultado onde valor=valor comparado. A segunda expressão retorna o o resultado da primeira condição, a qual é verdadeira. Se não existe nenhum resultado correspondente, então o resultado depois do ELSE é retornado. Se não existe parte ELSE então é retornado NULL is returned:

    mysql> SELECT CASE 1 WHEN 1 THEN "um"
    WHEN 2 THEN "dois" ELSE "mais" END;
    -> "one"
    mysql> SELECT CASE WHEN 1>0 THEN "verdadeiro" ELSE "falso" END;
    -> "true"
    mysql> SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END;
    -> NULL
    

    O tipo do valor de retorno (INTEGER, DOUBLE ou STRING) é do mesmo tipo do primeiro valor retornado (a expressão depois do primeiro THEN).

  • IF(expr1,expr2,expr3)

    Se expr1 é VERDADEIRA (expr1 <> 0 e expr1 <> NULL) então IF() retorna expr2, senão ela retorna expr3. IF() returna um valor numérico ou string, dependendo do contexto no qual é usado.

    mysql> SELECT IF(1>2,2,3);
    -> 3
    mysql> SELECT IF(1<2,'sim','não');
    -> 'sim'
    mysql> SELECT IF(STRCMP('teste','teste1'),'não','sim');
    -> 'não'
    

    Se expr2 ou expr3 é explicitamente NULL então o tipo resultante da função IF() é o tipo da coluna não NULL. (Este comportamento é novo na versão 4.0.3 do MySQL).

    expr1 é avaliada como um valor inteiro, o qual significa que se você está testando valores de ponto flutuante ou strings, você de fazê-lo usando um operando de comparação:

    mysql> SELECT IF(0.1,1,0);
    -> 0
    mysql> SELECT IF(0.1<>0,1,0);
    -> 1
    

    No primeiro caso acima, IF(0.1) retorna 0 porque 0.1 é convertido para um valor inteiro, resultando um um teste IF(0). Isto pode não ser o que você esperava. No segundo caso, a comparação testa se o valor de ponto flutuante não é zero. O resultado da comparação converte o termo em um interiro.

    O tipo de retorno padrão de IF() (o que pode importar quando ele é armazenado em uma tabela temporária) é calculado na versão 3.23 do MySQL de seguinte forma:

    ExpressãoValor de retorno
    expr2 ou expr3 retorna stringstring
    expr2 ou expr3 retorna um valor de ponto flutuanteponto flutuante
    expr2 ou expr3 retorna um inteirointeiro

    Se expr2 e expr3 são strings, então o resultado é caso-insensitivo se ambas strings são caso insensitivo. (A patir da versão 3.23.51)

  • IFNULL(expr1,expr2)

    Se expr1 não é NULL, IFNULL() retorna expr1, senão retorna expr2. IFNULL() retorna um valor numérico ou string, dependendo do contexto no qual é usado:

    mysql> SELECT IFNULL(1,0);
    -> 1
    mysql> SELECT IFNULL(NULL,10);
    -> 10
    mysql> SELECT IFNULL(1/0,10);
    -> 10
    mysql> SELECT IFNULL(1/0,'yes');
    -> 'yes'
    

    Na versão 4.0.6 e acima o valor resultante padrão de IFNULL(expr1,expr2) é o mais geral das duas expressões, na seguinte ordem: STRING, REAL ou INTEGER. A diferença das versões anteriores é mais notável quando se cria uma tabela baseada em uma expressão ou o MySQL tem que armazenar internamente um valor de IFNULL() em uma tabela temporária.

    CREATE TABLE foo SELECT IFNULL(1,"teste") as teste;
    

    Na versão 4.0.6 do MySQL o tipo da coluna 'teste' é CHAR(4) enquanto nas versões anteriores ela seria do tipo BIGINT.

  • NULLIF(expr1,expr2)

    Se expr1 = expr2 for verdadeiro, é retornado NULL senão é retornado expr1. Isto é o mesmo que CASE WHEN x = y THEN NULL ELSE x END:

    mysql> SELECT NULLIF(1,1);
    -> NULL
    mysql> SELECT NULLIF(1,2);
    -> 1
    

    Note que expr1 é avaliada duas vezes no MySQL se os argumentos não são iguais.

6.3.2. Funções String

Funções string retornam NULL se o tamanho do resultado for maior que o parâmetro do servidor max_allowed_packet. Veja mais informações sobre isto na Seção 5.5.2, “Parâmetros de Sintonia do Servidor”.

Para funções que operam com as posições de uma string, a primeira posição é numerada como 1.

  • ASCII(str)

    Retorna o valor do código ASCII do caracter mais a esquerda da string str. Retorna 0 se str é uma string vazia. Retorna NULL se str é NULL:

    mysql> SELECT ASCII('2');
    -> 50
    mysql> SELECT ASCII(2);
    -> 50
    mysql> SELECT ASCII('dx');
    -> 100
    

    Veja também a função ORD().

  • BIN(N)

    Retorna um representação string do valor binário de N, onde N é um número muito grande (BIGINT). Isto é equivalente a CONV(N,10,2). Retorna NULL se N é NULL:

    mysql> SELECT BIN(12);
    -> '1100'
    

  • BIT_LENGTH(str)

    Retorna o tamanho da string str em bits:

    mysql> SELECT BIT_LENGTH('text');
    -> 32
    

  • CHAR(N,...)

    CHAR() interpretia os argumentos como inteiros e retorna uma string com caracteres dados pelo valor do código ASCII referentes a estes inteiros. Valores NULL são desconsiderados:

    mysql> SELECT CHAR(77,121,83,81,'76');
    -> 'MySQL'
    mysql> SELECT CHAR(77,77.3,'77.3');
    -> 'MMM'
    

  • CONCAT(str1,str2,...)

    Retorna a string resultante da concatenação dos argumentos. Retorna NULL se qualquer dos argumentos for NULL. Pode ter mais de 2 argumentos. Um argumento numérico é convertido para sua forma string equivalente:

    mysql> SELECT CONCAT('My', 'S', 'QL');
    -> 'MySQL'
    mysql> SELECT CONCAT('My', NULL, 'QL');
    -> NULL
    mysql> SELECT CONCAT(14.3);
    -> '14.3'
    

  • CONCAT_WS(separador, str1, str2,...)

    CONCAT_WS() significa CONCAT With Separator (CONCAT com separador) e é uma forma especial do CONCAT(). O primeiro argumento é o separador para os outros argumentos. O separador é adicionado entre as strings a serem concatenadas: O separador pode ser uma string assim como os outros argumentos. Se o separador é NULL, o resultado será NULL. A função irá desconsiderar qualquer NULL depois do argumento do separador.

    mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
    -> 'First name,Second name,Last Name'
    mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
    -> 'First name,Last Name'
    

    Antes do MySQL 4.1.1, CONCAT_WS() desconsiderava strings vazias assim como valores NULL.

  • CONV(N,da_base,para_base)

    Converte números entre diferentes bases. Retorna uma representação string do número N, convertido da base da_base para base para_base. Retorna NULL se qualquer argumento é NULL. O argumento N é interpretado como um inteiro, mas pode ser especificado como um inteiro ou uma string. A base mínima é 2 e a máxima é 36. Se para_base é um número negativo, N é considerado como um número com sinal. Caso contrário, N é tratado como um número sem sinal. CONV funciona com precisão de 64-bit:

    mysql> SELECT CONV("a",16,2);
    -> '1010'
    mysql> SELECT CONV("6E",18,8);
    -> '172'
    mysql> SELECT CONV(-17,10,-18);
    -> '-H'
    mysql> SELECT CONV(10+"10"+'10'+0xa,10,10);
    -> '40'
    

  • ELT(N,str1,str2,str3,...)

    Retorna str1 se N = 1, str2 se N = 2, e assim por diante. Retorna NULL se N é menor que 1 ou maior que o número de argumentos. ELT() é o complemento de FIELD():

    mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
    -> 'ej'
    mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
    -> 'foo'
    

  • EXPORT_SET(bits,on,off,[separador,[numero_de_bits]])

    Retorna uma string onde para todo bit 1 em 'bit', você obtém uma string 'on' e para cada bit 0 você obtem uma string 'off', Cada string é separada com 'separador' (padrão,',') e só 'número_de_bits' (padrão 64) de 'bits' é usado:

    mysql> SELECT EXPORT_SET(5,'S','N',',',4)
    -> S,N,S,N
    

  • FIELD(str,str1,str2,str3,...)

    Retorna o índice de str na lista str1, str2, str3, .... Retorns 0 se str não for encontrada. FIELD() é o complemento de ELT():

    mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 2
    mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 0
    

  • FIND_IN_SET(str,strlista)

    Retorna um valor 1 para N se a string str está na lista strlist contendo N substrings. A lista de string é composta de substrings separadas pelo caracter ‘,’. Se o primeiro argumento é uma string constante e o segundo é uma coluna do tipo SET, a função FIND_IN_SET() é otimizada para usar aritmética binária! Retorna 0 se str não está na strlista ou se strlista é uma string vazia. Retorna NULL se os argumentos são NULL. Esta função não irá funcionar adequadamente se o primeiro argumento contém uma vírgula (‘,’):

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
    -> 2
    

  • HEX(N_ou_S)

    Se N_OU_S é um número, é retornado um representação string do valor hexadecimal de N, onde N é um número muito grande (BIGINT). Isto é equivalente a CONV(N,10,16).

    Se N_OU_S é uma string, é retornado uma string hexadecimal de N_OU_S onde cada caracter de N_OU_S é convertido para 2 dígitos hexadecimais. Isto é o inverso da string 0xff.

    mysql> SELECT HEX(255);
    -> 'FF'
    mysql> SELECT HEX("abc");
    -> 616263
    mysql> SELECT 0x616263;
    -> "abc"
    

  • INSTR(str,substr)

    Retorna a posição da primeira ocorrência da substring substr na string str. É o mesmo que as o LOCATE() com dois argumentos, exceto pelo fato de que os argumentos estão tracados:

    mysql> SELECT INSTR('foobarbar', 'bar');
    -> 4
    mysql> SELECT INSTR('xbar', 'foobar');
    -> 0
    

    Esta função é multi-byte. Na versão 3.23 do MySQL esta função é caso sensitivo, enquanto na versão 4.0 ela só é caso-sensitivo se os argumentos são uma string binária.

  • INSERT(str,pos,tam,novastr)

    Retorna a string str, com a a substring começando na posição pos e contendo tam caracteres substituida pela string novastr:

    mysql> SELECT INSERT('Quadratico', 3, 4, 'Onde');
    -> 'QuOndetico'
    

    Esta função é multi-byte.

  • LCASE(str), LOWER(str)

    Retorna a string str com todos caracteres alterados para letra minúsculas de acordo com o conjunto de caracteres atual (o padrão é ISO-8859-1 Latin1):

    mysql> SELECT LCASE('MYSQL');
    -> 'mysql'
    

    Esta é uma função multi-byte.

  • LEFT(str,tam)

    Retorna os tam caracteres mais a esquerda da string str:

    mysql> SELECT LEFT('foobarbar', 5);
    -> 'fooba'
    

    Esta função é multi-byte.

  • LOAD_FILE(nome_arquivo)

    Lêb o arquivo e retona o conteudo do arquivo como uma string. O arquivo beve estar no servidor, você deve especificar o caminho completo para o arquivo, e você deve ter o privilégio FILE. O arquivo deve ser legível para todos e ser menor que o especificado em max_allowed_packet.

    Se o arquivo não existe ou não pode ser lido devido a alguma das razões acima, a função retornará NULL:

    mysql> UPDATE nome_tabela
    SET coluna_blob=LOAD_FILE("/tmp/picture")
    WHERE id=1;
    

    Se você não está usando a versão 3.23 MySQL, você tem que fazer a leitura do arquivo dentro do seu aplicativo e criar uma instrução INSERT para atualizar o banco de dados com a informação do arquivo. Um modo de se fazer isto, se você estiver usando a biblioteca MySQL++, pode ser encontrada em http://www.mysql.com/documentation/mysql++/mysql++-examples.html.

  • LOCATE(substr,str), LOCATE(substr,str,pos)

    A primeira sintaxe retorna a posição da primeira ocorrência da substring substr na string str. A segunda sintaxe retorna a posição da primeira ocorrência da substring substr na string str, iniciando na posição pos. Retornam 0 se substr não está em str:

    mysql> SELECT LOCATE('bar', 'foobarbar');
    -> 4
    mysql> SELECT LOCATE('xbar', 'foobar');
    -> 0
    mysql> SELECT LPAD('hi',4,'??');
    -> '??hi'
    

  • LTRIM(str)

    Retorna a string str com caracteres de espaços extras iniciais removidos:

    mysql> SELECT LTRIM(' barbar');
    -> 'barbar'
    

  • MAKE_SET(bits,str1,str2,...)

    Retorna um conjunto (uma string contendo substrings separadas por ‘,’) contendo as strings que tem o bit correspondente em bits definido . str1 corresponde ao bit 1, str2 ao bit 2, etc. Strings NULL em str1, str2, ... não são adicionadas ao resultado:

    mysql> SELECT MAKE_SET(1,'a','b','c');
    -> 'a'
    mysql> SELECT MAKE_SET(1 | 4,'Oi','meu','mundo');
    -> 'Oi,mundo'
    mysql> SELECT MAKE_SET(0,'a','b','c');
    -> ''
    

  • OCT(N)

    Retorna uma representação string do valor octal de N, onde N é um número muito grande. Isto é equivalente a CONV(N,10,8). Retorna NULL se N é NULL:

    mysql> SELECT OCT(12);
    -> '14'
    

  • ORD(str)

    Se o caracter mais a esquerda da string str é um caracter multi-byte, é retornado o código para este caracter, calculado a partir dos valores do código ASCII dos seus caracteres contituintes utizando-se a seguinte fórmula: ((primeiro byte do código ASCII)*256+(segundo byte do código ASCII))[*256+terceiro byte do código ASCII...]. Se o caracter mais a esquerda não é multi-byte, é retornado o mesmo valor que a função ASCII() retorna:

    mysql> SELECT ORD('2');
    -> 50
    

  • LENGTH(str), OCTET_LENGTH(str), CHAR_LENGTH(str), CHARACTER_LENGTH(str)

    Retorna o tamanho da string str:

    mysql> SELECT LENGTH('text');
    -> 4
    mysql> SELECT OCTET_LENGTH('text');
    -> 4
    

    LENGTH() e OCTET_LENGTH() são sinônimos e medem o tamanho da length em bytes (octets). Um caracter multi-byte conta é considerado vários bytes. CHAR_LENGTH() e CHARACTER_LENGTH() são sinônimos e medem o tamanho da string em caracteres. Um caracter multi-byte conta como um único caracter. Isto significa que para uma string contendo cinco caracteres de dois bytes, LENGTH() retorna 10, enquanto CHAR_LENGTH() retorna 5.

  • MID(str,pos,len)

    MID(str,pos,len) é um sinônimo para SUBSTRING(str,pos,len).

  • POSITION(substr IN str)

    POSITION(substr IN str) é um sinônimo para LOCATE(substr,str).

  • QUOTE(str)

    Coloca uma string entre aspas para produzir um resultado que possa ser usada em uma intrução SQL como um valor de dados com o caracter de escape correto. A string é retornada entre aspas simples e cada instâqncia de aspas simples (‘'’), barra invertida (‘\’), ASCII NUL, e Control-Z é precedida por uma barra invertida. Se o argumento é NULL, o valor retornado é a palavra ``NULL'' sem aspas simples.

    A função QUOTE() foi adicionada na versão 4.0.3 do MySQL.

    mysql> SELECT QUOTE("Don't");
    -> 'Don\'t!'
    mysql> SELECT QUOTE(NULL);
    -> NULL
    

  • REPEAT(str,cont)

    Retorna uma string consistindo da string str repetida cont vezes. Se cont <= 0, é retornado uma string vazia. É retornado NULL se str ou cont são NULL:

    mysql> SELECT REPEAT('MySQL', 3);
    -> 'MySQLMySQLMySQL'
    

  • REPLACE(str,da_str,para_str)

    Retorna a string str com todas ocorrências da string da_str substituida pela string para_str:

    mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
    -> 'WwWwWw.mysql.com'
    

    Esta função é multi-byte.

  • REVERSE(str)

    Returns the string str with the order of the characters reversed:

    mysql> SELECT REVERSE('abc');
    -> 'cba'
    

    Esta função é multi-byte.

  • RIGHT(str,tem)

    mysql> SELECT RIGHT('foobarbar', 4);
    -> 'rbar'
    

    Esta função é multi-byte.

  • RPAD(str,tam,strpreech)

    Retorna a string str, preenchida a direita com a string strpreench para um tamanho de tam caracteres. Se str é maior que tam, o valor retornado é reduzido para tam caracteres.

    mysql> SELECT RPAD('hi',5,'?');
    -> 'hi???'
    

  • RTRIM(str)

    Retourna a string str com caracteres de espaços extras finais removidos:

    mysql> SELECT RTRIM('barbar ');
    -> 'barbar'
    

    Esta função é multi-byte.

  • SOUNDEX(str)

    Retorna uma string 'soundex' de str. Duas strings que parecidas fonéticamentea devem ter strings 'soundex' iguais. Uma string soundex padrão possui 4 caracteres, mas a função SOUNDEX() retorna uma string de tamanho arbitrário. Você posde usar SUBSTRING() no resultado para obter uma string 'soundex' padrão. Todos os caracteres não alfanuméricos são ignorados na string dada. Todas caracteres internacionais fora da faixa A-Z são tratados como vogais:

    mysql> SELECT SOUNDEX('Hello');
    -> 'H400'
    mysql> SELECT SOUNDEX('Quadratically');
    -> 'Q36324'
    

  • SPACE(N)

    Retorna uma string contendo N caracteres de espaço:

    mysql> SELECT SPACE(6);
    -> ' '
    

  • SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,tam), SUBSTRING(str FROM pos FOR tam)

    A forma sem um argumento tam retorna uma substring da string str iniciando na posição pos. A forma com um argumento tam retorna a substring com tam caracteres da string str, iniciando da posição pos. A forma variante que utiliza FROM é a sintaxe SQL-92:

    mysql> SELECT SUBSTRING('Quadratically',5);
    -> 'ratically'
    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
    -> 'barbar'
    mysql> SELECT SUBSTRING('Quadratically',5,6);
    -> 'ratica'
    

    Esta função é multi-byte.

  • SUBSTRING_INDEX(str,delim,cont)

    Retorna a substring da string str antes de cont ocorrencias do delimitador delim. Se cont é positivo, tudo a esquerda do delimitador final (contando a partir da esquerda) é retornado. Se cont é negativo, tudo a direita do delimitador final (contando a partir da direita) é retornado.

    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
    -> 'www.mysql'
    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
    -> 'mysql.com'
    

    Esta função é multi-byte.

  • TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

    Retorna a string str com todos prefixos e/ou sufixos remstr removidos. Se nenhum dos especificadores BOTH, LEADING ou TRAILING são dados, é considerado BOTH. Se remstr não é especificada, espaços são removidos:

    mysql> SELECT TRIM(' bar ');
    -> 'bar'
    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
    -> 'barxxx'
    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
    -> 'bar'
    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
    -> 'barx'
    

    Esta função é multi-byte.

  • UCASE(str), UPPER(str)

    Retorna a string str com todos caracteres alterados para letra maiúsculas de acordo com o conjunto de caracteres atual (o padrão é ISO-8859-1 Latin1):

    mysql> SELECT UCASE('Hej');
    -> 'HEJ'
    

    Esta é uma função multi-byte.

6.3.2.1. Funções de Comparação de Strings

MySQL automaticamente converte números para quando necessário, e vice-versa:

mysql> SELECT 1+"1";
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'

Se você quiser converter um número em uma string de forma explicita, passe-o como um argumento de CONCAT().

Se uma função de string tem uma string binária como argumento, a string resultante é também um string binária. Um número convertido para uma string é tratado como um string binária. Isto afeta apenas a comparação.

Normalmente, se qualquer expressão em uma string é caso-sensitivo, a comparação é realizada no modo caso sensitivo.

  • expr LIKE pad [ESCAPE 'car-escape']

    Correspondência de padrões usando uma simples expressão de comparações SQL. Retorna 1 (VERDADEIRO) ou 0 (FALSO). Com LIKE você pode usar os seguintes meta-caracteres no padrao:

    CarDescrição
    %Corresponde a qualquer número de caracteres, até zero caracteres
    _Corresponde a exatamente um caracter

    mysql> SELECT 'David!' LIKE 'David_';
    -> 1
    mysql> SELECT 'David!' LIKE '%D%v%';
    -> 1
    

    Para testar instâncias literais de um meta caracter, preceda o caracter com o carcter de escape. Se você não especificar o caracter de ESCAPE, assume-se ‘\’:

    StringDescription
    \%Correponde a um caracter %
    \_Correponde a um caracter _

    mysql> SELECT 'David!' LIKE 'David\_';
    -> 0
    mysql> SELECT 'David_' LIKE 'David\_';
    -> 1
    

    Para especificar um caracter de escape diferebte, use a cláusula ESCAPE:

    mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
    -> 1
    

    As seguintes instruções mostram que a comparação de strings são caso-insensitivo, a menos que um dos operandos seja uma string binária:

    mysql> SELECT 'abc' LIKE 'ABC';
    -> 1
    mysql> SELECT 'abc' LIKE BINARY 'ABC';
    -> 0
    

    LIKE é permitido em uma expressão numérica! (Esta é uma extensão MySQL para o LIKE do SQL-99.)

    mysql> SELECT 10 LIKE '1%';
    -> 1
    

    Nota: Como MySQL usa sintaxe de escape do C em strings (por exemplo, '\n'), você deve dobrar qualquer ‘\’ que você usar em sua string LIKE. Por exemplo, para pesquisar por '\n', especifique-o como '\\n'. Para buscar por ‘\’, especifique-o como '\\\\' (as barras invertidas são eliminadas uma vez pelo analizador e outra vez quando a correspondência de padrões é feita, deixando uma únicas barra invertida para ser verificada).

    Note: O LIKE atual não é um caracter multi-byte. Comparaçãoes são feitas caracter por caracter.

  • expr NOT LIKE pad [ESCAPE 'car-escape']

    O mesmo que NOT (expr LIKE pad [ESCAPE 'car-escape']).

  • expr SOUNDS LIKE expr

    O mesmo que SOUNDEX(expr)=SOUNDEX(expr) (disponível apenas na versão 4.1 ou posterior).

  • expr REGEXP pad, expr RLIKE pad

    Realiza a busca de padrões em uma expressã string com base no padrão pad. O padrão pode ser uma expressão regular extendida. Veja mais informações sobre isto na Apêndice G, Sintaxe de Expressões Regulares do MySQL. Retorna 1 se expr conincide com pad, senão retorna 0. RLIKE é um sinônimo para REGEXP, fornecido para compatibilidade com mSQL. Nota: Como MySQL usa a sintaxe de escape do C em strings (por exemplo, '\n'), você deve dobrar qualquer ‘\’ que você use em sua string REGEXP. Como na versão 3.23.4 do MySQL, REGEXP é caso- insensitivo para strings normais (não binárias).

    mysql> SELECT 'Monty!' REGEXP 'm%y%%';
    -> 0
    mysql> SELECT 'Monty!' REGEXP '.*';
    -> 1
    mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
    -> 1
    mysql> SELECT "a" REGEXP "A", "a" REGEXP BINARY "A";
    -> 1 0
    mysql> SELECT "a" REGEXP "^[a-d]";
    -> 1
    

    REGEXP e RLIKE usam o conjunto de caracteres atual (ISO-8859-1 Latin1 por padrão) para decidir o tipo de caracter.

  • expr NOT REGEXP pad, expr NOT RLIKE pad

    O mesmo que NOT (expr REGEXP pad).

  • STRCMP(expr1,expr2)

    STRCMP() retorna 0 se as string são a mesma, -1 se o primeiro argumento é menor que o segundo de acordo com a ordenação atual e 1 em caso contrário:

    mysql> SELECT STRCMP('texto', 'texto2');
    -> -1
    mysql> SELECT STRCMP('texto2', 'texto');
    -> 1
    mysql> SELECT STRCMP('texto', 'texto');
    -> 0
    

  • MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION] )

    MATCH ... AGAINST() é usado para busca de textos completos e retorna a relvância - similaridade medidad entre o texto nas colunas (col1,col2,...) e a consulta expr. Relevância é um número de ponto flutuante. Relevância zero significa que não houve nenhuma similaridade. MATCH ... AGAINST() está disponível na versão 3.23.23 ou posterior do MySQL. A extensão IN BOOLEAN MODE foi adicionada na versão 4.0.1, WITH QUERY EXPANSION foi adicionado na versão 4.1.1. Para detalhes e exemplos de uso, veja Seção 6.8, “Pesquisa Full-text no MySQL”.

6.3.2.2. Caso Sensitivo

  • BINARY

    O operador BINARY transforma uma string em uma string binária. Este é um modo fácil de forçar a comparação para se caso-sensitivo mesmo se a coluna não seja definida como BINARY ou BLOB:

    mysql> SELECT "a" = "A";
    -> 1
    mysql> SELECT BINARY "a" = "A";
    -> 0
    

    BINARY string é um atalho para CAST(string AS BINARY). Veja mais informações sobre isto na Seção 6.3.5, “Funções de Conversão”. BINARY foi introduzida na versão 3.23.0 do MySQL.

    Note que em alguns contextos MySQL não estará apto a usar o índice de forma eficiente quando se transformar uma coluna índice em BINARY.

Se você quiser compara um blob caso-insensitivo você pode sempre convertê-lo para letras maiúsculas antes de faer a comparação:

SELECT 'A' LIKE UPPER(col_blobl) FROM nome_tabela;

Não planejamos introduzir em breve coerção (casting) entre diferentes conjuntos de caracteres para tornar comparções de strings mais flexível.

6.3.3. Funções Numéricas

6.3.3.1. Operações Aritiméticas

Os operadores aritiméticos usuais estão disponíveis. ‘-’, ‘+’, e ‘*’, o resultado é calculado com precisão de BIGINT (64-bit) se ambos os argumentos são inteiros! Se um dos argumentos for um inteiro sem sinal, e o outro argumento é um inteiro também, o resultado será um inteiro sem sinal. Veja mais informações sobre isto na Seção 6.3.5, “Funções de Conversão”.

  • +

    Adição:

    mysql> SELECT 3+5;
    -> 8
    

  • -

    Subtração:

    mysql> SELECT 3-5;
    -> -2
    

  • *

    Multiplicação:

    mysql> SELECT 3*5;
    -> 15
    mysql> SELECT 18014398509481984*18014398509481984.0;
    -> 324518553658426726783156020576256.0
    mysql> SELECT 18014398509481984*18014398509481984;
    -> 0
    

    O resultado da última expressão é incorreta porque o resultado da multiplicação de inteiros excede a faixa de 64-bits dos cálculos BIGINT.

  • /

    Divisão:

    mysql> SELECT 3/5;
    -> 0.60
    

    Divisões por zero produz um resultado NULL:

    mysql> SELECT 102/(1-1);
    -> NULL
    

    Uma divisão será calculada com aritimética BIGINT somente se executada em um contexto no qual o resultado é convertido para um interiro!

6.3.3.2. Funções Matematicas

Todas as funções matematicas retornam NULL no caso de um erro.

  • -

    Menos unario. Muda o sinal do argumento:

    mysql> SELECT - 2;
    -> -2
    

    Note que se este operador é utilizando com um BIGINT, o valor retornado é um BIGINT! Isto significa que você deve evitar usar - em inteiros que pode ter o valor de -2^63!

  • ABS(X)

    Retorna o valor absoluto de X:

    mysql> SELECT ABS(2);
    -> 2
    mysql> SELECT ABS(-32);
    -> 32
    

    O uso desta função é seguro com valores BIGINT.

  • SIGN(X)

    Retorna o sinal do argumento como -1, 0, ou 1, dependendo de quando X é negativo, zero, ou positivo:

    mysql> SELECT SIGN(-32);
    -> -1
    mysql> SELECT SIGN(0);
    -> 0
    mysql> SELECT SIGN(234);
    -> 1
    

  • MOD(N,M), %

    Modulo (como o operador % em C). Retorna o resto de N dividido por M:

    mysql> SELECT MOD(234, 10);
    -> 4
    mysql> SELECT 253 % 7;
    -> 1
    mysql> SELECT MOD(29,9);
    -> 2
    mysql> SELECT 29 MOD 9;
    -> 2
    

    O uso desta função é seguro com valores BIGINT. O último exemplo só funciona no MySQL 4.1

  • FLOOR(X)

    Retorna o maior valor inteiro não maior que X:

    mysql> SELECT FLOOR(1.23);
    -> 1
    mysql> SELECT FLOOR(-1.23);
    -> -2
    

    Note que o valor retornado é convertido para um BIGINT!

  • CEILING(X), CEIL(X)

    Retorna o menor valor inteiro não menor que X:

    mysql> SELECT CEILING(1.23);
    -> 2
    mysql> SELECT CEIL(-1.23);
    -> -1
    

    O alias CEIL() foi adicionado versão 4.0.6.

    Note que o valor retornado é convertido para um BIGINT!

  • ROUND(X), ROUND(X,D)

    Retorna o argumeto X, arredondado para o inteiro mais próximo. Com dois argumentos o arredandamento é feito para um número com D decimais.

    mysql> SELECT ROUND(-1.23);
    -> -1
    mysql> SELECT ROUND(-1.58);
    -> -2
    mysql> SELECT ROUND(1.58);
    -> 2
    mysql> SELECT ROUND(1.298, 1);
    -> 1.3
    mysql> SELECT ROUND(1.298, 0);
    -> 1
    mysql> SELECT ROUND(23.298, -1);
    -> 20
    

    Note que o comportamento de ROUND() quando o argumento está no meio do caminho entre dois inteiros depende da implementação da biblioteca C. Alguns arredondamentos para o número mais próximo, são sempre para baixo, para cima ou são zero. Se você precisa de um tipo de arredondamento, você deve usar uma função bem definida como TRUNCATE() ou FLOOR().

  • DIV

    Divisão de inteiros. Similar ao FLOOR() mas seguro com valores BIGINT.

    mysql> SELECT 5 DIV 2
    -> 2
    

    DIV é novo no MySQL 4.1.0.

  • EXP(X)

    Retorna o valor de e (the base of natural logarithms) raised to the power of X:

    mysql> SELECT EXP(2);
    -> 7.389056
    mysql> SELECT EXP(-2);
    -> 0.135335
    

  • LN(X)

    Retorna o logaritmo natural de X:

    mysql> SELECT LN(2);
    -> 0.693147
    mysql> SELECT LN(-2);
    -> NULL
    

    Esta função foi adicionada na versão 4.0.3 do MySQL. É sinônimo de LOG(X) no MySQL.

  • LOG(X), LOG(B,X)

    Se chamado com um parâmetro, esta função retorna o logarítmo natural de X:

    mysql> SELECT LOG(2);
    -> 0.693147
    mysql> SELECT LOG(-2);
    -> NULL
    

    Se chamado com dois parâmetros, esta função retorna o logarítmo natural de X para uma base arbitraria B:

    mysql> SELECT LOG(2,65536);
    -> 16.000000
    mysql> SELECT LOG(1,100);
    -> NULL
    

    A opção de base arbitrária foi adicionada na versão 4.0.3 do MySQL. LOG(B,X) é equivalente a LOG(X)/LOG(B).

  • LOG2(X)

    Returna o logarítmo na base 2 de X:

    mysql> SELECT LOG2(65536);
    -> 16.000000
    mysql> SELECT LOG2(-100);
    -> NULL
    

    LOG2() é útil para descobrir quantos bits um número necessitaria para ser armazenado. Esta função foi adicionada na versão 4.0.3 do MySQL. Em versões anteriores, você pode usar LOG(X)/LOG(2).

  • LOG10(X)

    Returna o logarítmo na base 10 de X:

    mysql> SELECT LOG10(2);
    -> 0.301030
    mysql> SELECT LOG10(100);
    -> 2.000000
    mysql> SELECT LOG10(-100);
    -> NULL
    

  • POW(X,Y), POWER(X,Y)

    Retorna o valor de X elevado a potência de Y:

    mysql> SELECT POW(2,2);
    -> 4.000000
    mysql> SELECT POW(2,-2);
    -> 0.250000
    

  • SQRT(X)

    Retorna o a raiz quadrada não negativa de X:

    mysql> SELECT SQRT(4);
    -> 2.000000
    mysql> SELECT SQRT(20);
    -> 4.472136
    

  • PI()

    Retorna o valor de PI. A quantidade de números decimais padrão é 5, mas o MySQL usa internamente a precisão dupla completa para PI.

    mysql> SELECT PI();
    -> 3.141593
    mysql> SELECT PI()+0.000000000000000000;
    -> 3.141592653589793116
    

  • COS(X)

    Retorna o cosseno de X, onde X é dado em radianos:

    mysql> SELECT COS(PI());
    -> -1.000000
    

  • SIN(X)

    Retorna o seno de X, onde X é dado em radianos:

    mysql> SELECT SIN(PI());
    -> 0.000000
    

  • TAN(X)

    Retorna a tangente de X, onde X é dado em radianos:

    mysql> SELECT TAN(PI()+1);
    -> 1.557408
    

  • ACOS(X)

    Retorna o arco cosseno X, isto é, o valor cujo cosseno é X. Retorna NULL se X não está na faixa de -1 a 1:

    mysql> SELECT ACOS(1);
    -> 0.000000
    mysql> SELECT ACOS(1.0001);
    -> NULL
    mysql> SELECT ACOS(0);
    -> 1.570796
    

  • ASIN(X)

    Retorna o arco seno X, isto é, o valor cujo seno é X. Retorna NULL se X não está na faixa de -1 a 1:

    mysql> SELECT ASIN(0.2);
    -> 0.201358
    mysql> SELECT ASIN('foo');
    -> 0.000000
    

  • ATAN(X)

    Retorna o arco tangente X, isto é, o valor cuja tangente é X. X:

    mysql> SELECT ATAN(2);
    -> 1.107149
    mysql> SELECT ATAN(-2);
    -> -1.107149
    

  • ATAN(Y,X), ATAN2(Y,X)

    Retorna o arco tangente de duas variaveis X e Y. É similar ao caclculo do arco tengente de Y / X, exceto que os sinais de ambos argumentos são usados para determinas o quadrante do resultado:

    mysql> SELECT ATAN(-2,2);
    -> -0.785398
    mysql> SELECT ATAN2(PI(),0);
    -> 1.570796
    

  • COT(X)

    Returns a cotangente de X:

    mysql> SELECT COT(12);
    -> -1.57267341
    mysql> SELECT COT(0);
    -> NULL
    

  • CRC32(expr)

    Calcula um valor de verificação de redundância cíclica e retorna um valor unsigned de 32 bits. O resultado é NULL se o argumento é NULL. O argumento esperado é uma string e será tratado como tal se não for.

    mysql> SELECT CRC32('MySQL');
    -> 3259397556
    

    CRC32() está disponível a partir do MySQL 4.1.0.

  • RAND(), RAND(N)

    Retorna um valor de ponto flutuante aleatório na faixa de 0 a 1.0. Se um argumento inteiro N é especificado, ele é usado como uma semente (produzindo uma sequência repetitiva):

    mysql> SELECT RAND();
    -> 0.9233482386203
    mysql> SELECT RAND(20);
    -> 0.15888261251047
    mysql> SELECT RAND(20);
    -> 0.15888261251047
    mysql> SELECT RAND();
    -> 0.63553050033332
    mysql> SELECT RAND();
    -> 0.70100469486881
    

    Você não pode usar uma coluna com valores RAND() em uma cláusula ORDER BY, pois ORDER BY avaliaria a coluna múltiplas vezes. Na versão 3.23 você pode fazer: SELECT * FROM nome_tabela ORDER BY RAND()

    Isto é útil para obter um amostra aleatória de um conjunto SELECT * FROM tabela1,tabela2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000.

    Note que um RAND() em uma cláusula WHERE será reavliado toda vez que WHERE é executado.

    RAND() não é um gerador de números aletatórios perfeito, mas é um modo rápido de se gerar números aleatórios ad hoc que serão portáveis entre plataformas para a mesma versão do MySQL.

  • LEAST(X,Y,...)

    Com dois ou mais argumentos, retorna o menor (valor-mínimo) argumento. Os argumentos são comparados usando as seguintes regras:

    • Se o valor de retorno é usado em um contexto INTEGER, ou todos argumentos são valores inteiro, eles são comparados como inteiros.

    • Se o valor de retorno é usado em um contexto REAL, ou todos argumentos são valores reais, eles são comparados como inteiros.

    • Se qualquer um dos argumento for uma string caso-sensitivo, os argumentos são comparados como strings caso-sensitivo.

    • Nos outros casos, os argumentos são comparados como strings caso-insensitivo:

    mysql> SELECT LEAST(2,0);
    -> 0
    mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
    -> 3.0
    mysql> SELECT LEAST("B","A","C");
    -> "A"
    

    Em versões do MySQL anteriores a versão 3.22.5, você pode usar MIN() no lugar de LEAST.

  • GREATEST(X,Y,...)

    Retorna o maior (valor máximo) argumento. Os argumentos são comparados usando as mesmas regras do LEAST:

    mysql> SELECT GREATEST(2,0);
    -> 2
    mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
    -> 767.0
    mysql> SELECT GREATEST("B","A","C");
    -> "C"
    

    Em versões do MySQL anteriores a versão 3.22.5, você pode usar MAX() no lugar de GRATEST.

  • DEGREES(X)

    Retorna o argumento X, convertido de radianos para graus:

    mysql> SELECT DEGREES(PI());
    -> 180.000000
    

  • RADIANS(X)

    Retorna o argumento X, convertido de graus para radianos:

    mysql> SELECT RADIANS(90);
    -> 1.570796
    

  • TRUNCATE(X,D)

    Retiorna o número X, truncado para D casas decimais. Se D é 0, o resultado não terá ponto deciaml ou prate fracionária:

    mysql> SELECT TRUNCATE(1.223,1);
    -> 1.2
    mysql> SELECT TRUNCATE(1.999,1);
    -> 1.9
    mysql> SELECT TRUNCATE(1.999,0);
    -> 1
    mysql> SELECT TRUNCATE(-1.999,1);
    -> -1.9
    

    A partir do MySQL 3.23.51 todos o números são arredondados para zero.

    Se D é negativo, então D numeros da parte inteira são zerados:

    mysql> SELECT TRUNCATE(122,-2);
    -> 100
    

    Note que como os números decimais não são normalmente armazenados como números exatos, mas como valores de dupla precisão, você pode obter o seguinte resultado:

    mysql> SELECT TRUNCATE(10.28*100,0);
    -> 1027
    

    O resultado acima acontece porque 10.28 é, na verdade, armazenado como 10.2799999999999999.

6.3.4. Funções de Data e Hora

Esta seção descreve as funções que podem ser usadas para manipular valores temporais.

Veja Seção 6.2.2, “Tipos de Data e Hora” para uma descrição da faixa de valores que cada tipo tem e os formatos válidos nos quais valores de data e hora podes ser especificados.

Aqui está um exemplo que usa funções de data. A consulta seguinte seleciona todos os registros com um valores em uma coluna col_data dentro dos últimos 30 dias:

mysql> SELECT algo FROM nome_tabela
WHERE TO_DAYS(NOW()) - TO_DAYS(col_data) <= 30;

(Note que a consulta também selecionará registros com datas futuras.)

Funções que esperam valores de data normaemente aceitaram valores datetime e ignoram a parte da hora. Funções que esperam valores de hora normalmente aceitarão valores datetime e ignoram a parte da data.

Funções que retornam a data ou hora atual são avaliadas apenas uma vez por consulta, no inicio da sua execução. Isto significa que várias referências a uma função com NOW() dentro de uma mesma consulta sempre produzirá o memo resultado. Este princípio também se aplica a CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(), e qualquer um dos seus sinônimos.

A faixa do valor retornado na seguinte descrição da função se aplica a datas completas. Se uma data é um valor ``zero'' ou uma data incompleta tal como '2001-11-00', funções que extraem parte de uma data podem retornam 0. Por exemplo, DAYOFMONTH('2001-11-00') retorna 0.

  • DATE(expr)

    Extrai a parte da data da expressão date ou datetime em expr.

    mysql> SELECT DATE('2003-12-31 01:02:03');
    -> '2003-12-31'
    

    DATE() está disponível a partir do MySQL 4.1.1.

  • TIME(expr)

    Extrai a parte da hora da expressão time ou datetime em expr.

    mysql> SELECT TIME('2003-12-31 01:02:03');
    -> '01:02:03'
    mysql> SELECT TIME('2003-12-31 01:02:03.000123');
    -> '01:02:03.000123'
    

    TIME() está disponível a partir do MySQL 4.1.1.

  • TIMESTAMP(expr), TIMESTAMP(expr,expr2)

    Com um argumento, retorna a expressão date ou datetime em expr como um valor datetime. Com dois argumentos, adiciona a expressão time e expr2 à expressão date ou datetime em expr e retorna um valor datetime.

    mysql> SELECT TIMESTAMP('2003-12-31');
    -> '2003-12-31 00:00:00'
    mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
    -> '2004-01-01 00:00:00'
    

    TIMESTAMP() está disponível a partir do MySQL 4.1.1.

  • DAYOFWEEK(data)

    Retorna o índice do dia da semana para data (1 = Domingo, 2 = Segunda, ... 7 = Sábado). Estes valores de índices correspondem ao padrão ODBC.

    mysql> SELECT DAYOFWEEK('1998-02-03');
    -> 3
    

  • WEEKDAY(data)

    Retorna o índice do dia das semana para data (0 = Segunda, 1 = Terça, ... 6 = Domingo):

    mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
    -> 1
    mysql> SELECT WEEKDAY('1997-11-05');
    -> 2
    

  • DAYOFMONTH(data)

    Retorna o dia do mês para data, na faixa de 1 até 31:

    mysql> SELECT DAYOFMONTH('1998-02-03');
    -> 3
    

  • DAY(date)

    DAY() é um sinônimo para DAYOFMONTH(). Está disponível a partir do MySQL 4.1.1.

  • DAYOFYEAR(data)

    Retorna o dia do ano para data, na faixa de 1 até 366:

    mysql> SELECT DAYOFYEAR('1998-02-03');
    -> 34
    

  • MONTH(data)

    Retorna o mês para data, na faixa de 1 até 12:

    mysql> SELECT MONTH('1998-02-03');
    -> 2
    

  • DAYNAME(data)

    Retorna o nome do dia da semana para data:

    mysql> SELECT DAYNAME('1998-02-05');
    -> 'Thurday'
    

  • MONTHNAME(data)

    Retorna o nome do mês para data:

    mysql> SELECT MONTHNAME('1998-02-05');
    -> 'February'
    

  • QUARTER(data)

    Retorna o trimaster para data, na faixa de 1 até 4:

    mysql> SELECT QUARTER('98-04-01');
    -> 2
    

  • WEEK(data [,modo])

    A função retorna o número da semana para date. A forma de dois argumentos de WEEK() permite que você especifique se a semana começa no Domingo ou na Segunda e se o valor de retorno deve estar na faixa de 0-53 ou 1-5. Quando o argumento modo é omitido, o valor de uma variável de servidor default_week_format (ou 0 no MySQL 4.0 e mais novo) é assumido. Veja mais informações sobre isto na Seção 5.5.6, “Sintaxe de SET.

    A seguinte tabela demonstra como o argumento modo funciona:

    ValorSignificado
    0Semana começa no Domingo; retorna o valor na faixa de 0 a 53; semana 1 é a primeira semana neste ano.
    1Semana começa na Segunda; retorna o valor na faixa de 0 a 53; semana 1 é a primeira semana com mais de 3 dias neste ano
    2Semana começa no Domingo; retorna o valor na faixa de 1 a 53; semana 1 é a primeira semana neste ano.
    3Semana começa na Segunda; retorna o valor na faixa de 1 a 53; semana 1 é a primeira semana com mais de 3 dias neste ano.
    4Semana começa no Domingo; retorna o valor na faixa de 0 a 53; semana 1 é a primeira semana com mais de 3 dias neste ano.
    5Semana começa na Segunda; retorna o valor na faixa de 0 a 53; semana 1 é a primeira semana neste ano.
    6Semana começa no Domingo; retorna o valor na faixa de 0 a 53; semana 1 é a primeira semana que tenha mais de 3 dias neste ano.
    7Semana começa na Segunda; retorna o valor na faixa de 1 a 53; semana 1 é a primeira semana neste ano.

    O valor mode de 3 pode ser usado a partir do MySQL 4.0.5. O valor mode de 4 e acima pode ser usado a partir do MySQL 4.0.17.

    mysql> SELECT WEEK('1998-02-20');
    -> 7
    mysql> SELECT WEEK('1998-02-20',0);
    -> 7
    mysql> SELECT WEEK('1998-02-20',1);
    -> 8
    mysql> SELECT WEEK('1998-12-31',1);
    -> 53
    

    Nota: Na versão 4.0, WEEK(#,0) foi alterado para corresponder ao calendário americano. Antes WEEK() era calculada de forma errada para data no EUA. (Na verdade WEEK(#) e WEEK(#,0) era errado para todos os casos).

    Note que se a data for a última semana do ano anterior, o MySQL retornará 0 se você não usar 2, 3, 6 ou 7 como argumento opcional modo:

    mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
    -> 2000, 0
    

    Pode-se questionar que o MySQL deveria retornar 52 para a função WEEK() ja que a data dada ocorre, na verdade, ma 52a. semana de 1999. Nós decidimos retornar 0 já que queremos que função retorne ``o número da semana do ano dado''. Isto faz com que o uso da função WEEK() seja seguro quando combinado com outras funções que extraiam um parte de uma data.

    Se você prefere que o resultado seja avaliado em relacão ao ano que aontém o primeiro dia da semana de uma data dada, então você deve usar o 2, 3, 6 ou 7 como argumento opcional modo:

    mysql> SELECT WEEK('2000-01-01',2);
    -> 52
    

    Alternativamente você pode usar a função YEARWEEK():

    mysql> SELECT YEARWEEK('2000-01-01');
    -> 199952
    mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
    -> '52'
    

  • WEEKOFYEAR(data)

    Retorna a semana da data como um número na faixa de 1 a 53.

    mysql> SELECT WEEKOFYEAR('1998-02-20');
    -> 8
    

    WEEKOFYEAR() esta disponível a partir do MySQL 4.1.1.

  • YEAR(data)

    Retorna o ano para data na faixa de 1000 a 9999:

    mysql> SELECT YEAR('98-02-03');
    -> 1998
    

  • YEARWEEK(data), YEARWEEK(data,inicio)

    Retorna o ano e a semana para a data. O argumento inicio funciona exatamente como o argumento inicio de WEEK(). Note que o ano pode ser diferente do ano no argumento data para a primeira e a última semana do ano:

    mysql> SELECT YEARWEEK('1987-01-01');
    -> 198653
    

    Note que o número da semana é diferente do que seria retornado pela função WEEK() (0) para os argumentos opcionais 0 ou 1, já que WEEK() retorna a semana no centexto de um ano dado.

  • HOUR(hora)

    Retorna a hora para hora. A faixa do valor retornado será de 0 a 23 para o valor hora do dia.

    mysql> SELECT HOUR('10:05:03');
    -> 10
    

    No entanto, a faixa dos valores TIME atualmente são muito grandes, assim HOUR pode retornar valores maior que 23:

    mysql> SELECT HOUR('272:59:59');
    -> 272
    

  • MINUTE(hora)

    Retorna o minuto para hora, na faixa de 0 a 59:

    mysql> SELECT MINUTE('98-02-03 10:05:03');
    -> 5
    

  • SECOND(hora)

    Retorna o segundo para hora, na faixa de 0 a 59:

    mysql> SELECT SECOND('10:05:03');
    -> 3
    

  • MICROSECOND(expr)

    Retorna os microsegundos da expressão time ou datetime em expr como um número na faixa de 0 a 999999.

    mysql> SELECT MICROSECOND('12:00:00.123456');
    -> 123456
    mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');
    -> 10
    

    MICROSECOND() está disponível a partir do MySQL 4.1.1.

  • PERIOD_ADD(P,N)

    Adiciona N meses ao período P (no formato AAMM ou AAAAMM). Retorna um valor no formato AAAAMM.

    Note que o argumento de período P não é um valor de data:

    mysql> SELECT PERIOD_ADD(9801,2);
    -> 199803
    

  • PERIOD_DIFF(P1,P2)

    Retorna o número de meses entre os períodos P1 e P2. P1 e P2 devem estar no formato AAMM ou AAAAMM.

    Note que os argumentos de período P1 e P2 não são valores de data:

    mysql> SELECT PERIOD_DIFF(9802,199703);
    -> 11
    

  • DATE_ADD(data,INTERVAL tipo expr), DATE_SUB(data,INTERVAL tipo expr)

    Estas funções realizam operações aritméticas em datas.

    A partir do MySQL 3.23, INTERVAL expr tipo é permitido nos dois lados do operador + se a expressao em ambos os lados é um valor date ou datetime. Para o operador -, INTERVAL expr tipoe é permitido apenas no lado direito, porque não faz sentido subtrair um valor date ou datetime de um intervalo. (Veja exemplo abaixo.)

    data é um valor DATETIME ou DATE especificando a data de início. expr is an expressão especificando o intervala a ser adicionado ou subtraido da data de início. expr é uma string; ela pode iniciar com um ‘-’ para intervalos negativos. type é uma palavra chave indicando como a expressão deve ser interpretada.

    A seguinte tabela mostra como os argumentos tipo e expr se relacionam:

    tipo do valorFormarto esperado da expr
    SECONDSECONDS
    MINUTEMINUTES
    HOURHOURS
    DAYDAYS
    MONTHMONTHS
    YEARYEARS
    MINUTE_SECOND'MINUTES:SECONDS'
    HOUR_MINUTE'HOURS:MINUTES'
    DAY_HOUR'DAYS HOURS'
    YEAR_MONTH'YEARS-MONTHS'
    HOUR_SECOND'HOURS:MINUTES:SECONDS'
    DAY_MINUTE'DAYS HOURS:MINUTES'
    DAY_SECOND'DAYS HOURS:MINUTES:SECONDS'
    DAY_MICROSECOND'DAYS.MICROSECONDS'
    HOUR_MICROSECOND'HOURS.MICROSECONDS'
    MINUTE_MICROSECOND'MINUTES.MICROSECONDS'
    SECOND_MICROSECOND'SECONDS.MICROSECONDS'
    MICROSECOND'MICROSECONDS'

    Os valores do tipo DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND e MICROSECOND são permitidos após o MySQL 4.1.1.

    O MySQL permite qualquer delimitador de pontuação no formato de expr. Os delimitadores mostrados na tabela são apenas sugeridos. Se o argumento date é um valor de DATA e seus cálculos envolvem apenas as partes ANO, MÊS, e DIA (into é, nenhuma parte de hora), o resultado é um valor do tipo DATE. Senão, o resultado é um valor do tipo DATETIME:

    mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
    -> '1998-01-01 00:00:00'
    mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
    -> '1998-01-01'
    mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
    -> '1997-12-31 23:59:59'
    mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    -> INTERVAL 1 SECOND);
    -> '1998-01-01 00:00:00'
    mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    -> INTERVAL 1 DAY);
    -> '1998-01-01 23:59:59'
    mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    -> INTERVAL '1:1' MINUTE_SECOND);
    -> '1998-01-01 00:01:00'
    mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
    -> INTERVAL '1 1:1:1' DAY_SECOND);
    -> '1997-12-30 22:58:59'
    mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
    -> INTERVAL '-1 10' DAY_HOUR);
    -> '1997-12-30 14:00:00'
    mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
    -> '1997-12-02'
    mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    -> INTERVAL '1.999999' SECOND_MICROSECOND);
    -> '1993-01-01 00:00:01.000001'
    

    Se você especificado um intervalo muito curto (não inclue todas as partes que seriam esperadas pelo intervalo para aquele tipo), MySQL assume que você não especificou a parte mais a esquerda do valor do intervalo. Por exemplo, se você especifica um tipo DAY_SECOND, o valor esperado de expr deverá ter as partes de dias, horas, minutos e segundos. Se você especifica um valor como '1:10', MySQL assume que as partes do dia e da hora foram esquecidas e o valor representa minutos e segundos. Em outras palavras, '1:10' DAY_SECOND é interpretado de forma equivalente a '1:10' MINUTE_SECOND. Isto é análogo a forma que o MySQL interpreta valores TIME representado tempo decorrido no lugar de hora do dia.

    Note que se você adicionar ou subtrair de uma data algo contendo uma parte de hora, o resultado é automaticamente convertido para um valor datetime:

    mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
    -> '1999-01-02'
    mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
    -> '1999-01-01 01:00:00'
    

    Se você utilizar datas mal formadas, o valor retornado NULL. Sê você adicionar MONTH, YEAR_MONTH, ou YEAR e a data resultante tiver um dia maior que o dia máximo para aquele mês, o dia é ajustado para o dia máximo no mês.

    mysql> SELECT DATE_ADD('1998-01-30', interval 1 month);
    -> '1998-02-28'
    

    Note pelo exemplo anterior que a palavra-chave INTERVAL e o especificador tipo não são caso sensitivo.

  • ADDDATE(data,INTERVAL expr type), ADDDATE(expr,dias)

    Quando chamada com a forma INTERVAL do segundo argumento, ADDDATE() é um sinônimo para DATE_ADD(). A função relcionada SUBDATE() é um sinônimo para DATE_SUB().

    mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
    -> '1998-02-02'
    mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
    -> '1998-02-02'
    

    A partir do MySQL 4.1.1, a segunda sintaxe é permitida, onde expr é uma expresão date ou datetime e dias é o número de dias a ser adicionado a expr.

    mysql> SELECT ADDDATE('1998-01-02', 31);
    -> '1998-02-02'
    

  • ADDTIME(expr,expr2)

    ADDTIME() adiciona expr2 a expr e retorna o resultado. expr é uma expressão date ou datetime, e expr2 é uma expressão time.

    mysql> SELECT ADDTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
    -> '1998-01-02 01:01:01.000001'
    mysql> SELECT ADDTIME("01:00:00.999999", "02:00:00.999998");
    -> '03:00:01.999997'
    

    ADDTIME() foi adicionado no MySQL 4.1.1.

  • EXTRACT(tipo FROM data)

    A função EXTRACT() usa o mesmo tipo de intervalo especificado como DATE_ADD() ou DATE_SUB(), mas extrai partes da da data em vez de realizar aritimética de data.

    mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
    -> 1999
    mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
    -> 199907
    mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
    -> 20102
    mysql> SELECT EXTRACT(MICROSECOND FROM "2003-01-02 10:30:00.00123");
    -> 123
    

  • DATEDIFF(expr,expr2), TIMEDIFF(expr,expr2)

    DATEDIFF() retorna o número de dias entre a data inicial expr e a data final expr2. expr e expr2 são expressões de datas ou data e hora. Apenas a parte da data dos valores sã usados no cálculo.

    TIMEDIFF() retorna o tempo entre a hora inicial expr e a hora final expr2. expr e expr2 são expressões de hora ou data e hora, mas ambas devem ser do mesmo tipo.

    mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
    -> 1
    mysql> SELECT DATEDIFF('1997-11-31 23:59:59','1997-12-31');
    -> -30
    mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
    -> '-00:00:00.000001'
    mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');
    -> '46:58:57.999999'
    

    DATEDIFF() e TIMEDIFF() foram adicionados no MySQL 4.1.1.

  • TO_DAYS(data)

    Dada uma data data, retorna o número do dia (o número de dias desde o ano 0);

    mysql> SELECT TO_DAYS(950501);
    -> 728779
    mysql> SELECT TO_DAYS('1997-10-07');
    -> 729669
    

    TO_DAYS() não pode ser usado com valores que orecedem o advento do calendario Gregoriano (1582), porque ele não leva em conta os dias perdidos quando o calendário foi mudado.

  • FROM_DAYS(N)

    Dado um número de dia N, retorna um valor DATE:

    mysql> SELECT FROM_DAYS(729669);
    -> '1997-10-07'
    

    FROM_DAYS() não pode ser usado com valores que orecedem o advento do calendario Gregoriano (1582), porque ele não leva em conta os dias perdidos quando o calendário foi mudado.

  • DATE_FORMAT(data,formato)

    Formata o valor de data de acordo com a string formato string. Os seguintes identificadores podem ser utilizados na string formato:

    SpecifierDescription
    %MNome do mês (January..December)
    %WNome da semana (Sunday..Saturday)
    %DDia do mês com sufixo Inglês (0th, 1st, 2nd, 3rd, etc.)
    %YAno, numerico, 4 digitos
    %yAno, numerico, 2 digitos
    %XAno para a semana onde o Domingo é o primeiro dia da semana, numerico, 4 digitos; usado com %V
    %xAno para a semana onde a segunda é o primeiro dia da semana, numerico, 4 digitos; usado com %v
    %aNome da semana abreviado (Sun..Sat)
    %dDia do mês, numerico (00..31)
    %eDia do mês, numerico (0..31)
    %mMês, numerico (00..12)
    %cMês, numerico (0..12)
    %bNome do mês abreviado (Jan..Dec)
    %jDia do ano (001..366)
    %HHora (00..23)
    %kHora (0..23)
    %hHora (01..12)
    %IHora (01..12)
    %lHora (1..12)
    %iMinutos, numerico (00..59)
    %rTempo, 12-horas (hh:mm:ss seguido por AM ou PM)
    %TTempo, 24-horas (hh:mm:ss)
    %SSegundos (00..59)
    %sSegundos (00..59)
    %fMicrosegundos (000000..999999)
    %pAM ou PM
    %wDia da semana (0=Domingo..6=Sabado)
    %USemana(00..53), onde o Domingo é o primeiro dia da semana.
    %uSemana(00..53), onde a Segunda é o primeiro dia da semana.
    %VSemana(01..53), onde o Domingo é o primeiro dia da semana; usado com %X
    %vSemana(01..53), onde a Segunda é o primeiro dia da semana; usado com %x
    %%Um literal ‘%’.

    Todos os outros caracteres são apenas copiados para o resultado, sem interpretação.

    O especificador dr formato %f está disponível a partir do MySQL 4.1.1.

    Como na versão 3.23 do MySQL, o caracter ‘%’ é exigido antes dos caracteres de especificação de formato. Em versões anteriores do MySQL ‘%’ era opcional.

    A razão para a faixa de valores do mês e do dia começarem com zero é que o MySQL permite datas incompletas tais como '2004-00-00' serem armazenadas no MySQL 3.23.

    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
    -> 'Saturday October 1997'
    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
    -> '22:23:00'
    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    '%D %y %a %d %m %b %j');
    -> '4th 97 Sat 04 10 Oct 277'
    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    '%H %k %I %r %T %S %w');
    -> '22 22 10 10:23:00 PM 22:23:00 00 6'
    mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
    -> '1998 52'
    

  • STR_TO_DATE(str,format)

    Esta é a função reversa da função DATE_FORMAT(). Ela pega uma string str, e um formato format, e retorna uma valor DATETIME.

    Os valores date, time, ou datetime contidos em str devem ser dados no formato indicado por format. Para o especificadores que podem ser usados em format, veja a tabela na descrição da função DATE_FORMAT(). Todos os outros caracteres serão apenas exibidos, não sendo interpretados. Se str contém um valor date, time, ou datetime ilegal, STR_TO_DATE() retorna NULL.

    mysql> SELECT STR_TO_DATE('03.10.2003 09.20', '%d.%m.%Y %H.%i')
    -> 2003-10-03 09:20:00
    mysql> SELECT STR_TO_DATE('10rap', '%crap')
    -> 0000-10-00 00:00:00
    mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00', '%Y-%m-%d %H:%i:%s')
    -> NULL
    

    STR_TO_DATE() está disponível a partir do MySQL 4.1.1.

  • GET_FORMAT(DATE | TIME | TIMESTAMP, 'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL')

    Retorna uma string de formato. Esta função é útil combinado com as funções DATE_FORMAT() e STR_TO_DATE(), e quando configurarmos as variáveis do servidor DATE_FORMAT, TIME_FORMAT e DATETIME_FORMAT. Os três valores possíveis para o primeiro argumento e os cinco valores possíveis para o segundo argumento resultam em 15 strings de formato possíveis (para o especificador usado, veja a tabela na descrição da função DATE_FORMAT()):

    Chamada da FunçãoResultado
    GET_FORMAT(DATE,'USA')'%m.%d.%Y'
    GET_FORMAT(DATE,'JIS')'%Y-%m-%d'
    GET_FORMAT(DATE,'ISO')'%Y-%m-%d'
    GET_FORMAT(DATE,'EUR')'%d.%m.%Y'
    GET_FORMAT(DATE,'INTERNAL')'%Y%m%d'
    GET_FORMAT(TIMESTAMP,'USA')'%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(TIMESTAMP,'JIS')'%Y-%m-%d %H:%i:%s'
    GET_FORMAT(TIMESTAMP,'ISO')'%Y-%m-%d %H:%i:%s'
    GET_FORMAT(TIMESTAMP,'EUR')'%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(TIMESTAMP,'INTERNAL')'%Y%m%d%H%i%s'
    GET_FORMAT(TIME,'USA')'%h:%i:%s %p'
    GET_FORMAT(TIME,'JIS')'%H:%i:%s'
    GET_FORMAT(TIME,'ISO')'%H:%i:%s'
    GET_FORMAT(TIME,'EUR')'%H.%i.%S'
    GET_FORMAT(TIME,'INTERNAL')'%H%i%s'

    Formato ISO é do ISO ISO 9075, não do ISO 8601.

    mysql> SELECT DATE_FORMAT('2003-10-03', GET_FORMAT(DATE, 'EUR')
    -> '03.10.2003'
    mysql> SELECT STR_TO_DATE('10.31.2003', GET_FORMAT(DATE, 'USA'))
    -> 2003-10-31
    mysql> SET DATE_FORMAT=GET_FORMAT(DATE, 'USA'); SELECT '2003-10-31';
    -> 10-31-2003
    

    GET_FORMAT() está disponível a partir do MySQL 4.1.1. Veja Veja mais informações sobre isto na Seção 5.5.6, “Sintaxe de SET.

  • SUBDATE(date,INTERVAL expr type), SUBDATE(expr,days)

    Quando chamado com a forma INTERVAL do segunto argumento, SUBDATE() é um sinonimo para DATE_SUB().

    mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
    -> '1997-12-02'
    mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
    -> '1997-12-02'
    

    A partir do MySQL 4.1.1, a segunda sintaxe é permitida, onde expr é uma expressão date ou datetime e days é o número de dias a ser subtraído de expr.

    mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);
    -> '1997-12-02 12:00:00'
    

  • SUBTIME(expr,expr2)

    SUBTIME() subtrai expr2 de expr e retorna o resultado. expr é uma expressão date ou datetime, e expr2 é uma expressão time.

    mysql> SELECT SUBTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
    -> '1997-12-30 22:58:58.999997'
    mysql> SELECT SUBTIME("01:00:00.999999", "02:00:00.999998");
    -> '-00:59:59.999999'
    

    SUBTIME() foi adicionado no MySQL 4.1.1.

  • TIME_FORMAT(hora,formato)

    É usado como a função DATE_FORMAT() acima, mas a string de formato pode conter apenas os especificadores de formato que tratam de horas, minutos e segundos. Outros especificadores produzem um valor NULL ou 0.

    Se o valor time contém uma hora que é maior que 23, os especificadores de formato de hora %H e %k produzem um valor maior que a faixa como de 0..23. O outro especificador do formato de hora produz o valor da hora módulo 12:

    mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
    -> '100 100 04 04 4'
    

  • LAST_DAY(data)

    Pega um valor date ou datetime e retorna o valor correspondente para o último dia do mês. Retorna NULL se o argumento é invalido.

    mysql> SELECT LAST_DAY('2003-02-05'), LAST_DAY('2004-02-05');
    -> '2003-02-28', '2004-02-29'
    mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
    -> '2004-01-31'
    mysql> SELECT LAST_DAY('2003-03-32');
    -> NULL
    

    LAST_DAY() está disponível a partir do MySQL 4.1.1.

  • MAKEDATE(ano,diadoano)

    Retorna uma data, dado os valores da ano e dia do ano. diadoano deve ser maior que 0 ou o resultado será NULL.

    mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
    -> '2001-01-31', '2001-02-01'
    mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
    -> '2001-12-31', '2004-12-30'
    mysql> SELECT MAKEDATE(2001,0);
    -> NULL
    

    MAKEDATE() está disponível a partir do MySQL 4.1.1.

  • MAKETIME(hora,minuto,segundo)

    Retorna um valor time calculado a partir dos argmentos hora, minuto e segundo.

    mysql> SELECT MAKETIME(12,15,30);
    -> '12:15:30'
    

    MAKETIME() está disponível a partir do MySQL 4.1.1.

  • CURDATE(), CURRENT_DATE, CURRENT_DATE()

    Retorna a data atual como um valor no formato 'YYYY-MM-DD' ou YYYYMMDD, dependendo se a função é usada num contexto numérico ou de string.

    mysql> SELECT CURDATE();
    -> '1997-12-15'
    mysql> SELECT CURDATE() + 0;
    -> 19971215
    

  • CURTIME(), CURRENT_TIME, CURRENT_TIME()

    Retorna a hora atual como um valor no formato 'HH:MM:SS' ou HHMMSS, dependo se a função é usada em um contexto numérico ou como string:

    mysql> SELECT CURTIME();
    -> '23:50:26'
    mysql> SELECT CURTIME() + 0;
    -> 235026
    

  • NOW(), SYSDATE(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()

    Retorna a data e hora atual como um valor no formato 'YYYY-MM-DD HH:MM:SS' ou YYYYMMDDHHMMSS, dependendo se a função é utilizada num contexto numérico ou de string.

    mysql> SELECT NOW();
    -> '1997-12-15 23:50:26'
    mysql> SELECT NOW() + 0;
    -> 19971215235026
    

  • UNIX_TIMESTAMP(), UNIX_TIMESTAMP(data)

    Se chamado sem argumento, retorna um tipo timestamp do Unix (segundos desde '1970-01-01 00:00:00' GMT) como um inteiro sem sinal. Se UNIX_TIMESTAMP() é chamada com um argumento data, é retornado o valor do argumento como segundo desde '1970-01-01 00:00:00' GMT. data pode ser um string DATE, uma string DATETIME, um TIMESTAMP, ou um número no formato YYMMDD ou YYYYMMDD na hora local:

    mysql> SELECT UNIX_TIMESTAMP();
    -> 882226357
    mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
    -> 875996580
    

    Qaundo UNIX_TIMESTAMP é usado em uma coluna TIMESTAMP, a função retorna o valor timestamp interno diretamente, sem nenhuma conversão ``string-para-unix-timestamp'' implicita. Se você passar uma data fora da faixa para UNIX_TIMESTAMP(), a função irá retornar 0, mas por favor note que só verificações básicas são realizadas. (ano 1970-2037, mês 01-12, dia 01-31).

    Se você subtrair colunas UNIX_TIMESTAMP(), você pode querer mudar o resultado para inteiro com sinal. Veja mais informações sobre isto na Seção 6.3.5, “Funções de Conversão”.

  • FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

    Retorna a representação do argumento unix_timestamp como um valor no formato 'YYYY-MM-DD HH:MM:SS' ou YYYYMMDDHHMMSS, dependendo de do contexto em que a funçõ é utilizada:

    mysql> SELECT FROM_UNIXTIME(875996580);
    -> '1997-10-04 22:23:00'
    mysql> SELECT FROM_UNIXTIME(875996580) + 0;
    -> 19971004222300
    

    Se o formato é dado o resultado é formatado de acordo com a string formato. formato pode conter os especificadores listados acima para a função DATE_FORMAT()

    mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    -> '%Y %D %M %h:%i:%s %x');
    -> '2003 6th August 06:22:58 2003'
    

  • SEC_TO_TIME(seconds)

    Retorna o argumento segundos, convertido em horas, minutos e segundos como um valor no formato 'HH:MM:SS' ou HHMMSS, dependendo do contexto em que a função é utilizada:

    mysql> SELECT SEC_TO_TIME(2378);
    -> '00:39:38'
    mysql> SELECT SEC_TO_TIME(2378) + 0;
    -> 3938
    

  • TIME_TO_SEC(time)

    Retorna o argumento time, convertido em segundos:

    mysql> SELECT TIME_TO_SEC('22:23:00');
    -> 80580
    mysql> SELECT TIME_TO_SEC('00:39:38');
    -> 2378
    

  • UTC_DATE, UTC_DATE()

    Retorna a data UTC atual como um valor no formato 'YYYY-MM-DD' ou YYYYMMDD, dependendo se a função é usada emum contexto string ou numérico:

    mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
    -> '2003-08-14', 20030814
    

    UTC_DATE() está disponível a partir do MySQL 4.1.1.

  • UTC_TIME, UTC_TIME()

    Retorna a hora UTC atual como um valor no formato 'HH:MM:SS' ou HHMMSS, dependendo se a função é usada em um contexto string ou numérico:

    mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
    -> '18:07:53', 180753
    

    UTC_TIME() está disponível a partir do MySQL 4.1.1.

  • UTC_TIMESTAMP, UTC_TIMESTAMP()

    Retorna a data e hora UTC atual como um valor no formato 'YYYY-MM-DD HH:MM:SS' ou YYYYMMDDHHMMSS, dependendo se a função é usada em um contexto string ou numérico:

    mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
    -> '2003-08-14 18:08:04', 20030814180804
    

    UTC_TIMESTAMP() está disponível a partir do MySQL 4.1.1.

6.3.5. Funções de Conversão

As funções CAST() e CONVERT() devem ser usada para tomar um valor de um tipo e produzir um valor de outro tipo. As suas sintaxes são as seguintes:

CAST(expressão AS tipo)
CONVERT(expressão,tipo)
CONVERT(expr USING transcoding_name)

O valor tipo pode ser um dos seguintes:

  • BINARY

  • CHAR

  • DATE

  • DATETIME

  • SIGNED {INTEGER}

  • TIME

  • UNSIGNED {INTEGER}

CAST() e CONVERT() estão disponíveis a partir do MySQL 4.0.2. O tipo de conversão CHAR está disponível a partir do versão 4.0.6. A forma USING de CONVERT() está disponível a partir da versão 4.1.0.

CAST() e CONVERT(... USING ...) são da sintaxe SQL-99. A forma não-USING de CONVERT() é da sintaxe ODBC.

CAST() é da sintaxe SQL-99 syntax e CONVERT() é da sintaxe ODBC.

As funções de conversão são principalmente úteis quando você deseja criar uma coluna com um tipo específico em uma CREATE ... SELECT:

CREATE TABLE nova_tabela SELECT CAST('2000-01-01' AS DATE);

As funções também podem ser úteis para ordenar colunas ENUM na ordem lexicográfica. Normalmente a ordenação das colunas ENUM ocorrem usando os valores numéricos internos. Converter os valores para CHAR resultam em uma ordenação lexicográfica:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(string AS BINARY) é a mesma coisa que BINARY string. CAST(expr AS CHAR) trata a expressão como uma string com o conjunto de caracteres padrão.

NOTA: No MysQL 4.0 o CAST() para DATE, DATETIME ou TIME só marca a coluna para ser um tipo específico mas não altera o valor da coluna.

No MySQL 4.1.0 o valor será convertido para a coluna correta quando for enviado para o utilizador (este é um recurso de como o novo protocolo na versão 4.1 envia as informações de data para o cliente):

mysql> SELECT CAST(NOW() AS DATE);
-> 2003-05-26

Em versões futuras do MySQL (provavelmente 4.1.2 ou 5.0) iremos corrigir o fato de que CAST também altera o resultado se você usá-lo como parte de uma expressão mais complexa, como CONCAT("Data: ",CAST(NOW() AS DATE)).

Você não deve utilizar CAST() para extrair dados em formatos diferentes, mas sim para usar funções strins como LEFT ou EXTRACT(). Veja mais informações sobre isto na Seção 6.3.4, “Funções de Data e Hora”.

Para converter uma string para um valor numérico, normalmente não é necessário se fazer nada; apenas use a string como se fosse um número:

mysql> SELECT 1+'1';
-> 2

Se você usar um número em um contexto string, o número será convertido automaticamente para uma string BINARY.

mysql> SELECT CONCAT("hello you ",2);
-> "hello you 2"

O MySQL suporta aritimético com valores de 64 bits com sinal e sem sinal. Se você está usando operações numéricas (como +) e um dos operandos é unsigned integer (inteiro sem sinal), o resultado também será sem sinal (unsigned). Você pode forçar o tipo usando os operadores de conversão SIGNED e UNSIGNED para converter a operação para um inteiro de 64 bits com sinal e sem sinal, respectivamente.

mysql> SELECT CAST(1-2 AS UNSIGNED)
-> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
-> -1

Note que se um dos operandos for um valor de ponto flutuante o resultado é um valor de ponto flutuante e não é afetado pela regra acima. (Neste contexto DECIMAL() é considerado um valor de ponto flutuante).

mysql> SELECT CAST(1 AS UNSIGNED) -2.0;
-> -1.0

Se você estiver utilizando uma string em uma operação aritimética, ela é convertida para um número de ponto flutuante.

O tratamento de valores sem sinais foi mudado no MySQL 4.0 para suportar valores BIGINT apropriadamente. Se você tiver algum código que deseja executar no MySQL 4.0 e 3.23 (casos em que você provavelmente não poderá usar a função CAST()), você pode utilizar o seguinte truque para conseguir um resultado com sinal quando subtraindo duas colunas do tipo unsigned integer (inteiro sem sinal):

SELECT (coluna_sem_sinal_1+0.0)-(coluna_sem_sinal_2+0.0);

A idéia é que as colunas sejam convertidas para valores de ponto flutuante antes da subtração ocorrer.

Se você tiver algum problema com colunas UNSIGNED no seu aplicação MySQL antiga ao portar para o MySQL 4.0, você pode usar a opção --sql-mode=NO_UNSIGNED_SUBTRACTION ao iniciar mysqld. Note, no entanto, que enquanto você utilizar esta opção, não será possível conseguir um uso efetivo do tipo de coluna BIGINT UNSIGNED.

CONVERT() com USING é usado para converter dados entre diferentes conjuntos de caracteres. No MySQL, nomes trancodificados são o mesmo que o nome do conjunto de caracteres correspondentes. Por exemplo, esta instrução converte a string 'abc' no conjunto de caracteres padrão do servidor na string correspondente no conjunto de caracteres utf8:

SELECT CONVERT('abc' USING utf8);

6.3.6. Outras Funções

6.3.6.1. Funções Binárias

O MySQL utiliza aritimética BIGINT (64bits) para operações binárias, assim estes operadores possuem uma faixa máxima de 64 bits.

  • |

    Operador binário OR

    mysql> SELECT 29 | 15;
    -> 31
    

    O resultado é um inteiro sem sinal de 64 bits.

  • &

    Operado binário AND

    mysql> SELECT 29 & 15;
    -> 13
    

    O resultado é um inteiro sem sinal de 64 bits.

  • ^

    Operado binário XOR

    mysql> SELECT 1 ^ 1;
    -> 0
    mysql> SELECT 1 ^ 0;
    -> 1
    mysql> SELECT 11 ^ 3;
    -> 8
    

    O resultado é um inteiro sem sinal de 64 bits.

    XOR foi adicionado na versão 4.0.2.

  • <<

    Desloca um número BIGINT (muito grande) a esquerda:

    mysql> SELECT 1 << 2;
    -> 4
    

    O resultado é um inteiro sem sinal de 64 bits.

  • >>

    Desloca um número BIGINT (muito grande) a direita:

    mysql> SELECT 4 >> 2;
    -> 1
    

    O resultado é um inteiro sem sinal de 64 bits.

  • ~

    Inverte todos os bits:

    mysql> SELECT 5 & ~1;
    -> 4
    

    O resultado é um inteiro sem sinal de 64 bits.

  • BIT_COUNT(N)

    Retorna o número de bits que são passados no argumento N:

    mysql> SELECT BIT_COUNT(29);
    -> 4
    

6.3.6.2. Funções Diversas

  • DATABASE()

    Retorna o nome do banco de dados atual:

    mysql> SELECT DATABASE();
    -> 'test'
    

    Se nenhum banco de dados estiver selecionado, DATABASE() retorna NULL a partir do MySQL 4.1.1, e uma string vazia em versões anteriores.

  • USER(), SYSTEM_USER(), SESSION_USER()

    Retorna o nome do utilizador MySQL e nome de máquina atual:

    mysql> SELECT USER();
    -> 'davida@localhost'
    

    O valor indica o nome do utilizador que você especificou ao conectar ao servidor e a máquina cliente da qual você se conectou. (Antes do MySQL versão 3.22.11, o valor da função não inclui o nome da máquina cliente.)

    Você pode extrair apenas a parte do nome do utilizador, desconsiderando se o valor inclui a parte do nome de máquina, desta forma:

    mysql> SELECT SUBSTRING_INDEX(USER(),"@",1);
    -> 'davida'
    

  • CURRENT_USER()

    Retorna o nome do utilizador e o nome de máquina com os quais a sessão atual foi autenticada. Este valor corresponde a conta que é usada para acessar seu privilégio de acessos. Ela pode ser diferente do valor de USER().

    mysql> SELECT USER();
    -> 'davida@localhost'
    mysql> SELECT * FROM mysql.user;
    -> ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
    mysql> SELECT CURRENT_USER();
    -> '@localhost'
    

    O exemplo ilustra que embora o cliente tenha especificado um nome de utilizador davida (como indicado pelo valor da função USER()), o servidor autenticou o cliente usando uma conta de utilizador anônimo (como visto pela parte vazia no nome de utilizador do valor CURRENT_USER()). Um modos de isto ocorrer é que não haja uma conta listada na tabela de permissões para davida.

  • PASSWORD(str), OLD_PASSWORD(str)

    Calcula a senha a partir de senha str em texto puro. Está é a função que é utilizada para criptografar a senha do MySQL para armazenamento na coluna Password da tabela de permissões user

    mysql> SELECT PASSWORD('badpwd');
    -> '7f84554057dd964b'
    

    A criptografia de PASSWORD() não e reversível.

    PASSWORD() não realiza a criptografia da senha da mesa maneira que as senhas Unix são criptografadas. Veja ENCRYPT().

    Note: A função PASSWORD() é usada pelo sistema de autentificação no servidor MySQL, você NÃO deve uitlizá-las em suas próprias aplicações. Para este propósito utilize MD5() ou SHA1(). Veja também RFC-2195 para maiores informações sobre o tratamento de senha e autenticação segura em suas aplicações.

  • ENCRYPT(str[,salt])

    Criptografa str utilizando a chamada de sistema crypt() do Unix. O argumento salt deve ser uma string com dois caracteres. (Na versão 3.22.16 do MySQL, salt deve ser maior que dois caracteres.)

    mysql> SELECT ENCRYPT("hello");
    -> 'VxuFAJXVARROc'
    

    ENCRYPT() ignora tudo depois dos primeiros 8 caracteres de str, pelo menos em alguns sistemas. Este comportamento é determinado pela implementação da chamada de sistema crypt().

    Se crypt() não estiver disponível no seu sistema, ENCRYPT() sempre retorna NULL. Devido a isto recomendamos que você use MD5() ou SHA1() em vez dos existentes em sua plataforma.

  • ENCODE(str,senha_str)

    Criptografa str usando senha_str como a senha. Para descriptografar o resultado, utilize DECODE().

    O resultado é uma string binária do mesmo tamanho de str. Se você deseja salvá-la em uma coluna, use uma coluna do tipo BLOB.

  • DECODE(cript_str,senha_str)

    Descriptografa o string criptografada cript_str usando senha_str como a senha. cript_str deve ser uma string retornada de ENCODE().

  • MD5(string)

    Calcula um checksum MD5 de 128 bits para a string. O valor é retornado como um número hexadecimal de 32 digitos que pode, por exemplo, ser usado como uma chave hash:

    mysql> SELECT MD5("testing");
    -> 'ae2b1fca515949e5d54fb22b8ed95575'
    

    Este é o "RSA Data Security, Inc. MD5 Message-Digest Algorithm".

  • SHA1(string), SHA(string)

    Calcula um checksum SHA1 de 160 bit para a string, como descrito no RFC 3174 (Algoritmo Hash de Segurança). O valor é retornado como um número hexadecial de 40 digitos, or NULL no caso do argumento ser NULL . Uma das possibilidades para o uso desta função é a chave hash. Você também pode usá-lo como uma função segura de criptografia para armazenar senhas.

    mysql> SELECT SHA1("abc");
    -> 'a9993e364706816aba3e25717850c26c9cd0d89d'
    

    SHA1() foi adicionado na versão 4.0.2, e pode ser considerada um equivalente ao MD5() com criptografia mais segura. SHA() é um sinônimo para SHA1().

  • AES_ENCRYPT(string,string_chave), AES_DECRYPT(string,string_chave)

    Estas funções permitem criptografia/descriptografia de dados usando o algoritmo oficial AES (Padrão Avançado de Criptografia), antes conhecido como Rijndael. Criptgrafia com uma chave de 128 bits podem ser usadas, mas você pode extendê-la para 256 bits através da fonte. Nós escolhemos 128 bits porque é muito mais rápido e é bastante seguro.

    Os argumentos de entrada podem ser de qualquer tamanho. Se ambos argumentos são NULL, o resultado desta função tam bém será NULL.

    Como o AES é um algorítimo de nível de bloco, padding é usado para codificar strings de tamanho ímpares e então a string resultante pode ser calculada como 16*(trunc(tamanho_string/16)+1).

    Se AES_DECRYPT() detectar dados inválidos ou padding incorreto, ela retorna NULL. No entanto, é possível para o AES_DECRYPT() retornar um valor não-NULL (possivelmente lixo) se os dados de entrada ou a chave eram inválidos

    Você pode usar as funções AES para armazenar dados de forma criptografada modificando as suas consultas:

    INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));
    

    Você pode obter mais segurança não transferindo a chave em suas conexões a cada consulta, o que pode ser conseguido armazenando-o em varáveis do lado do servidor na hora das conexão.

    SELECT @password:='my password';
    INSERT INTO t VALUES (1,AES_ENCRYPT('text',@password));
    

    AES_ENCRYPT() e AES_DECRYPT() foram adicionados na versão 4.0.2, e podem ser considerados a função de criptografia mais segura atualmente disponível no MySQL.

  • DES_ENCRYPT(string_para_ciptografar [, (numero_chave | chave_string) ] )

    Criptografa a string com a chave dada utilizando o algortimo Triplo-DES.

    Note que esta função só funciona se o MySQL tiver sido configurado com suporte a SSL. Veja mais informações sobre isto na Seção 4.4.10, “Usando Conexões Seguras”.

    A chave de criptografia utilizada é escolhida da seguinte forma:

    ArgumentoDescrição
    Somente um argumentoA primeira chave de des-key-file é utilizada.
    Número da chaveA chave dada (0-9) de des-key-file é utilizada.
    stringA chave_string dada será utilizada para criptografar string_para_criptografar.

    O string retornada será uma string binária onde o primeiro caracter será CHAR(128 | número_chave).

    O 128 é adicionado para facilitar o reconhecimento da chave de criptografia. Se você usar uma chave string, numéro_chave será 127.

    Havendo erro, esta função retorna NULL.

    O tamanho da string para o resultado será novo_tamanho= tamanho_orig + (8-(tamanho_orig % 8))+1.

    O des-key-file terá o seguinte formato:

    numero_chave chave_string_des
    numero_chave chave_string_des
    

    Cada numero_chave deve ser um núero na faixa de 0 a 9. As linhas do arquivo podem estar em qualquer ordem. chave_string_des é a string que será usada para criptografar a mensagem. Entre o número e a chave deve haver pelo menos um espaço. A primeira chave é a chave padrão que será utilizada se não for especificada nenhuma chave como argumento para DES_ENCRYPT()

    Você pode dizer ao MySQL para ler novos valores de arquivos de chave com o comando FLUSH DES_KEY_FILE. Isto exige o privilégio Reload_priv.

    Um benefício de ter um conjunto de chaves padrões é que ele dá a aplicação um modo de verificar a existência de valores criptografados em colunas, sem dar ao utilizador final o direito de descriptografar estes valores.

    mysql> SELECT endereco_clientes FROM tabela_clientes WHERE
    cartao_credito_criptografado = DES_ENCRYPT("numero_cartao_credito");
    

  • DES_DECRYPT(string_para_descriptografar [, chave_string])

    Derscritogra uma string criptografada com DES_ENCRYPT().

    Note que esta função só funciona se o MySQL tiver sido configurado com suporte SSL. Veja mais informações sobre isto na Seção 4.4.10, “Usando Conexões Seguras”.

    Se nenhum argumento chave_string for dado, DES_DECRYPT() examina o primeiro byte da string criptografada para determinar o número de chave DES que foi usado para criptografar a string original, e então lê a chave de des-key-file para descriptografar a mensagem. Para isto funcionar o utilizador deve ter o privilégio SUPER.

    Se você passar para esta função um argumento chave_string, aquela string é usada como a chave para descriptografar a mensagem.

    Se a string_para_descriptografar não se paracer com uma string criptografada, o MySQL retornará a string_para_descriptografar dada.

    Havendo erro, esta função retorna NULL.

  • COMPRESS(string_para_compactar)

    Compacta uma string

    mysql> SELECT LENGTH(COMPRESS(REPEAT("a",1000)));
    -> 21
    1 row in set (0.00 sec)
    mysql> SELECT LENGTH(COMPRESS(""));
    -> 0
    1 row in set (0.00 sec)
    mysql> SELECT LENGTH(COMPRESS("a"));
    -> 13
    1 row in set (0.00 sec)
    mysql> SELECT LENGTH(COMPRESS(REPEAT("a",16)));
    -> 15
    1 row in set (0.00 sec)
    

    COMPRESS() foi adicionado no MySQL 4.1.1. Se exigido, o MySQL tem que ser compilado com uma biblioteca de compactação como zlib. Senão , o valor de retorno é sempre NULL.

    O conteúdo da string compactada é armazenada da seguinte forma:

    • Strings vazias são armazenadas como strings vazias

    • Strings que não estão vazias são armazenadas como um string descompacatada de 4 byte de tamanho (low-byte-first) seguida pela string compactada com gzip. Se a string finaliza com espaço, adicionamos um ‘.’ extra para evitar problemas com o corte do espaço final o resultado deve ser armazenado em um campo CHAR ou VARCHAR. O uso de CHAR ou VARCHAR para armazenar strings compactadas não é recomendado. É melhor usar uma coluna BLOB.

  • UNCOMPRESS(string_para_descompactar)

    Descompacta uma string compactado pela função COMPRESS()

    mysql> select UNCOMPRESS(COMPRESS("any string"));
    -> 'any string'
    1 row in set (0.00 sec)
    

    UNCOMPRESS() foi adicionado no MySQL 4.1.1 Se exigido, o MySQL tem que ser compilado com uma biblioteca de compactação como zlib. Senão , o valor de retorno é sempre NULL.

  • UNCOMPRESSED_LENGTH(string_compactada)

    Retorna o tamanho da string compactada antes da compactação

    mysql> select UNCOMPRESSED_LENGTH(COMPRESS(REPEAT("a",30)));
    -> 30
    1 row in set (0.00 sec)
    

    UNCOMPRESSED_LENGTH() foi adicionado no MySQL 4.1.1

  • LAST_INSERT_ID([expr])

    Retorna o último valor gerado automaticamente que tenha sido inserido em um coluna AUTO_INCREMENT.

    mysql> SELECT LAST_INSERT_ID();
    -> 195
    

    O último ID que foi gerado e mantido no servidor em uma base por conexão. Isto significa que o valor que a função retona para um dado cliente é o valor AUTO_INCREMENT gerado mais recentemente por aquele cliente. O valor não pode ser afetado pelos outros clientes, mesmo se eles gerarem um valor AUTO_INCREMENT deles mesmos. Este comportamento assegura que você pode recuperar seu próprio ID sem se preocupar com a atividade de outros clientes e sem precisar de locks ou transações.

    O valor de LAST_INSERT_ID() não é alterado se você atualizar uma coluna AUTO_INCREMENT de uma linha com um valor não-mágico (Isto é, um valor que não seja NULL e nem 0).

    Se você inserir muitos registros ao mesmo tempo com uma instrução insert, LAST_INSERT_ID() retorna o valor da primeira linha inserida. A razão para isto é tornar possível reproduzir facilmente a mesma intrução INSERT em algum outro servidor.

    Se expr é dado com um argumento para LAST_INSERT_ID(), então o valor do argumento é retornado pela função e é configurado como o próximo valor para ser retornado pela LAST_INSERT_ID(). Isto pode ser útil para simular sequências:

    Primeiro crie a tabela:

    mysql> CREATE TABLE sequencia (id INT NOT NULL);
    mysql> INSERT INTO sequencia VALUES (0);
    

    Então a tabela pode ser usada para gerar sequência de números como estes:

    mysql> UPDATE sequencia SET id=LAST_INSERT_ID(id+1);
    

    Você pode gerar sequências sem chamar LAST_INSERT_ID(), mas a utilidade de se usar a função deste modo é que o valor ID é mantido no servidor como o último valor gerado automaticamente (seguro para multi-usurário). Você pode recuperar a nova ID como você leria qualquer valor AUTO_INCREMENT normal no MySQL. Por exemplo, LAST_INSERT_ID() (sem um argmento) retornará a nova ID. A função mysql_insert_id() da API C também pode ser usada para obter o valor.

    Note que como mysql_insert_id() só é atualizado depois de instruções INSERT e UPDATE, você não pode utilizar a função da API C para recuperar o valor para LAST_INSERT_ID(expr) depois de executar outra instrução SQL como SELECT ou SET. Veja mais informações sobre isto na Seção 12.1.3.32, “mysql_insert_id().

  • FORMAT(X,D)

    Formata o número X com um format como '#,###,###.##', arredondado para D casas decimais, e retorna o resultado como uma string. Se D é 0, o resultado não terá nehum ponto decimal ou parte fracionária:

    mysql> SELECT FORMAT(12332.123456, 4);
    -> '12,332.1235'
    mysql> SELECT FORMAT(12332.1,4);
    -> '12,332.1000'
    mysql> SELECT FORMAT(12332.2,0);
    -> '12,332'
    

  • VERSION()

    Retorna uma string indicando a versão do servidro MySQL:

    mysql> SELECT VERSION();
    -> '3.23.13-log'
    

    Note que se seu versão finalizar com -log, significa que o log está habilitado.

  • CONNECTION_ID()

    Retorna a identificação (ID da thread) desta conexão. Cada conexão tem seu próprio ID único:

    mysql> SELECT CONNECTION_ID();
    -> 23786
    

  • GET_LOCK(str,temo_limite)

    Tenta conseguir uma trava com o nome dado pela string str, com um tempo limite de timeout segundos. Retorna 1 se o bloqueio foi obtido com sucesso, 0 se o tempo esgotou (por exemplo, porque outro cliente ja bloqueou o nome), ou NULL se uma erro ocorreu (tal como estouro de memória ou a threado tiver sido finalizada com mysqladmin kill). Uma trava é liberada quando você executa RELEASE_LOCK(), executa uma nova GET_LOCK(), ou a thread termina. (tanto de forma normal quanto anormal) Esta função pode ser usada para implementar bloqueio de aplicação ou para simular registros travados. Nomes são bloqueados em uma base ampla do servidor. Se um nome foi bloqueado por um cliente, GET_LOCK() trava qualquer pedido de bloqueio de outro cliente com o mesmo nome. Isto permite que clientes que concordam com um dado nome da trava possam usar a string para realizar travamento de consultas cooperativas:

    mysql> SELECT GET_LOCK("lock1",10);
    -> 1
    mysql> SELECT IS_FREE_LOCK("lock2");
    -> 1
    mysql> SELECT GET_LOCK("lock2",10);
    -> 1
    mysql> SELECT RELEASE_LOCK("lock2");
    -> 1
    mysql> SELECT RELEASE_LOCK("lock1");
    -> NULL
    

    Note que a segunda chamada de RELEASE_LOCK() retorna NULL porque a trava "lock1" foi liberada automaticamente pela segunda chamada GET_LOCK().

  • RELEASE_LOCK(str)

    Libera a trava nomeada pela string str que foi obtida com GET_LOCK(). Retorna 1 se a trava foi liberada, 0 se a trava não foi bloquada pela thread (caso onde a trava não é liberada), e NULL se o nome da trava não existe. (A trava nunca exitirá se ela nunca for obtida pela chamada de GET_LOCK() ou se ela ja tiver sido liberada).

    A instrução DO é conveniente para ser utilizada com RELEASE_LOCK(). Veja mais informações sobre isto na Seção 6.4.10, “Sintaxe DO.

  • IS_FREE_LOCK(str)

    Verifica se a trava chamada str está livre para ser utilizada (ex. não está bloqueada). Retorna 1 se a trava está liver (ninguém a esta usando), 0 se a trava está em uso, e NULL caso ocorra erro (como argumentos incorretos).

  • BENCHMARK(cont,expr)

    A função BENCHMARK() executa a expressão expr repetidamente cont vezes. Ela pode ser usada para medir a velocidade em que o MySQL processa a expressão. O valor resultante é sempre 0. A intenção é usá-la no clientei mysql, relatando o tempo de execução da consulta:

    mysql> SELECT BENCHMARK(1000000,ENCODE("hello","goodbye"));
    +----------------------------------------------+
    | BENCHMARK(1000000,ENCODE("hello","goodbye")) |
    +----------------------------------------------+
    | 0 |
    +----------------------------------------------+
    1 row in set (4.74 sec)
    

    O tempo relatado é o tempo decorrido no cliente, não o tempo de CPU no servidor. Pode ser aconselhável executar BENCHMARK() diversas vezes e interpretar o resultado cosiderado o peso da carga da maquina servidora.

  • INET_NTOA(expr)

    Dado um endereço numérico de rede (4 ou 8 bytes), retorna a representacão no formato com pontos do endereço como uma string:

    mysql> SELECT INET_NTOA(3520061480);
    -> "209.207.224.40"
    

  • INET_ATON(expr)

    Dada a represenação com pontos de um endereço de rede como uma string, retorna um inteiro que representa o valor numérico deste endereço. Endereços podem ter 4 ou 8 bytes de endereçamento:

    mysql> SELECT INET_ATON("209.207.224.40");
    -> 3520061480
    

    O número gerado é sempre na ordem de bytes da rede; por exemplo o número acima é calculado como 209*256^3 + 207*256^2 + 224*256 +40.

  • MASTER_POS_WAIT(nome_log, log_pos [, tempo_limite])

    Envia blocos o slave alcançar (ex.: ter lido e aplicado todas as atualizações) a posição específica no log master. Se a informação master não está inicializada, ou se os argumentos estão incorretos, retorna NULL. Se o slave não está em execução, enviará blocos e irá esperar até que ele seja iniciado e vá para (ou passe por) a posição especificada. Se o slave já passou pela posição especificada, retorna imediatamente.

    Se tempo_limite (novo na versão 4.0.10) é especificado, irá esperar até que tempo_limite segundos tenham se passado. tempo_limite deve ser maior que 0; zero ou um tempo_limite negativo significa sem tempo_limite. O valor de retorno é o número de eventos de log que ele tem que esperar para obter a posição especificada, NULL no caso de erro, ou -1 se o tempo_limite tiver sido excedido.

    O comando é útil para controle de sincronização mo master/slave.

  • FOUND_ROWS()

    Uma instrução SELECT pode incluir uma cláusula LIMIT para restringir o número de linhas que o servidor retorna para um cliente. Em alguns casos, é desejável saber quantas linhas a instrução teria retornado sem o LIMIT, mas sem executar a instrução novamente. Para obter esta contagem de linhas, inclua uma opção SQL_CALC_FOUND_ROWS na instrução SELECT, então chame FOUND_ROWS() loga depois:

    mysql> SELECT SQL_CALC_FOUND_ROWS * FROM nome_tabela
    WHERE id > 100 LIMIT 10;
    mysql> SELECT FOUND_ROWS();
    

    O segundo SELECT irá retornar um número indicando quantas linhas o primeiro SELECT teria retornado se ele fosse escrito sem a cláusula LIMIT. (Se o instrução SELECT anterior não inclui a opção SQL_CALC_FOUND_ROWS, então FOUND_ROWS() pode retornar um resultado diferente quando LIMIT é usado daquele que não é usado).

    Note que se você estiver usando SELECT SQL_CALC_FOUND_ROWS ..., o MySQL tem que calcular quantos registros existem em todo o conjunto de resultados. No entanto, isto é mais rápido que se você não utilizar LIMIT, já que o resultado precisa ser enviado ao cliente.

    SQL_CALC_FOUND_ROWS e FOUND_ROWS() podem ser úteis em situações em que você queira restringir o número de registros que uma consulta retorna, mas também determinar o número de linhas em todo o resultado sem executar a consulta novamente. Um exemplo é um script web que apresenta um display paginado contendo links para as páginas que mostram outras seções de um resultado de busca. Usar FOUND_ROWS() lhe permite determinar quantos outras páginas são necessárias para o resto do resultado.

    O uso de SQL_CALC_FOUND_ROWS e FOUND_ROWS() é mais complexa para consultas UNION que para instruções SELECT simples, porque LIMIT pode ocorrer em vários lugares em um UNION. Ele pode ser aplicado a instruções SELECT individuais no UNION, ou globais ao resultado UNION como um todo.

    A intenção de SQL_CALC_FOUND_ROWS para UNION é que ele deve retornar a contagem das linhas que seriam retornadas sem um LIMIT global. As consições para uso de SQL_CALC_FOUND_ROWS com UNION são:

    • A palavra chave SQL_CALC_FOUND_ROWS deve aparecer na primeira SELECT do UNION.

    • O valor de FOUND_ROWS() é exato apenas se UNION ALL for usado. Se UNION sem ALL for usado, as duplicatas são removidas e o valor de FOUND_ROWS() é apenas aproximado.

    • Se nenhum LIMIT está presente no UNION, SQL_CALC_FOUND_ROWS é ignorado e retorna o número de linhas na tabela temporária que é criada para processar o UNION.

    SQL_CALC_FOUND_ROWS e FOUND_ROWS() estão disponíveis a partir da versão 4.0.0 do MySQL.

6.3.7. Funções e Modificadores para Usar com Cláusulas GROUP BY

6.3.7.1. Funções GROUP BY

Se você utiliza um função de agrupamento em uma instrução que não contenha um cláusula GROUP BY, equivale a fazer um agrupamento com todos os registros.

  • COUNT(expr)

    Retorna a quantidade de valores não-NULL nos registros recuperados por uma instrucao SELECT:

    mysql> SELECT estudante.nome_estudente,COUNT(*)
    -> FROM estudante,curso
    -> WHERE estudante.id_estudante=curso.id_estudante
    -> GROUP BY nome_estudante;
    

    COUNT(*) difere um pouco ao retornar o número de registros recuperados, se eles possuírem ou não valores NULL.

    COUNT(*) é otimizado para retornar muito rápido se SELECT recuoperar registros de uma tabela, nenhuma outra coluna for retornada, e não houver nenhuma cláusula WHERE. Por exemplo:

    mysql> SELECT COUNT(*) FROM estudente;
    

    Esta otimizacão se aplica apenas a tabelas MyISAM e ISAM, porque uma contagem exata de registros é armazenada para estes tipos de tabelas e podem ser acessadas muito rapidamente. Para mecanismos de armazenamentos transacionais (InnodB, BDB), armazenar um contagem de registros exatos é mais problemático porque múltiplas transações podem estar ocorrendo, e cada uma pode afetar a contagem.

  • COUNT(DISTINCT expr,[expr...])

    Retorna a quantidade de regiastros com valores não-NULL diferentes:

    mysql> SELECT COUNT(DISTINCT resultados) FROM estudente;
    

    No MySQL você pode obter o número de combinação de expressões distintas que não contém NULL fornecendo uma lista de expressões. No SQL-99 você teria que concatenar todas as expressão utilizando COUNT(DISTINCT ...).

  • AVG(expr)

    Retorna o valor médio de expr:

    mysql> SELECT nome_estudante, AVG(nota_teste)
    -> FROM estudante
    -> GROUP BY nome_estudante;
    

  • MIN(expr), MAX(expr)

    Retorna o valor mínimo o u máximo de expr. MIN() e MAX() poder usar uma string como argumento; nestes casos eles retornam o a string de valor mínimo ou máximo. Veja mais informações sobre isto na Seção 5.4.3, “Como o MySQL Utiliza Índices”.

    mysql> SELECT nome_estudante, MIN(nota_teste), MAX(nota_teste)
    -> FROM estudante
    -> GROUP BY nome_estudante;
    

    Em MIN(), MAX() e outras funções de agrupamento o MySQL, atualmente, compara colunas ENUM e SET pelo seu valor string em vez de fazê-lo pela sua posição relativa de string no conjunto. Isto será retificado.

  • SUM(expr)

    Retorna a soma de expr. Note que se o conjunto de retorno não possuir registros ele retornará NULL!

  • GROUP_CONCAT(expr)

    Sintaxe completa:

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
    [ORDER BY {inteiro_sem_sinal | nome_coluna | formula} [ASC | DESC] [,col ...]]
    [SEPARATOR valor_str])
    

    Esta função foi adicionada na versão 4.1 do MySQL. Ele retorna a string resultante contendo valores de um grupo:

    mysql> SELECT nome_estudante,
    -> GROUP_CONCAT(note_teste)
    -> FROM estudante
    -> GROUP BY nome_estudante;
    ou
    mysql> SELECT nome_estudante,
    -> GROUP_CONCAT(DISTINCT nota_teste
    -> ORDER BY nota_teste DESC SEPARATOR " ")
    -> FROM estudante
    -> GROUP BY nome_estudante;
    

    No MySQL você pode obter valores de combinações de expressões concatenados. Você pode eliminar valores duplicados utilizando DISTINCT. Se você quiser ordenar valores no resultado você deve utilizar a cláusula ORDER BY. Para ordenar inversamente, adicione a palavra chave DESC (descendente) ao nome da coluna que você está ordenando na cláusula ORDER BY. O padrão é a ordem crescente; pode-se também especificála explicitamente usando a palavra chave ASC. SEPARATOR é o valor string que deve ser inserido entre os valores no resultado. O padrão é um virgula ('","'). Você pode remover o separador especificando SEPARATOR "".

    Você pode definir um tamanho máximo permitido com a variável group_concat_max_len em sua configuração. A sintaxe para se fazer isto em tempo de execução é:

    SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
    

    Se um tamanho máximo tiver sido atribuido, o resultado é truncado no seu tamanho máximo.

    A função GROUP_CONCAT() é uma implementação aprimorada da função básica LIST() suportada pelo Sybase SQL Anywhere. GROUP_CONCAT() é compatível com a funcionalidade extrwemamente limitada de de LIST(), se utilizada em apenas uma coluna e nenhuma outra opção é especificada. LIST() não tem uma ordem de classificação padrão.

  • VARIANCE(expr)

    Retorna a variância padrão de expr (considerando linha como toda a população, não com uma amostra; assim ele tem o número de linhas como denominador). Esta é uma extensão do SQL-99 (disponível somente a partir da versão 4.1).

  • STD(expr), STDDEV(expr)

    Retorna o desvio padrão de expr (a raiz quadrada de VARIANCE()). Esta é uma extensão do SQL-99. O formato STDDEV() desta função é fornecida para compatibilidade com Oracle.

  • BIT_OR(expr)

    Retorna o resultado da operação binária OR de todos os bits em expr. O calcululo é relizado com precisão de 64 bits (BIGINT).

    A função retortna 0 se não houver registros coincidentes.

  • BIT_XOR(expr)

    Retorna o bitwise XOR de todos os bits em expr. O calculo é relizado com precisão de 64-bits (BIGINT).

    A função retorna 0 se não houver linhas coincidentes.

    Esta função está disponível a partir do MySQL 4.1.1.

  • BIT_AND(expr)

    Retorna o resultado da operação binária AND de todos os bits em expr. O calcululo é relizado com precisão de 64 bits (BIGINT).

    A função retortna 1 se não houver registros coincidentes.

6.3.7.2. Modificadores GROUP BY

No MySQL 4.1.1, a cláusula GROUP BY permite um modificador WITH ROLLUP que faz com que uma linha extra seja adicionada à saida resumo. Estas linhas representam operações de resumo de nível mais alto (ou super agregadas). Assim, o ROLLUP permite que você responda questões em multiplos níveis de análise com uma única consulta. Ele pode ser usado, por exemplo, para fornecer suporte para operações OLAP (Online Analytical Processing - Processamento Analítico OnLine).

Como ilustração, suponha que uma tabela chamada sales tenha as colunas year, country, product e profit para registrar as vendas lucrativas:

CREATE TABLE sales
(
year INT NOT NULL,
country VARCHAR(20) NOT NULL,
product VARCHAR(32) NOT NULL,
profit INT
);

O conteúdo da tabela pode ser resumido pode ano com um simples GROUP BY como este:

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
+------+-------------+

Esta saída mostra o lucro total para cada ano, mas se você também quiser determinar o lucro total somado em todos os anos, você deve adicionar os valores adicionais ou executar uma consulta adicional.

Ou você pode usar o ROLLUP, que fornece os dois níveis de análise com uma única consulta. Adicionando um modificador WITH ROLLUP a cláusula GROUP BY faz com que a consulta produza outra linha que mostra o total geral de todos os anos:

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+-------------+

A linha de total super-agrupada é identificada pelo valor NULL na coluna year.

ROLLUP tem um efeito mais complexo quando há múltiplas colunas GROUP BY. Neste caso, cada vez que houver um ``break'' (alteração no valor) em qualquer agrupamento, com exceção da última coluna, a consulta produz um linha resumo super-agrupada extra.

Por exemplo, sem ROLLUP, um resumo na tabela sales baseada no year, country e product pode se parecer com isto:

mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | Finland | Phone | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
+------+---------+------------+-------------+

A saída indica os valores resumidos apenas no nível year/country/product da análise. Quando ROLLUP é adicionado, a consulta produz diversas linhas extras:

mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+

Para esta consulta, adicionar ROLLUP faz com que a saída inclua uma informação resumida nos qualtro níveis de análise, não só em um. Aqui está como interpretar a saída ROLLUP:

  • Seguindo cada conjunto de produtos para um dado ano e país, um linha de resumo extra é produzida mostrando o total para todos os produtos. Estas linhas têm a coluna product atribuída com NULL.

  • Seguindo cada conjunto de linhas para um dado ano, uma l;inha resumo extra é produzida mostrando o total para todos os países e produtos. Estas linhas têm as colunas country e products atribuídas com NULL.

  • Finalmente, seguindo todas as outras linhas, um linha resumo extra é produzida mostrando o total geral para todos os anos, países e produtos. Esta linha tem as colunas year, country e products atribuídas com NULL.

Outras Considerações ao Usar ROLLUP

O seguinte item lista alguns comportamentos específicaos para a implementação do ROLLUP no MySQL:

Quando você usa ROLLUP, você não pode usar uma cláusula ORDER BY para ordenar os resultados. (Em outras palavras, ROLLUP e ORDER BY são exclusivos mutualmente.) No entanto, você ainda tem algum controle sobre a ordem de ordenação. O GROUP BY no MySQL ordena os resultados, e você pode usar as palavras chaves ASC e DESC explicitamente com colunas chamadas na lista GROUP BY para especificar a ordem de classificação para colunas individuais. (A linha resumo de nível mais alto adicionado por ROLLUP ainda aparece depois da linha para as quais elas são calculadas, considerando a ordenação.)

LIMIT pode ser usado para restringir o númerod e linhas retornadas para o cliente. LIMIT é aplicado depois do ROLLUP, assim o limite se aplica contra as linhas extras adicionadas por ROLLUP. Por exemplo:

mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP
-> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
+------+---------+------------+-------------+

Note que usar LIMIT com ROLLUP pode produzir resultados mais difíceis de interpretar, porque você têm menos contexto para entender as linhas super agrupadas.

O indicador NULL em cada linha super-agrupadas são produzidas quando a linha é enviada para o cliente. O servidor olha por cada coluna chamada na cláusula GROUP BY seguindo aquela mais a esquerda que tem o valor alterado. Para qualquer coluna no resultado com o nome que é uma combinação léxica de qualquer daqueles nomes, seu valor é definido com NULL. (Se você especifica o agrupamento de colunas pelo número da coluna, o servidor identifica quais colunas definir com NULL pelo número.)

Como os valores NULL em linhas super agrupadas são colocadas dentro do resultado como um estágio posterior no processamento da consulta, você não pode testá-los com valores NULL dentro da própria consulta. Por exemplo, você não pode adicionar HAVING product IS NULL a consulta para eliminar da saída todas as linhas com exceção das agrupadas.

Por outro lado, o valor NULL aparece como NULL no lado do cliente e pode ser testado usando qualquer interface de programação do cliente MySQL.

6.3.7.3. GROUP BY com Campos Escondidos

O MySQL tem extendido o uso de GROUP BY. Você pode utilizar colunas ou cálculos na expressão SELECT que não aparecem na parte GROUP BY. Ele espera por qalquer valor possível para este grupo. Você pode utilizar isto para conseguir um melhor desempenho evitando ordenação e agrupamento em itens desnecessários. Por exemplo, você não precisa fazer um agrupamento em cliente.nome na consulta seguinte:

mysql> SELECT pedido.idcliente,cliente.nome,MAX(pagamento)
-> FROM pedido, cliente
-> WHERE pedido.idcliente = cliente.idcliente
-> GROUP BY pedido.idcliente;

No padrão SQL, você teria que adicionar cliente.nome a cláusula GROUP BY. No MySQL, o nomê é redundante se você não o executa em modo ANSI.

Não utilize este recurso se as colunas omitidas na parte GROUP BY não são únicas no grupo! Você obterá resultados inexperados.

Em alguns casos, você pode utilizar MIN e MAX para obter o valor de uma coluna específica, mesmo que ele não seja único. O exemplo seguinte fornece o valor de coluna do registro contendo o menor valor na coluna ordem:

SUBSTR(MIN(CONCAT(RPAD(ordem,6,' '),coluna)),7)

Veja mais informações sobre isto na Seção 3.6.4, “As Linhas Armazenando o Group-wise Máximo de um Certo Campo”.

Note que se você estiver usando a versão 3.22 do MySQL (ou anterior) ou se estiver tentando seguir o SQL-99, você não pode utilizar expressões nas cláusulas GROUP BY or ORDER BY. Você pode contornar esta limitação utilizando um apelido para a expressão:

mysql> SELECT id,FLOOR(value/100) AS val FROM nome_tabela
-> GROUP BY id,val ORDER BY val;

Na versão 3.23 do MySQL você pode fazer:

mysql> SELECT id,FLOOR(value/100) FROM nome_tabela ORDER BY RAND();