XOOPS Brasil

 

Tipos de Campos do MySQL





MySQL suporta um certo números de tipos de campos que podem ser agrupaos em três categorias: tipos numéricos, tipos de data e hora, e tipos string (caracteres). Esta seção primeiro lhe dá uma visão geral dos tipos disponíveis e resume as exigencias de armazenamento em cada tipo de coluna, também fornece uma descrição mais detalhada da propriedade dos tipos em cada categoria. A visão dada é propositalmente breve. As descrições mais detalhdas devem ser consultadas para informações adicionais sobre tipos de campo particulares como os formatos permitidos nos quais você pode especificar valores.

Os tipos de campos suportados pelo MySQL estão listados abaixo: As seguintes letras são usadas como código nas descrições:

  • M

    Indica o tamanho máximo do display. O tamanho máximo oficial do display é 255.

  • D

    Aplica aos tipos de ponto flutuante e indica o número de digitos após o ponto decimal. O maior valor possível é 30, mas não pode ser maior que M-2.

Colchetes (‘[’ and ‘]’) indicam partes de tipos específicos que são opicionais

Note que se você especificar ZEROFILL para um campo MySQL automaticamente irá adicionar o atributo UNSIGNED ao campo.

Aviso: você deve estar ciente de que quando fizer uma subtração entre valores inteiros, onde um deles é do tipo UNSIGNED, o resultado será sem sinal! Veja mais informações sobre isto na Seção 6.3.5, “Funções de Conversão”.

  • TINYINT[(M)] [UNSIGNED] [ZEROFILL]

    Um inteiro muito pequeno. A faixa deste inteiro com sinal é de -128 até 127. A faixa sem sinal é de 0 até 255.

  • BIT, BOOL, BOOLEAN

    Estes são sinônimos para TINYINT(1).

    O sinônimo BOOLEAN foi adicionado na versão 4.1.0.

    Um tipo boolean verdadeiro será introduzido de acordo com o SQL-99.

  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

    Um inteiro pequeno. A faixa do inteiro com sinal é de -32768 até 32767. A faixa sem sinal é de 0 a 65535.

  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

    Um inteiro de tamanho médio. A faica com sinal é de -8388608 a 8388607. A faixa sem sinal é de 0 to 16777215.

  • INT[(M)] [UNSIGNED] [ZEROFILL]

    Um inteiro de tamanho normal. A faixa com sinal é de -2147483648 a 2147483647. A faixa sem sinal é de 0 a 4294967295.

  • INTEGER[(M)] [UNSIGNED] [ZEROFILL]

    Este é um sinônimo para INT.

  • BIGINT[(M)] [UNSIGNED] [ZEROFILL]

    Um inteiro grande. A faixa com sinal é de -9223372036854775808 a 9223372036854775807. A faixa sem sinal é de 0 a 18446744073709551615.

    Existem algumas coisas sobre campos BIGINT sobre as quias você deve estar ciente:

    • Todas as operações aritiméticas são feitas usando valores BIGINT ou DOUBLE com sinal, não devemos utilçizar inteiros sem sinal maiores que 9223372036854775807 (63 bits) exceto com funções ded bit! Se você fizer isto, alguns dos últimos digitos no resultado podem estar errados por causa de erros de arredondamento na conversão de BIGINT para DOUBLE.

      O MySQL 4.0 pode tratar BIGINT nos seguintes casos:

      • Usar inteiros para armazenar grandes valores sem sinais em uma coluna BIGINT.

      • Em MIN(big_int_column) e MAX(big_int_column).

      • Quando usar operadores (+, -, *, etc.) onde ambos os operandos são inteiros.

    • Você pode armazenar valores inteiro exatos em um campo BIGINT aramzenando-os como string, como ocorre nestes casos não haverá nenhuma representação intermediaria dupla.

    • -’, ‘+’, e ‘*’ serão utilizados em cálculos aritiméticos BIGINT quando ambos os argumentos forem valores do tipo INTEGER! Isto significa que se você multilicar dois inteiros grandes (ou obter resultados de funções que retornam inteiros) você pode obter resultados inesperados quando o resultado for maior que 9223372036854775807.

  • FLOAT(precisão) [UNSIGNED] [ZEROFILL]

    Um número de ponto flutuante. Não pode ser sem sinal. precisão pode ser <=24 para um número de ponto flutuante de precisão simples e entre 25 e 53 para um número de ponto flutuante de dupla-precisão. Estes tipos são como os tipos FLOAT e DOUBLE descritos logo abaixo. FLOAT(X) tem o mesma faixa que os tipos correspondentes FLOAT e DOUBLE, mas o tamanho do display e número de casas decimais é indefinido.

    Na versão 3.23 do MySQL, este é um verdadeiro valor de ponto flutuante. Em versões anteriores , FLOAT(precisão) sempre tem 2 casas decimais.

    Note que o uso de FLOAT pode trazer alguns problemas inesperados como nos cálculos já que em MySQL todos são feitos com dupla-precisão. Veja mais informações sobre isto na Seção A.5.6, “Resolvendo Problemas Com Registros Não Encontrados”.

    Esta sintaxe é fornecida para comptibilidade com ODBC.

  • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

    Um número de ponto flutuante pequeno (precisão simples). Os valores permitidos estão entre -3.402823466E+38 e -1.175494351E-38, 0 e entre 1.175494351E-38 e 3.402823466E+38. Se UNSIGNED for especificado, valores negativos não são permitidos O M é a largura do display e o D é o número de casas decimais. FLOAT sem um argumento ou FLOAT(X) onde X <=24 tende a um número de ponto flutuante de precisão simples.

  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

    Um número de ponto flutuante de tamanho normal (dupla-precisão). Valores permitidos estão entre -1.7976931348623157E+308 e -2.2250738585072014E-308, 0 e entre 2.2250738585072014E-308 e 1.7976931348623157E+308. Se UNSIGNED for especificado, valores negativos não são permitidos. O M é a largura do display e o D é número de casa decimais. DOUBLE sem argumento ou FLOAT(X) onde 25 <= X <= 53 são números de ponto flutuante de dupla-precisão.

  • DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

    Estes são sinônimos para DOUBLE.

  • DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

    Um número de ponto flutuante não empacotado. Se comporta como um campo CHAR: ``não empacotado'' significa que o número é armazenado como uma string, usando um caracter para cada digito do valor. O ponto decimal e, para números negativos, o sinal de menos (‘-’), não são contados em M (mas é reservado espaço para isto). Se D for 0, os valores não terão ponto decimal ou parte fracionária. A faixa máxima do valor DECIMAL é a mesma do DOUBLE, mas a faixa atual para um campo DECIMAL dado pode ser limitado pela escolha de M e D. Se UNSIGNED é especificado, valores negativos não são permitidos.

    Se D não for definido será considerado como 0. Se M não for definido é considerado como 10.

    Note que antes da versão 3.23 do MySQL o argumento M deve incluir o espaço necessário para o sinal é o ponto decimal.

  • DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

    Este é um sinônimo para DECIMAL.

    O alias FIXED foi adicionado na versão 4.1.0 para compatibilidade com outros servidores.

  • DATE

    Uma data. A faixa suportada é entre '1000-01-01' e '9999-12-31'. MySQL mostra valores DATE no formato 'AAAA-MM-DD', mas permite a você a atribuir valores a campos DATE utilizando tanto strings quanto números. Veja mais informações sobre isto na Seção 6.2.2.2, “Os Tipos DATETIME, DATE e TIMESTAMP.

  • DATETIME

    Um combinação de hora e data. A faixa suportada é entre '1000-01-01 00:00:00' e '9999-12-31 23:59:59'. MySQL mostra valores DATETIME no formato 'AAAA-MM-DD HH:MM:SS', mas permite a você que atribuir valores a campos DATETIME utilizado strings ou números. Veja mais informações sobre isto na Seção 6.2.2.2, “Os Tipos DATETIME, DATE e TIMESTAMP.

  • TIMESTAMP[(M)]

    Um timestamp. A faixa é entre '1970-01-01 00:00:00' e algum momento no ano 2037.

    No MySQL 4.0 ou anteriores, os valores TIMESTAMP são exibidos nos formatos YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, ou YYMMDD, dependendo se M é 14 (ou não definido), 12, 8 ou 6, mas permite a você atribuir valores ao campo TIMESTAMP usando strings ou números.

    Um campo TIMESTAMP é util para gravar a data e a hora em uma operação de INSERT or UPDATE porque é automaticamente definido a data e a hora da operação mais recente se você próprio não especificar um valor. Você também pode definir a data e a hora atual atribuindo ao campo um valor NULL. Veja mais informações sobre isto na Seção 6.2.2, “Tipos de Data e Hora”.

    Desde o MySQL 4.1, TIMESTAMP é retornado com um string com o formato 'YYYY-MM-DD HH:MM:SS'. Se você deseja tê-lo como um número você deve adcionar +0 a coluna timestamp. Teimestamp de tamanhos diferentes não são supoortados. Desde a versão 4.0.12, a opção --new pode ser usada para fazer o servidor se comportar como na versào 4.1.

    Um TIMESTAMP sempre é armazenado em 4 bytes. O argumento M só afeta como a coluna TIMESTAMP é exibida.

    Note que colunas do tipo TIMESTAMP(M) columns onde M é 8 ou 14 são apresentadas como números enquanto as outras colunas TIMESTAMP(M) são strings. Isto é apenas para assegurar que podemos eliminar e restaurar com segurança tabelas com estes tipos! Veja mais informações sobre isto na Seção 6.2.2.2, “Os Tipos DATETIME, DATE e TIMESTAMP.

  • TIME

    Uma hora. A faixa é entre '-838:59:59' e '838:59:59'. MySQL mostra valores TIME no formato 'HH:MM:SS', mas permite a você atribuir valores para as colunas TIME usando strings ou números. Veja mais informações sobre isto na Seção 6.2.2.3, “O Tipo TIME.

  • YEAR[(2|4)]

    Um ano no formato de 2 ou 4 digitos (padrão são 4 digitos). Os valores permitidos estão entre 1901 e 2155, 0000 no formato de 4 digitos, e 1970-2069 se você estiver usando o formato de 2 digitos (70-69). MySQL mostra valores YEAR no formato YYYY, mas permie atribuir valores aos campos do tipo YEAR usando strings ou números. (O tipo YEAR é novo na versão 3.22 do MySL). Veja mais informações sobre isto na Seção 6.2.2.4, “O Tipo YEAR.

  • [NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]

    Uma string de tamanho fixo que é sempre preenchida a direita com espaços até o tamanho especificado quando armazenado. A faixa de M é de 1 a 255 caracteres. Espaços extras são removidos quando o valor é recuperado. Valores CHAR são ordenados e comparados no modo caso insensitivo de acordo com o conjunto de caracteres padrão, a menos que a palavra chave BINARY seja utilizada.

    A partir da versão 4.1.0, se o valor M especificado é maio que 255, o tipo de coluna é convertido para TEXT. Este é um recurso de compatibilidade.

    NATIONAL CHAR (ou em sua forma reduzida NCHAR) é o modo SQL-99 de definir que um campo CHAR deve usar o conjunto CHARACTER padrão. Este é o padrão no MySQL.

    CHAR é uma simplificação para CHARACTER.

    A partir da versão 4.1.0, o atributo ASCII pode ser especificado o que atribui o conjunto de caracteres latin1 a coluna CHAR.

    A partir da versão 4.1.1, o atributo UNICODE pode ser especificado o que atribui o conjunto de caracteres ucs2 a coluna CHAR.

    O MySQL lhe permite criar um campo do tipo CHAR(0).Isto é muito útil quando você precisa de comptibilidade com aplicativos antigos que dependem da existência de uma coluna, mas que, na verdade, não utiliza um valor. Isto também é muito bom quando você precisa de uma coluna que só pode receber 2 valores. Um CHAR(0), que não é definido como um NOT NULL, só irá ocupar um bit e pode assumir 2 valores: NULL or "". Veja mais informações sobre isto na Seção 6.2.3.1, “Os Tipos CHAR e VARCHAR.

  • BIT, BOOL, CHAR

    This is a synonym for CHAR(1).

  • [NATIONAL] VARCHAR(M) [BINARY]

    Uma string de tamanho variável. NOTA: Espaços extras são removidos quando o caracter é armazenado (o que difere da especificação ANSI SQL). A faixa de M é de 1 a 255 characters. Valores VARCHAR são ordenados e comparados no modo caso insensitivo a menos que a palavra chave BINARY seja utilizada. Veja mais informações sobre isto na Seção 6.5.3.1, “Alteração de Especificações de Colunas”.

    A partir da versão 4.1.0, se o valor M especificado é maio que 255, o tipo de coluna é convertido para TEXT. Este é um recurso de compatibilidade.

    VARCHAR é uma simplificação para CHARACTER VARYING. Veja mais informações sobre isto na Seção 6.2.3.1, “Os Tipos CHAR e VARCHAR.

  • TINYBLOB, TINYTEXT

    Um campo BLOB ou TEXT com tamanho máximo de 255 (2^8 - 1) caracteres. Veja mais informações sobre isto na Seção 6.5.3.1, “Alteração de Especificações de Colunas”. Veja mais informações sobre isto na Seção 6.2.3.2, “Os Tipos BLOB e TEXT.

  • BLOB, TEXT

    Um campo BLOB ou TEXT com tamanho máximo de 65535 (2^16 - 1) caracteres. Veja mais informações sobre isto na Seção 6.5.3.1, “Alteração de Especificações de Colunas”. Veja mais informações sobre isto na Seção 6.2.3.2, “Os Tipos BLOB e TEXT.

  • MEDIUMBLOB, MEDIUMTEXT

    Um campo BLOB ou TEXT com tamanho máximo de 16777215 (2^24 - 1) caracteres. Veja mais informações sobre isto na Seção 6.5.3.1, “Alteração de Especificações de Colunas”. Veja mais informações sobre isto na Seção 6.2.3.2, “Os Tipos BLOB e TEXT.

  • LONGBLOB, LONGTEXT

    Um campo BLOB ou TEXT com tamanho máximo de 4294967295 ou 4G (2^32 - 1) caracteres. Veja mais informações sobre isto na Seção 6.5.3.1, “Alteração de Especificações de Colunas”. Veja mais informações sobre isto na Seção 6.2.3.2, “Os Tipos BLOB e TEXT. Até a versão 3.23 o protocolo cliente/servidor e tabelas MyISAM tinham um limite de 16M por pacote de transmissão/registro de tabela, a partir da versão 4.x o tamanho máximo permitido das colunas LONGTEXT ou LONGBLOB depende do tamanho máximo configurado para o pacote no protocolo cliente/servidor e da memória disponível. Veja mais informações sobre isto na Seção 6.2.3.2, “Os Tipos BLOB e TEXT.

  • ENUM('valor1','valor2',...)

    Uma enumeração. Um objeto string que só pode ter um valor, selecionado da lista de valores 'valor1', 'valor2', ..., NULL ou valor especial de erro "". Um ENUM pode ter um máximo de 65535 valores diferentes. Veja mais informações sobre isto na Seção 6.2.3.3, “O Tipo ENUM.

  • SET('valor1','valor2',...)

    Um conjunto. Um objeto string que pode ter zero ou mais valores, cada um deve ser selecionado da lista de valores 'valor1', 'valor2', .... Um SET pode ter até 64 membros. Veja mais informações sobre isto na Seção 6.2.3.4, “O Tipo SET.

6.2.1. Tipos Numéricos

MySQL suporta todos os tipos numéricos da ANSI/ISO SQL92. Estes tipos incluem o tipos de dados numéricos exatos (NUMERIC, DECIMAL, INTEGER, e SMALLINT), assim como o tipos de dados numéricos aproximados (FLOAT, REAL, e DOUBLE PRECISION). A palavra-chave INT é um sinônimo para INTEGER, e a palavra-chave DEC é um sinônimo para DECIMAL.

Os tipos NUMERIC e DECIMAL são implementados como o mesmo tipo pelo MySQL, como permitido pelo padrão SQL92. Eles são usados por valores para os quais é importante preservar a exatidão como, por exemplo, dados monetários. Quando é declarado um campo de algum desses tipos a precisão e a escala podem ser (e normalmente é) especificadas; por exemplo:

 salario DECIMAL(5,2)

Neste exemplo, 5 (precisão) representa o número de digitos decimais significantes que serão armazenados no valor, e 2 (escala) representa o número de dígitos que serão armazenados após o ponto decimal. Neste caso, no entanto, a faixa de valores que podem ser armazendos na coluna salario é de -99.99 a 99.99. (MySQL pode, na verdade, armazenar numeros acima de 999.99 neste campo porque ele não precisa armazenar o sinal para números positivos).

Em ANSI/ISO SQL92, a sintaxe DECIMAL(p) é equivalente a DECIMAL(p,0). Da mesma forma, a sintaxe DECIMAL é equivalente a DECIMAL(p,0), onde a implementação permite decidir o valor de p. MySQL ainda não suporta nenhuma dessas duas formas variantes dos tipos de dados DECIMAL/NUMERIC. Este, geralmente, não é um problema sério, já que os principais benefícios destes tipos derivam da habilidade de controlar precisão e escala explicitamente.

Valores DECIMAL e NUMERIC são armazenados como strings, ao invés de um número de ponto-flutuante binário, para preservar o precisão decimal destes valores. Um caracter é usado para cada digito, para o ponto decimal (se escala > 0), e para o sinal ‘-’ (para números negativos). Se escala é 0, valores DECIMAL e NUMERIC não contém ponto decimal ou parte fracionária.

A faixa máxima dos valores DECIMAL e NUMERIC é o mesmo do DOUBLE, mas a faixa real para um campo DECIMAL or NUMERIC pode ser limitado pela precisão ou pela escala para uma dada coluna. Quando é atribuído a uma coluna um valor com mais digitos após o ponto decimal do que o permitido especificado na escala, o valor é arredondado para aquela escala. Quando é atribuido um valor a uma coluna DECIMAL ou NUMERIC o qual excede a faixa determinada pelas precisão e escala especificada (ou padrão), MySQL armazena o valor correspondente ao final daquela faixa.

Como uma extensão do padrão ANSI/ISO SQL92, MySQL também suporta os tipos integrais TINYINT, MEDIUMINT, e BIGINT como listado nas tabelas abaixo. Outra extensão suportada pelo MySQL é especificar, opcionalmente, o tamanho do display de um valor inteiro entre parenteses seguindo o nome do tipo (por exemplo, INT(4)). Esta especificação opcional do tamanho é usada para preenchimento a esquerda do display de valores cujo tamanho é menor que o especificado para a coluna, mas não limita a faixa de valores que podem ser armazendos na coluna, nem o número de dígitos que serão mostrados para valores que excederem o tamanho especificado na coluna. Quando usados em conjunto com o atributo opcional de extensão ZEROFILL, o padrão do preenchimento de espaços é a substituição por zeros. Por exemplo, para uma coluna declarada com INT(5) ZEROFILL, o valor 4 é retornado como 00004. Note que se você armazenar valores maiores que a largura do display em um coluna do tipo inteiro, você pode ter problemas quando o MySQL gerar tabelas temporárias para algum join complicado, já que nestes casos o MySQL acredita que os dados cabem na largura original da coluna.

Todos os tipos inteiros podem ter um atributo opcional (não-padrão) UNSIGNED. Valores sem sinal podem ser usados quando você permite apenas números positivos em uma coluna e você precisa de uma faixa de valores um pouco maior para a coluna.

Desde o MySQL 4.0.2, tipos de ponto flutuante também podem ser sem sinal (UNSIGNED). Como no tipos inteiros, este atributoprevine que valores negativos sejam armazenados na coluna. Ao contrário dos tipos negativos, o valor máximo da faixa permitida permanece o mesmo.

O tipo FLOAT é usado para representar tipos de dados numéricos aproximados. O padrão SQL-92 permite uma especificação opcional da precisão (mas não da faixa do expoente) em bits, após a a palavra FLOAT e entre parenteses. A implementação MySQL também suporta esta especificação opcional de precisão. Quando FLOAT é usada para uma tipo de coluna sem especificação de precisão, MySQL utiliza quatro bytes para armazenar os valores. Uma sintaxe variante também é suportada, com dois numeros entre parenteses após a palavra FLOAT. Com esta opção, o primeiro número continua a representar a quantidade de bytes necessária para armazenar o valor, e o segundo número especifica o número de dígitos a serem armazenados e mostrados após o ponto decimal (como com DECIMAL e NUMERIC). Quando é pedido ao MySQL para armazenar um número em uma coluna com mais digitos decimais após o ponto decimal que o especificado para esta coluna, o valor é arredondado eliminando os digitos extras quando armazenado.

Os tipos REAL e DOUBLE PRECISION não aceitam especificações de precisão. Como uma extensão do padrão SQL-92, o MySQL reconhece DOUBLE como um sinônimo para o tipo DOUBLE PRECISION. Em constraste com a exigencia do padrão de que a precisão do tipo REAL seja menor que aquele usado pelo DOUBLE PRECISION, MySQL implementa ambos como valores de ponto flutuante de 8 bits de dupla precisão (quando não estiver executando em ``modo ANSI''). Para uma portabilidade máxima, códigos que requerem armazenamento de valores de dados numéricos aproximados usam FLOAT ou DOUBLE PRECISION sem especificação de precisão ou de numeros decimais.

Quando solicitado a armazenar um valor em uma coluna numérica que está fora da faixa permitida pelo tipo da coluna, o MySQL ajusta o valor ao limite da faixa permitida mais apropriado e armazena este valor.

Por exemplo, a faixa de uma coluna INT é de -2147483648 a 2147483647. Se você tentar inserir -9999999999 em uma coluna INT, o valor é ajustado para o limite mais baixo da faixa de valores e -2147483648 é armazenado. Da mesma forma, se você tentar inserir 9999999999, 2147483647 será armazenado.

Se o campo INT é UNSIGNED, o tamanho da faixa do campo é o mesmo mas o limite passa a ser de 0 a 4294967295. Se você tentar armazenar -9999999999 e 9999999999, os valores armazenados na coluna serão 0 e 4294967296.

Conversões que ocorrem devido a ajustes são relatados como ``avisos'' para ALTER TABLE, LOAD DATA INFILE, UPDATE, e instruções INSERT multi-registros.

TipoBytesDeAté
TINYINT1-128127
SMALLINT2-3276832767
MEDIUMINT3-83886088388607
INT4-21474836482147483647
BIGINT8-92233720368547758089223372036854775807

6.2.2. Tipos de Data e Hora

Os tipos de data e hora são DATETIME, DATE, TIMESTAMP, TIME, e YEAR. Cada um desses tipos tem uma faixa de valores legais, assim com um valor ``zero'' que é usado quando você especifica um valor ilegal. Note que o MySQL permite que você armazene certos valores de datas inexistentes, como 1999-11-31. A razão para isto é que pensamos que é responsabilidade do aplicativo tratar das verificações de data, não do servidor SQL. Para fazer uma verificação 'rápida' de data, MySQL só checa se o mês está na faixa de 0-12 e o dia está na faixa de 0-31. As faixas acima são definidas desta forma porque MySQL lhe permite armazenar, em um campo DATE ou DATETIME, datas onde o dia ou o dia/mês são zero. Isto é extremamente útil para aplicativos que precisam armazenar uma data de nascimento na qual você não sabe a data exata. Nestes casos você simplesmente armazena a data como 1999-00-00 ou 1999-01-00. (Você não pode esperar obter um valor correto para funções como DATE_SUB() ou DATE_ADD para datas como estas.)

Aqui estão algumas considerações para ter em mente quando estiver trabalhando com tipos de data e hora.

  • MySQL recupera valores para um tipo de data ou hora dado em um formato padrão, mas ele tenta interpretar uma variedade de formatos para os valores fornecidos (por exemplo, quando você especifica um valor a ser atribuido ou comparado a um tipo de data ou hora). No entanto, só os formatos descritos na seção seguinte são suportados. É esperado que você forneça valores permitidos. Resultados imprevisiveis podem ocorrer se você usar outros formatos.

  • Embora o MySQL tente interpretar valores em diversos formatos, ele sempre espera que a parte da data referente ao ano esteja mais a esquerda do valor. Datas devem ser dadas na ordem ano-mês-dia (por exemplo, '98-09-04'), ao invés das ordens mais usadas mês-dia-ano ou dia-mês-ano (por exemplo: '09-04-98', '04-09-98').

  • MySQL converte automaticamente um tipo de data ou hora em um número se o valor é usado em um contexto numérico, e vice-versa.

  • Quando o MySQL encontra um valor para um tipo de data ou hora que está fora da faixa permitida ou é ilegal neste tipo (veja o início desta seção), ele converte o valor para ``zero''. (A exceção ocorre no campo TIME, onde o valor fora da faixa é ajustado para o valor limite apropriado na faixa de valores deste tipo.) A tabela abaixo mostra o formato do valor ``zero'' para cada tipo:

    Tipo de ColunaValor ``Zero''
    DATETIME'0000-00-00 00:00:00'
    DATE'0000-00-00'
    TIMESTAMP00000000000000 (tamanho depende do tamanho do display)
    TIME'00:00:00'
    YEAR0000
  • Os valores ``zero'' são especiais, mas você pode armazenar ou fazer referência a eles explicitamente usando os valores mostrados na tabela. Você também pode fazer into usando '0' ou 0, o que é mais fácil de escrever.

  • Valores ``zero'' para data ou hora usados em MyODBC são convertidos automaticamente para NULL na versão 2.50.12 MyODBC e acima, porque ODBC não pode tratar tais valores.

6.2.2.1. Assuntos referentes ao ano 2000 (Y2K) e Tipos de Data

O MySQL tem sua própria segurança para o ano 2000 (see Seção 1.2.5, “Compatibilidade Com o Ano 2000 (Y2K)”), mas os dados entrados no MySQL podem não ter. Qualquer entrada contendo valores de ano de 2 digitos é ambíguo, porque o século é desconhecido. Tais valores devem ser interpretados na forma de 4 digitos já que o MySQL armazena anos internamente utilizando 4 digitos.

Para tipos DATETIME, DATE, TIMESTAMP e YEAR, MySQL interpreta datas com valores ambíguos para o ano usando as seguintes regras:

  • Valores de ano na faixa 00-69 são convertidos para 2000-2069.

  • Valores de anos na faixa 70-99 são convertidos para 1970-1999.

Lembre-se de que essas regras fornecem apenas palpites razoáveis sobre o que a sua data significa. Se a heurística usada pelo MySQL não produz o valor você deve fornecer entre sem ambiguidade contendo valores de ano de 4 digitos.

ORDER BY irá ordenar tipos YEAR/DATE/DATETIME de 2 digitos apropriadamente.

Note tembém que algumas funções com MIN() e MAX() irão converter TIMESTAMP/DATE para um número. Isto significa que um timestamp com ano de 2 digitos não irá funcionar corretamente com estas funções. A solução neste caso é converter o TIMESTAMP/DATE para um formato de ano de 4 digitos ou usar algo como MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS)).

6.2.2.2. Os Tipos DATETIME, DATE e TIMESTAMP

Os tipos DATETIME, DATE, e TIMESTAMP são relacionados. Esta seção descreve suas características, como eles se assemelham ou como se diferem.

O tipo DATETIME é usado quando você precisa de valores que contém informações sobre data e a a hora. MySQL recupera e mostra valores DATETIME no formato 'YYYY-MM-DD HH:MM:SS'. A faixa suportada é de '1000-01-01 00:00:00' até '9999-12-31 23:59:59'. (``Suportada'' significa que embora valores anteriores possam funcionar, não há nenhura garantia de disto.)

O tipo DATA é usado quando se necessita apenas do valor da data, sem a parte da hora. MySQL recupera e mostra valores do tipo DATA no formato 'YYYY-MM-DD'. A faixa suportada é de '1000-01-01' até '9999-12-31'.

A coluna do tipo TIMESTAMP possui comportamento e propriedade variado, dependendo da versão do MySQL e do modo SQL que o servidor está executando.

Comportamento do TIMESTAMP ao executar no modo MAXDB

Quando o MySQL está executando no modo SQPDB, o TIMESTAMP comporta como DATETIME. Nenhuma atualização automática da coluna TIMESTAMP ocorre, como descrito no parágrafo seguinte. O MySQL pode ser executado no modo MAXDB a partir da versão 4.1.1. Veja mais informações sobre isto na Seção 4.1.1, “Opções de Linha de Comando do mysqld.

Comportamento do TIMESTAMP quando não está executando no modo MAXDB

O tipo de campo TIMESTAMP fornece um tipo que pode ser usado para, automaticamente, marcar operações INSERT or UPDATE com a data e hora atual. Se você tiver multiplas colunas TIMESTAMP, só a primeira é atualizada automaticamente.

Atualizações automaticas da primeira coluna TIMESTAMP ocorrem sob qualquer uma das seguintes condições:

  • A coluna não é explicitamente especificada em uma instrução INSERT ou LOAD DATA INFILE.

  • A coluna não é explicitamente especificada em uma instrução UPDATE e e alguma outra coluna muda o valor. (Note que um UPDATE que coloca em uma coluna o mesmo valor que ele já possui não irá causar a atualização da coluna TIMESTAMP, porque se você atribui a uma coluna o seu valor atual, MySQL ignora a atualização para maior eficiência).

  • Você define explicitamente a uma coluna TIMESTAMP o valor NULL.

Outras colunas TIMESTAMP, além da primeira podem ser definidas com a data e hora atuais. Basta defini-las com NULL ou NOW()

Você pode definir colunas TIMESTAMP com um valor diferente da data e hora atuais colocando explicitamente o valor desejado. Isto é verdade mesmo para a primeira coluna TIMESTAMP. Você pode usar esta propriedade se, por exemplo, você quiser que um TIMESTAMP tenha seu valor definido como a data e hora atuais na criação de registros, mas não quer alterá-los quando o registro for atualizado mais tarde:

  • Deixe o MySQL definir a coluna quando o registro é criado. Isto irá inicializa-la com a data e hora atuais.

  • Quando você realizar subsequentes atualizações em outras colunas do registro, defina explicitamente a coluna TIMESTAMP com o valor atual.

Por outro lado, você pode achar que é mais fácil usar uma coluan DATETIME que você inicializa com NOW() quando o registro for criado e deixa como está em atualizações subsequentes.

Propriedades TIMESTAMP quando executando no modo MAXDB

Quando o MySQL está executando no modo MAXDB, TIMESTAMP é idêntico ao DATETIME. Ele usa o mesmo formato para armazenar e mostrar valores, e ele tem a mesma faixa. O MySQL pode ser executado no modo MAXDB a partir da versão 4.1.1. Veja mais informações sobre isto na Seção 4.1.1, “Opções de Linha de Comando do mysqld.

Propriedades TIMESTAMP a partir do MySQL 4.1 quando não executado no modo MAXDB

No MySQL 4.1.0, colunas TIMESTAMP são armazenadas e mostradas no mesmo formato que colunas DATETIME. Isto também significa que ele não podem ser estreitados ou alargados nos modos descritos no parágrafo seguinte. Em outras palavras, você não pode usar TIMESTAMP(2), TIMESTAMP(4), etc. Em outros casos, as propriedades são as mesmas de versões MySQL anteriores.

Propriedades TIMESTAMP antes do MySQL 4.1

Valores TIMESTAMP podem ter valores do incio de 1970 até algum momento do ano 2037, com a resolução de um segundo. Valores são mostrados como números

O formato no qual o MySQL recupera e mostra valores TIMESTAMP depende do tamanho do display, como ilustrado pela tabela que se segue: O formato `cheio' TIMESTAMP é de 14 digitos, mas colunas TIMESTAMP podem ser criadas com tamanho de display menores:

Tipo da ColunaFormato do Display
TIMESTAMP(14)YYYYMMDDHHMMSS
TIMESTAMP(12)YYMMDDHHMMSS
TIMESTAMP(10)YYMMDDHHMM
TIMESTAMP(8)YYYYMMDD
TIMESTAMP(6)YYMMDD
TIMESTAMP(4)YYMM
TIMESTAMP(2)YY

Todas as colunas TIMESTAMP tem o mesmo tamanho de armazenamento, independente do tamanho de display. Os tamanhos de display mais comuns são 6, 8, 12, e 14. Você pode especificar um tamanho de display arbitrario na hora da criação da tabela, mas valores de 0 ou maiores que 14 são mudados para 14. Valores ímpares de tamanho na faixa de 1 a 13 são mudados para o maior número par mais próximo.

Nota: Na versão 4.1, TIMESTAMP é retornado com uma string com o formato 'YYYY-MM-DD HH:MM:SS', e timestamp de diferentes tamamnhos não são mais suportados.

Você pode especificar calores DATETIME, DATE e TIMESTAMP usando qualquer conjunto de formatos comum:

  • Como uma string nos formatos 'YYYY-MM-DD HH:MM:SS' ou 'YY-MM-DD HH:MM:SS'. Uma sintaxe ``relaxada'' é permitida---nenhum caracter de pontuação pode ser usado como um delimitador entre parte de data ou hora. Por exemplo, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', e '98@12@31 11^30^45' são equivalentes.

  • Como uma string nos formatos 'YYYY-MM-DD' ou 'YY-MM-DD'. Uma sintaxe ``relaxada'' é permitida aqui também. Por exemplo, '98-12-31', '98.12.31', '98/12/31', e '98@12@31' são equivalentes.

  • Como uma string sem delimitadores nos formatos 'YYYYMMDDHHMMSS' ou 'YYMMDDHHMMSS', desde que a string faça sentido como data. Por example, '19970523091528' e '970523091528' são interpretadas com '1997-05-23 09:15:28', mas '971122129015' é ilegal (tem uma parte de minutos sem sentido) e se torna '0000-00-00 00:00:00'.

  • Como uma string sem delimitadores nos formatos 'YYYYMMDD' ou 'YYMMDD', desde que a string tenha sentido com data. Por exemplo, '19970523' e '970523' são interpretedas como '1997-05-23', mas '971332' é ilegal (tem uma parte de mês sem sentido) e se torna '0000-00-00'.

  • Como um número nos formatos YYYYMMDDHHMMSS ou YYMMDDHHMMSS, desde que o número faça sentido como uma data. Por exemplo, 19830905132800 e 830905132800 são interpretedos como '1983-09-05 13:28:00'.

  • Como um número nos formatos YYYYMMDD ou YYMMDD, desde que o número faça sentido como data. Por exemplo, 19830905 e 830905 são interpretedos como '1983-09-05'.

  • Como o resultado de uma função que retorne uma valor aceitavel em um contexto DATETIME, DATE ou TIMESTAMP, tal como NOW() ou CURRENT_DATE.

Valores DATETIME, DATE, ou TIMESTAMP ilegais são convertidos para o valor ``zero'' do tipo apropriado ('0000-00-00 00:00:00', '0000-00-00', ou 00000000000000).

Para valores especificados com strings que incluem delimitadores de data, não é necessário especificar dois digitos para valores de mês ou dia qua são menores que 10. '1979-6-9' é o mesmo que '1979-06-09'. Similarmente, para valores especificados como strings que incluem delimitadores de hora, não é necessário especificar dois digitos para valores de hora, minutos ou segundo que são menores que 10. '1979-10-30 1:2:3' Ré o mesmo que '1979-10-30 01:02:03'.

Valores especificados como números devem ter 6, 8, 12, ou 14 digitos. Se o número é de 8 ou 14 digitos, ele assume estar no formato YYYYMMDD ou YYYYMMDDHHMMSS e que o ano é dado pelos 4 primeiros dígitos. Se o é de 6 ou 12 dígitos, ele assume estar no formato YYMMDD or YYMMDDHHMMSS e que o ano é dado pelos 2 primeiros digitos. Números que não possua estes tamanho são interpretados como calores preenchidos com zero até o tamanho mais próximo.

Valores especificados como strings não delimitadas são interpretados usando o seu tamanho como dado. Se a string possui 8 ou 14 caracteres, o ano é assumido como os 4 primeiros caracteres. De outra forma o assume-se que o ano são os 2 primeiros caracteres. A string é interpretadada esquerda para direita para encontrar os valores do ano, mês, dia, hora, minute e segundo, para as partes da string. Isto significa que você não deve utilizar strings com menos de 6 caracteres. Por exemplo, se você especificar '9903', pensando em representar Março de 1999, você perceberá que o MySQL insere uma data ``zero'' em sua tabela. Isto ocorre porque os valores do ano e mês são 99 e 03, mas a parte contendo o dia não existe (zero), então o valor não é uma data legal. No entanto, a partir do MySQL 3.23, você pode especificar explicitamente um valor de zero para representar dia ou mês faltantes. Por exemplo, você pode usar '990300' para inserir o valor '1999-03-00'.

Colunas TIMESTAMP armazena valores legais utilizando precisão total com a qual os valores foram especificados, independente do tamanho do display. Isto tem diversas implicações:

  • Sempre especifique o ano, mês e dia, mesmo se seus tipos de coluna são TIMESTAMP(4) ou TIMESTAMP(2). De outra forma, os valores não serão datas legais date e um 0 será armazenado.

  • Se você usa ALTER TABLE para aumentar uma coluna TIMESTAMP, informações serão mostradas como se antes estivessem ``escondidas''.

  • De forma similar, reduzindo o tamanho de uma coluna TIMESTAMP não causa perda de informação, exceto no sentido de que menos informação aparece quando os valores são mostrados.

  • Embora os valores TIMESTAMP sejam armazenados com precisão total, a única função que opera diretamente com o valor armazenado é UNIX_TIMESTAMP(). OUtras funções operam com o formato do valor recuperado Isto significa que não se pode usar funções como HOUR() or SECOND() a menos que a parte relevante do valor TIMESTAMP esteja incluído no valor formatado. POr exemplo, a parte HH de uma coluna TIMESTAMP não é mostrada a menos que o tamanho do display seja de pelo menos 10, logo tentar usar HOUR() em um valor TIMESTAMP menor produz um resultado sem significado.

Você pode, algumas vezes, atribuir valores de um tipo de data para um objeto de um diferente tipo de data. No entanto pode haver algumas alterações de valores ou perda de informação

  • Se você atribuir um valor de DATE value a um objeto DATETIME ou TIMESTAMP, a parte da hora do valor resultante é definido como '00:00:00', porque o vlaor DATE não contém informações de hora.

  • Se você atribuir um valor DATETIME ou TIMESTAMP para um objeto DATE, a parte da hora do valor resultante é deletado, pois o tipo DATE não armazena informações de hora.

  • Lembre-se de que embora todos os valores DATETIME, DATE, e TIMESTAMP possam ser especificados usando o mesmo conjunto de formatos, os tipos não tem a mesa faixa de valores. Por exemplo, valores TIMESTAMP não podem ser anteriores a 1970 ou posteriores a 2037. Isto significia que datas como '1968-01-01', são permitidas como valores DATETIME ou DATE, mas não são válidas para valores TIMESTAMP e serão covertidas para 0 se atribuidas para tais objetos.

Esteja ciente de certas dificuldades quando especificar valores de data:

  • A forma ``relaxada'' permitida em valores especificados com strings podem causar certas confusões. Por exemplo, um valor como '10:11:12' pode parecer com um valor de hora devido ao limitador ‘:’, mas se usado em um contexto de data será interpretado como o ano '2010-11-12'. O valor '10:45:15' será convertido para '0000-00-00' pois '45' não é um valor de mês permitido.

  • O servidor MySQL funciona basicamente checando a validade da data: dias entre 00-31, mês entre 00-12, anos entre 1000-9999. Qualquer data que não esteja nesta faixa será revetida para 0000-00-00. Por favor, note que isto ainda lhe permite armazenar datas invalidas tais como 2002-04-31. Isto permite a aplicações web armazenar dados de um formulário sem verificações adicionais. Para assegurar que a data é valida, faça a checagem em sua aplicação.

  • Valores de anos especificados com 2 digitos são ambíguos, pois o século não é conhecido. MySQL interpreta valores de anos com dois digitos usando as seguintes regras:

    • Valores de ano na faixa de 00-69 são convertidos para 2000-2069.

    • Valores de ano na faixa de 70-99 são convertidos para 1970-1999.

6.2.2.3. O Tipo TIME

O MySQL recupera e mostra valores TIME no formato 'HH:MM:SS' (ou no formato 'HHH:MM:SS' para valores grandes). Volares TIME podem estar na faixa de '-838:59:59' até '838:59:59'. A razão para a parte da hora ser tão grande é que o tipo TIME pode ser usado não apenas para representar a hora do dia (que deve ser menor que 24 horas), mas também para tempo restante ou intervalos de tempo entre dois eventos(que podem ser maior que 24 horas ou mesmo negativo).

Você pode especificar valores TIME de variadas formas:

  • Como uma string no formato 'D HH:MM:SS.fração' . (Note que o MySQL não armazena ainda frações para a coluna time.) Pode-se também utilizar uma das seguintes sintaxes ``relaxadas'':

    HH:MM:SS.fração, HH:MM:SS, HH:MM, D HH:MM:SS, D HH:MM, D HH ou SS. Aqui D é um dia entre 0-33.

  • Como uma string sem delimitadores no formato 'HHMMSS', desde que ela tenha sentido como uma hora. Por exemplo, '101112' é esntendido como '10:11:12', mas '109712' é ilegal (a parte dos minutos não tem nenhum sentido) e se torna '00:00:00'.

  • Como um número no formato HHMMSS, desde que tenha sentido como uma hora. Por exemplo, 101112 é entendido com '10:11:12'. Os formatos alternativos seguintes também são entendidos: SS, MMSS, HHMMSS e HHMMSS.fração. Note que o MySQL ainda não armazena frações.

  • Como o resultado de uma função que retorne um valor que é aceitável em um contexto do tipo TIME, tal como CURRENT_TIME.

Para valores TIME especificados como uma string que incluem delimitadores de hora, não é necessário especificar dois dígitos para valores de hora, minutos ou segundos que sejam menores que 10. '8:3:2' é o mesmo que '08:03:02'.

Seja cuidadoso ao atribuir valores TIME ``pequenos'' para uma coluna TIME. Sem dois pontos, o MySQL interprete valores assumindo que os digitos mais a direita representam segundos. (MySQL interpreta valores TIME como tempo decorrido ao invés de hora do dia.) Por exemplo, você poderia pensar em '1112' e 1112 significam '11:12:00' (11 horas e 12 minutos), mas o MySQL o intepreta como '00:11:12' (onze minutos e 12 segundos). De forma similar, '12' e 12 são interpretados como '00:00:12'. Valores TIME com dois pontos, em contrapartida, são tratados como hora do dia. Isto é, '11:12' significará '11:12:00', não '00:11:12'.

Valores que são legais mas que estão fora da faixa permitidas são ajustados para o valor limita da faixa mais apropriado. Por exemplo, '-850:00:00' e '850:00:00' são convertidos para '-838:59:59' e '838:59:59', respectivmente.

Valores TIME ilegais são convertidos para '00:00:00'. Note que como '00:00:00' é um valor TIME, não temos com dizer, a partir de um valor '00:00:00' armazenado na tabela, se o valor original armazenado foi especificado como '00:00:00' ou se foi ilegal.

6.2.2.4. O Tipo YEAR

O tipo YEAR é um tipo de 1 byte usado para representar anos.

O MySQL recupera e mostra valores YEAR no formato YYYY. A faixa de valores é de 1901 até 2155.

Você pode especificar valores YEAR em uma variedade de formatos:

  • Como uma string de 4 digitos na faixa de '1901' até '2155'.

  • Como um número de 4 dígitos na faixa de 1901 até 2155.

  • Como uma string de dis dígitos na faixa '00' até '99'. Valores na faixa de '00' até '69' e '70' até '99' são convetidas para valores YEAR na faixa de 2000 até 2069 e 1970 até 1999.

  • Como um número de 2 digitos na faixa de 1 até 99. Valores na faixa de 1 até 69 e 70 até 99 são convertidos para valores YEAR na faixa de 2001 até 2069 e 1970 até 1999. Note que a faixa para números de dois dígitos é um pouco diferente da faixa de strings de dois dígitos, pois não se pode especificar zero diretamente como um número e tê-lo interpretado com 2000. Você deve especificá-lo como uma string '0' ou '00' ou ele será interpretado com 0000.

  • Como o resultado de uma função que retorna um valor que é aceitável em um contexto do tipo YEAR, tal como NOW().

Valores YEAR ilegais são convertidos para 0000.

6.2.3. Tipos String

Os tipos strings são CHAR, VARCHAR, BLOB, TEXT, ENUM, e SET. Esta seção descreve como este tipos funcionam, suas exigências de armazenamento e como usá-los em suas consultas.

TipoTam.maxímoBytes
TINYTEXT ou TINYBLOB2^8-1255
TEXT ou BLOB2^16-1 (64K-1)65535
MEDIUMTEXT ou MEDIUMBLOB2^24-1 (16M-1)16777215
LONGBLOB2^32-1 (4G-1)4294967295

6.2.3.1. Os Tipos CHAR e VARCHAR

Os tipos CHAR e VARCHAR são parecidos, mas diferem no modo como são armazenados e recuperados.

O tamanho de um campo CHAR é fixado pelo tamanho declarado na criação da tabela. O tamanho pode ser qualquer valor entre 1 e 255 (Como na versão 3.23 do MySQL, o tamanho pode ser de 0 a 255). Quando valores CHAR são armazenados, eles são preenchidos a direita com espaços até o tamanho especificado. Quando valores CHAR são recuperados, espaços extras são removidos.

Valores no campo VARCHAR são strings de tamanho variável. Você pode declarar um campo VARCHAR para ter qualquer tamanho entre 1 e 255, assim como para campo CHAR. No entanto, diferente de CHAR, valores VARCHAR são armazendos usando apenas quantos caracteres forem necessários, mais 1 byte para gravar o tamanho. Valores não são preenchidos; ao contrário, espaços extras são removidos quando valores são armazenados. (Esta remoção de espaços difere das especificações do SQL-99). Nenhum caso de conversão é feito durante um o armazenamento ou recuperação.

Se você atribuir um valor para uma coluna CHAR ou VARCHAR que exceda o tamanho máximo da coluna, o valor é truncado para este tamanho.

A seguinte tabela ilustra as diferenças entre os dois tipos de colunas, mostrando o resultado de se armazenar vários valores de strings em campos CHAR(4) e VARCHAR(4):

ValorCHAR(4)Exigência p/ armazenamentoVARCHAR(4)Exigência p/ armazenamento
'''    '4 bytes''1 byte
'ab''ab  '4 bytes'ab'3 bytes
'abcd''abcd'4 bytes'abcd'5 bytes
'abcdefgh''abcd'4 bytes'abcd'5 bytes

Os valores recuperados para as colunas CHAR(4) e VARCHAR(4) serão os mesmos em cada caso, já que espaços ectras são removidos das colunas CHAR quando recuperados.

Valores nas colunas CHAR e VARCHAR são ordenados e comparadaos no modo caso-insensitivo, a menos que o atributo BINARY seja especificado quando a tabela for criada. O atributo BINARY significa que os valores das colunas são ordenados e comparados no modo caso-sensitivo de acordo com a ordem ASCII da maquina onde o servidor MySQL está sesndo executado. BINARY não afeta como as colunas são armazenadas e recuperadas.

A partir da versão 4.1.0, o tipo de coluna CHAR BYTE é um apelido para CHAR BINARY. Thite é um recurso para compatibilidade.

O atributo BINARY é pegajoso. Isto significa que se uma coluna definida com BINARY é usada na expressão, toda a expressão é comparada como um valor BINARY.

MySQL pode alterar sem aviso o tipo de uma coluna CHAR ou VARCHAR na hora de criar a tabela. Veja mais informações sobre isto na Seção 6.5.3.1, “Alteração de Especificações de Colunas”.

6.2.3.2. Os Tipos BLOB e TEXT

Um BLOB é um objeto binario grande que pode guardar um montante variado de dados. Os quatro tipos BLOB: TINYBLOB, BLOB, MEDIUMBLOB, e LONGBLOB diferem apenas no tamanho maximo dos valores que eles podem guradar. Veja mais informações sobre isto na Seção 6.2.6, “Exigências de Armazenamento dos Tipos de Coluna”.

Os quatro tipos TEXT: TINYTEXT, TEXT, MEDIUMTEXT, e LONGTEXT correspondem aos quatro tipos BLOB e têm o mesmo tamanho máximo e necessidade de tamanho para armazenamento. A única diferença entre os tipos BLOB e TEXT é que ordenação e comparação são realizadas no modo caso-sensitivo para valores BLOB e no modo caso-insensitivo para valores TEXT. Em outras palavras, um TEXT é um BLOB no modo caso-insensitivo. Nenhum caso de conversão é feito durante um o armazenamento ou recuperação.

Se você atribuir um valor a uma coluna BLOB ou TEXT que exceda o tamanho máximo do tipo da coluna, o valor é truncado para servir ao campo.

Em muitos casos, podemos considerar um campo TEXT como um campo VARCHAR que pode ser tão grande quando desejamos. Da mesma forma podemos considerar um campo BLOB como um campo VARCHAR BINARY. As diferenças são:

  • Você pode ter indices em um campo BLOB e TEXT no MySQL Versão 3.23.2 e mais novas. Versões antigas do MySQL não suportam isto.

  • Não há remoção de espaços extras para campos BLOB e TEXT quando os valores são armazenados, como há em campos VARCHAR.

  • Colunas BLOB e TEXT não podem ter valores padrões.

A partir da versão 4.1.0, LONG e LONG VARCHAR mapeiam para o tipo de dados MEDIUMTEXT. Este é um recurso de compatibilidade.

MyODBC define valores BLOB como LONGVARBINARY e valores TEXT como LONGVARCHAR.

Como valores BLOB e TEXT podem ser extremamentes longos, você pode deparar com alguns problemas quando utilizá-los:

  • Se você quiser utilizar GROUP BY ou ORDER BY em um campo BLOB ou TEXT, você deve converte-los em objetos de tamanho fixo. O modo padrão de se fazer isto é com a função SUBSTRING. Por exemplo:

    mysql> SELECT comentario FROM nome_tabela,SUBSTRING(comentario,20) AS substr
    -> ORDER BY substr;
    

    Se você não fizer isto, só os primeiros max_sort_length bytes de uma coluna serão utilizados na ordenação. O valor padrão de max_sort_length é 1024; este calor pode ser alterado utilizando-se a opção -O quando o servidor é inicializado. Você pode agrupar uma expressão envolvendo valores BLOB ou TEXT especificando a posição da coluna ou utilizando apelidos (alias):

    mysql> SELECT id,SUBSTRING(col_blob,1,100) FROM nome_tabela GROUP BY 2;
    mysql> SELECT id,SUBSTRING(col_blob,1,100) AS b FROM nome_tabela GROUP BY b;
    
  • O tamanho máximo de uma objeto BLOB ou TEXTé determinado pelo seu tipo, mas o maior valor que você pode, atualmente, transmitir entre o cliente e o servidor é determinado pela quantidade de memória disponível e o tamanho dos buffers de comunicação. Você pode mudar o tamanho do buffer de mensagem (max_allowed_packet), mas você deve faze-lo no servidor e no cliente. Veja mais informações sobre isto na Seção 5.5.2, “Parâmetros de Sintonia do Servidor”.

Note que cada valor BLOB ou TEXT é representado internamente por um objeto alocado searadamente. Está é uma diferença com todos os outros tipos de colunas, para o qual o armazenamento é alocado um por coluna quando a tabela é aberta.

6.2.3.3. O Tipo ENUM

Um ENUM é um objeto string cujo valor normalmente é escolhido de uma lista de valores permitidos que são enumerados explicitamente na especificação da coluna na criação da tabela.

O valor pode ser a string vazia ("") ou NULL sob certas circunstâncias:

  • Se você inserir um valor inválido em um ENUM (isto é, uma string que não está presente na lista de valores permitidos), a string vazia é inserida no lugar como um valor especial de erro. Esta string pode se diferenciar de um string vazia 'norma' pelo fato de que esta string tem uo valor numérico 0. Veremos mais sobre este assunto mais tarde.

  • Se um ENUM é declarado NULL, NULL é também um valor permitido para a coluna, e o valor padrao é NULL. Se um ENUM é decalarado NOT NULL, o valor padrão é o primeiro elemento da lista de valores permitidos.

Cada enumeração tem um índice:

  • Valores da lista de elementos permitidos na especificação da coluna são números começados com 1.

  • O valor de índice de uma string vazia que indique erro é 0. Isto significa que você pode usar a seguinte instrução SELECT para encontrar linhas nas quais valores ENUM inválidos forma atribuidos:

    mysql> SELECT * FROM nome_tabela WHERE col_enum=0;
    
  • O índice de um valor NULL é NULL.

Por exemplo, uma coluna especificada como ENUM("um", "dois", "três") pode ter quqlquer um dos valores mostrados aqui. O índice de cada valor também é mostrado:

ValorIndice
NULLNULL
""0
"um"1
"dois"2
"três"3

Uma enumeração pode ter um máximo de 65535 elementos.

A partir da versão 3.23.51 espaços extras são automaticamente deletados dos valores ENUM quando a tabela é criada.

O caso da letra é irrelevante quando você atribui valores a um coluna ENUM. No entanto, valores recuperados posteriormente da coluna terá o caso de letras de acordo com os valores que foram usados para especificar os valores permitidos na criação da tabela.

Se você recupera um ENUM em um contexto numérico, o indice do valor da coluna é retornado. Por exemplo, você pode recuperar valores numéricos de uma coluna ENUM desta forma:

mysql> SELECT col_enum+0 FROM nome_tabela;

Se você armazena um número em um ENUM, o número é tratado como um índice, e o valor armazenado é o membro da enumeração com este índice. (No entanto, este não irá funcionar com LOAD DATA, o qual trata todas as entradas como strings.) Não é aconselhável armazenar números em uma string ENUM pois pode tornar as coisas um pouco confusas.

Valores ENUM são armazenados de acordo com a ordem na qual os membros da enumeração foram listados na especificação da coluna. (Em outras palavras, valores ENUM são ordenados de acordo com o seus números de índice.) Por exemplo, "a" vem antes de "b" para ENUM("a", "b"), mas "b" vem antes de "a" para ENUM("b", "a"). A string vazia vem antes de strings não-vazias, e valores NULL vem antes de todos os outros valores de enumeração. Para evitar resultados inesperados, especifique a lista ENUM em ordem alfabética. Você também pode usar GROUP BY CONCAT(col) para ter certeza de que as colunas estão ordenadas alfabeticamente e não pelo índice numérico.

Se você quiser obter todos os valores possíveis para uma coluna ENUM, você deve usar: SHOW COLUMNS FROM nome_tabela LIKE nome_coluna_enum e analizar a definição de ENUM na segunda coluna.

6.2.3.4. O Tipo SET

Um SET é um objeto string que pode ter zero ou mais valores, cada um deve ser escolhido de uma lista de valores permitidos especificados quando a tabela é criada. Valores de colunas SET que consistem de múltiplos membros são espeficados separados por virgula (‘,’). Uma consquência distop é que valores dos membros de SET não podem, eles mesmos, conter vírgula.

Por exemplo, uma coluna especificada como SET("um", "dois") NOT NULL pode ter qualquer um destes valores:

""
"um"
"dois"
"um, dois"

Um SET pode ter no máximo 64 membros diferentes.

A partir da versão 3.23.51, espaços extras são automaticamente removidos dos valores de SET quando a tabela é criada.

MySQL armazena valores SET numericamente, com o bit de baixa-ordem do valor armazenado correspondendo ao primeiro membro do conjunto. Se você recupera um valor SET em um contexto numérico, o valor recuperado tem o conjunto de bits correspondente aos membros que aparecem no valor da coluna. Por exemplo, você pode recuperar valores numéricos de uma coluna SET assim:

mysql> SELECT col_set+0 FROM nome_tabela;

Se um número é armazenado em uma coluna SET, os bits que estão habilitados (com 1) na representação binária do número determinam o qual o membro no valor da coluna. Suponha uma coluna especificada como SET("a","b","c","d"). Então os membros terão os seguintes valores binários:

SET membroValor decimalValor binário
a10001
b20010
c40100
d81000

Se você atribuir um valor 9 a esta coluna, que é 1001 em binário, o primeiro e o quarto valores membros do SET "a" e "d" são selecionados e o valor resultante é "a,d".

Para um valor contendo mais que um elemento de SET, não importa em qual ordem os elementos são listados quando foram inseridos seus valores. Também não importa quantas vezes um dado elemento e listado no valor. Quando o valor é recuperado posteriormente, cada elemento aparecerá uma vez, listados de acordo com a ordem em que eles foram especificados na crição da tabela. Por exemplo, se uma coluna é especificada como SET("a","b","c","d"), então "a,d", "d,a" e "d,a,a,d,d" irão todos aparecer como "a,d" quando recuperados.

Se você define um valor que não é suportado pela coluna SET, o valor será ignorado.

Valores SET são ordenados numéricamente. Valores NULL vêm antes de valores SET não NULL.

Normalmente, você realiza um SELECT em uma coluna SET usando o operador LIKE ou a função FIND_IN_SET():

mysql> SELECT * FROM nome_tabela WHERE col_set LIKE '%valor%';
mysql> SELECT * FROM nome_tabela WHERE FIND_IN_SET('valor',col_set)>0;

Mas o seguinte também funciona:

mysql> SELECT * FROM nome_tabela 2 WHERE col_set = 'val1,val2';
mysql> SELECT * FROM nome_tabela 3 WHERE col_set & 1;

A primeira desta instruções procura por uma correpondencia exata. A segunda por valores contendo o primeiro membro.

Se você quer obter todos os valores possíveis para uma coluna SET, você deve usar: SHOW COLUMNS FROM nome_tabela LIKE nome_coluna_set e analizar a definição do SET na segunda coluna.

6.2.4. Escolhendo o Tipo Correto para uma Coluna

Para um uso mais eficiente do armzenamento, tente usar o tipo mais adequado em todos os casos. Por exemplo, se um campo de inteiro for usado para valores em uma faixa entre 1 e 99999, MEDIUMINT UNSIGNED é o melhor tipo.

Represtação precisa de valores monetários é um priblema comum. No MySQL você deve usar o tipo DECIMAL. Ele armazena uma string, então nenhuma perda de precisão deve ocorrer. Se a precisão não é tão importante, o tipo DOUBLE pode ser satisfatório.

Para uma alta precisão você sempre pode converter para um tipo de ponto fixo armazenado em um BIGINT. Isto perite fazer todos os cálculos com inteiros e converter o resultado para um ponto flutuante somente quando necessário.

6.2.5. Usando Tipos de Colunas de Outros Mecanismos de Banco de Dados

Para facilitar o uso de code para implementações SQL de outras empresas, MySQL mapeia os tipos de campos como mostrado na tabela seguinte. Este mapeamento torna fácil mudar definições de tabelas de outros mecanismos de banco de dados para o MySQL:

Tipo de outras empresasTipo MySQL
BINARY(NUM)CHAR(NUM) BINARY
CHAR VARYING(NUM)VARCHAR(NUM)
FLOAT4FLOAT
FLOAT8DOUBLE
INT1TINYINT
INT2SMALLINT
INT3MEDIUMINT
INT4INT
INT8BIGINT
LONG VARBINARYMEDIUMBLOB
LONG VARCHARMEDIUMTEXT
MIDDLEINTMEDIUMINT
VARBINARY(NUM)VARCHAR(NUM) BINARY

O mapeamento do tipo de campo ocorre na criação da tabela. Se você cria uma tabela com tipos usador por outras empresas e então executa uma instrução DESCRIBE nome_tabela, MySQL relaciona a estrutura de tabela utilizando os tipos equivalentes do MySQL.

6.2.6. Exigências de Armazenamento dos Tipos de Coluna

As exigências de armazenamento para cada um dos tipos de colunas suportados pelo MySQL estão listados por categoria.

Exigências de armazenamento para tipos numéricos

Tipo da colunaTamanho exigido
TINYINT1 byte
SMALLINT2 bytes
MEDIUMINT3 bytes
INT4 bytes
INTEGER4 bytes
BIGINT8 bytes
FLOAT(X)4 se X <= 24 ou 8 se 25 <= X <= 53
FLOAT4 bytes
DOUBLE8 bytes
DOUBLE PRECISION8 bytes
REAL8 bytes
DECIMAL(M,D)M+2 bytes se D > 0, M+1 bytes se D = 0 (D+2, se M < D)
NUMERIC(M,D)M+2 bytes se D > 0, M+1 bytes se D = 0 (D+2, se M < D)

Exigência de armazenamento para tipos data e hora

Tipo de colunaTamanho exigido
DATE3 bytes
DATETIME8 bytes
TIMESTAMP4 bytes
TIME3 bytes
YEAR1 byte

Exigência de armazenamento para tipos string

Tipo de colunaTamanho exigido
CHAR(M)M bytes, 1 <= M <= 255
VARCHAR(M)L+1 bytes, onde L <= M e 1 <= M <= 255
TINYBLOB, TINYTEXTL+1 bytes, onde L < 2^8
BLOB, TEXTL+2 bytes, onde L < 2^16
MEDIUMBLOB, MEDIUMTEXTL+3 bytes, onde L < 2^24
LONGBLOB, LONGTEXTL+4 bytes, onde L < 2^32
ENUM('valor1','valor2',...)1 ou 2 bytes, dependendo do número de valores enumerados (65535 valores no máximo)
SET('valor1','valor2',...)1, 2, 3, 4 or 8 bytes, dependendo do número de membros do conjunto (64 membros no máximo)

Tipos VARCHAR, BLOB e TEXT são de tamanho variáveis, tendo o tamanho exigido para armazenamento dependendo do tamanho atual dos valores da coluna (representado por L na tabela anterior), e não do tamanho máximo do tipo. Por exemplo, uma coluna VARCHAR(10) pode guardar uma string com um tamanho máximo de 10 caracteres. O tamanho exigido para armazenamento atual é o tamanho da string (L), mais 1 byte para para gravar o tamanho da string. Por exemplo, para a string 'abcd', L é 4 e o tamanho exigido para armazenamento é 5 bytes.

Os tipos BLOB e TEXT exigem 1, 2, 3 ou 4 bytes para gravar o tamanho do valor da coluna, dependendo do tamanho máximo possível do tipo. Veja mais informações sobre isto na Seção 6.2.3.2, “Os Tipos BLOB e TEXT.

Se uma tabela inclui qualquer tipo de coluna de tamanho variável, o formato do registro também será de tamanho variável. Note que quando uma tabela é criada, MySQL pode, sob certas condições, mudar uma coluna de um tipo de tamanho variável para um tipo de tamanho fixo, ou vice-versa. Veja mais informações sobre isto na Seção 6.5.3.1, “Alteração de Especificações de Colunas”.

O tamanho de um objeto ENUM é determinado por um número de diferntes valores enumerados. Um byte é usado para enumerações até 255 valores possíveis. Dois bytes são usados para enumerações até 65535 valores. Veja mais informações sobre isto na Seção 6.2.3.3, “O Tipo ENUM.

O tamanho de uma objeto é determinado pelo número de diferentes membros do conjunto. Se o tamanho do conjunto é N, o objeto ocupa (N+7)/8 bytes, arredondados acima para 1, 2, 3, 4, ou 8 bytes. Um SET pode ter no máximo 64 membros. Veja mais informações sobre isto na Seção 6.2.3.4, “O Tipo SET.

O tamanho máximo de um registro em uma tabela MyISAM é 65534 bytes. Cada coluna BLOB e TEXT ocupa apenas 5-9 bytes deste tamanho.