Tipos de Campos no MySQL, saiba como escolher o tipo correto
Este post tem o objetivo de ajudar o desenvolvedor na escolha adequada do tipo de campo que deverá utilizar no banco MySQL para persistir seus dados.
O desenvolvedor inexperiente costuma confundir bastante os tipos de campo da linguagem utilizada (PHP por exemplo) com os tipos que o banco pode armazenar. Um exemplo clássico dessa confusão é o booleano que é automaticamente convertido de true para 1 e false para 0 (zero). Além disso, há o clássico erro de armazenamento de CPF em campo numérico o que faz com que todos os zeros a esquerda se percam.
Portanto coloco abaixo a tabela com os campos do MySQL para que sirva de um guia de implementação do banco de dados de sua aplicação.
Lembro ainda que antes de mais nada você deve identificar qual o tipo de variável que irá armazenar: número, texto, binário, data, lista ou combinação destes. Em seguida estimar o tamanho do campo e se será variável ou fixo. O MySQL costuma preencher campos texto não variáveis com espaços em branco. Em seguida, verificar se permitirá nulo ou não, se é uma chave candidata, primária, estrangeira, índice ou único.
Não há uma regra fixa para saber como os bancos deverão ser implementados, mas tenha sempre em mente que neste caso, mais é melhor. Ou seja, é melhor sobrar espaço do que ter uma informação inserida com pedaços faltando. Já viu alguém nascer no “Rio de Jane” pois o desenvolvedor não colocou o campo com o tamanho correto?!
Observações:
- O tamanho do campo para tipos numéricos não afeta o intervalo de valores que pode ser armazenado na coluna. Colunas definidas como TINYINT (1) ou TINYINT (20) podem armazenar exatamente, os mesmos valores. Em vez disso, para inteiros, o tamanho determina a largura do campo, para casas decimais, o tamanho é o número total de dígitos que pode ser armazenado.
- Muitos dos tipos de dados têm nomes sinônimos: INT e INTEGER, DEC e DECIMAL, etc
- O tipo de campo TIMESTAMP é automaticamente definido com a data e hora quando um INSERT ou UPDATE ocorre, mesmo se nenhum valor for especificado para esse campo . Se uma tabela possui múltiplas colunas TIMESTAMP, somente a primeira será atualizada quando um INSERT ou UPDATE é realizado.
- MySQL também tem diversas variantes para os tipos de texto que permitem o armazenamento de dados binários. Estes tipos são BINARY, VARBINARY, TINYBLOB, MEDIUMBLOB, e LONGBLOB. Esses tipos são usados para armazenamento de arquivos ou dados criptografados.
| Tipos Numéricos | ||||
|---|---|---|---|---|
| Tipo | Uso | Tamanho | ||
| Atributo | MIN | MAX | ||
| TINYINT | Um inteiro muito pequeno | Signed: | -128 | 127 |
| Unsigned | 0 | 255 | ||
| SMALLINT | Um inteiro pequeno | Signed: | –32768 | 32767 |
| Unsigned | 0 | 65535 | ||
| MEDIUMINT | Um inteiro de tamanho mediano | Signed: | –8388608 | 8388607 |
| Unsigned | 0 | 16777215 | ||
| INT or INTEGER | Um inteiro de tamanho normal | Signed: | –2147483648 | 2147483647 |
| Unsigned | 0 | 4294967295 | ||
| BIGINT | Um inteiro de temanho grande | Signed: | –9223372036854775808 | 9223372036854775807 |
| Unsigned | 0 | 18446744073709551615 | ||
| FLOAT | Um pequeno número de ponto flutuante (precisão simples) | Signed | –3.402823466E+38 | –1.175494351E-38, 0 |
| 1.175494351E-38 | 3.402823466E+38 | |||
| Não pode ser unsigned | – | |||
| OBS | Se o número de decimais não for especificado ou for <= 24 será de precisão simples | |||
| DOUBLE, DOUBLE PRECISION, REAL |
Um número de ponto flutuante de tamanho normal (precisão dupla) | Signed | -1.7976931348623157E+308 | -2.2250738585072014E-308, 0 |
| 2.2250738585072014E-308 | 1.7976931348623157E+308 | |||
| Não pode ser unsigned | – | |||
| OBS | Se o número de decimais não for especificado ou for 25 <= Decimals <= 53 será de precisão dupla | |||
| DECIMAL, NUMERIC |
Um número de ponto flutuante descompactado . | Signed | Se comporta como um campo CHAR: “descompactado” significa que o número é armazenado como uma string, usando um caractere para cada dígito do valor. O ponto decimal e, para números negativos, o sinal ‘-‘ não é contado. Se o decimal for 0, os valores não terão ponto decimal ou parte fracionária. | O alcance máximo de valores decimais é o mesmo que para o DOUBLE, mas a faixa atual para um campo DECIMAL dado pode ser limitado pela escolha de comprimento e decimais. |
| Não pode ser unsigned | – | |||
| OBS | Se Decimais é deixado de fora ele é definido como 0. Se o comprimento é deixado de fora ele é definido como 10. Note que no MySQL 3,22 o comprimento inclui o sinal eo ponto decimal | |||
| Campos de Datas | ||||
| Formato | MIN | MAX | ||
| DATE | Data | ‘1000-01-01’ | ‘9999-12-31’ | |
| OBS | Formato: ‘YYYY-MM-DD’ | |||
| DATETIME | Data e horário | ‘1000-01-01 00:00:00’ | ‘9999-12-31 23:59:59’ | |
| OBS | Formato: ‘YYYY-MM-DD HH:MM:SS’ | |||
| TIMESTAMP | Timestamp | ‘1970-01-01 00:00:00’ | aproximadamente 2037 | |
| OBS | Formato: YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD ou YYMMDD, dependendo se M é 14 (ausente), 12, 8 ou 6, podendo ser strings ou números.
Este tipo é recomendável para instruções de INSERT ou UPDATE pois é automaticamente marcado com os valores da operação mais recente quando não informado. |
|||
| TIME | A time | ‘-838:59:59’ | ‘838:59:59’ | |
| OBS | formato: ‘HH:MM:SS’, podem ser strings ou números | |||
| YEAR | Anos com 2 ou 4 digitos. O padrão é 4 digitos | 4 digitos | 1901 | 2155 e 0000 |
| 2 digitos | 1970 | 2069 | ||
| OBS | Formato: YYYY podem ser strings ou números. |
|||
| Campos Texto | ||||
| MIN | MAX | |||
| CHAR | String de tamanho fixo. Sempre é completada com espaços a direita até o tamanho definido | 1 | 255 caracteres | |
| OBS | Espaços excessivos são removidos quando o valor é trazido.Os valores são ordenados e comparados ignorando caixas altas e baixas de acordo com a codificação padrão, a menos que seja fornecido uma chave binária. | |||
| VARCHAR | String de tamanho variável | 1 | 255 caracteres | |
| OBS | Os valores são ordenados e comparados ignorando caixas altas e baixas de acordo com a codificação padrão, a menos que seja fornecido uma chave binária.Nota: Espaços execessivos são removidos quando o valor é inserido. | |||
| TINYTEXT | 0 | 255 (2^8 – 1) caracteres | ||
| TEXT | 0 | 65535 (2^16 – 1) caracteres | ||
| MEDIUMTEXT | 0 | 16777215 (2^24 – 1) caracteres | ||
| LONGTEXT | 0 | 4294967295 (2^32 – 1) caracteres | ||
| Dados Binários | ||||
| TINYBLOB | 0 | 255 (2^8 – 1) caracteres | ||
| BLOB | 0 | 65535 (2^16 – 1) caracteres | ||
| MEDIUMBLOB | 0 | 16777215 (2^24 – 1) caracteres | ||
| LONGBLOB | 0 | 4294967295 (2^32 – 1) caracteres | ||
| Listas | ||||
| MIN | MAX | |||
| ENUM | Enumeração | String que pode conter apenas um valor ou zero | 65535 valores distintos. | |
| SET | Lista | String que pode conter zero ou mais valores | 64 itens | |
| fonte: http://help.scibit.com/Mascon/masconMySQL_Field_Types.html e http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html(tradução livre) | ||||
Nota:
Post extremamente bem escrito! Parabéns, Rogério. Continue este trabalho foda
cara boa noite tenho uma dúvida tenho uma tabela de um sistema que carrega fotos só que o cliente só que a o usuário carregue doze fotos vou postar o código v se me ajuda
Table structure for table `media`
—
CREATE TABLE IF NOT EXISTS `media` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`active` int(1) NOT NULL DEFAULT ‘0’,
`album_id` int(255) NOT NULL DEFAULT ‘0’,
`descr` text COLLATE utf8_unicode_ci NOT NULL,
`extension` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘none’,
`name` text COLLATE utf8_unicode_ci NOT NULL,
`post_id` int(255) NOT NULL,
`temp` int(1) NOT NULL DEFAULT ‘1’,
`timeline_id` int(255) NOT NULL,
`type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘none’,
`url` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
Por favor,
Estou estudando MySQL, antes meu meu banco estava em Access. Em Access tenho a possibilidade de criar um campos Data Agora().
Sempre que gera um registro a data (mesm do computador) é criada automaticamente. Um MySQL tenho essa possibilidade?
SIm utilize a função date() e coloque como valor no input do form que estiver passando(post)
Boa Tarde.
Olha so.
estou usando posgrees, e os atributos (N15,2) numerico com duas decimais, Na geracao da aplicacao na da erro, porem quando vou homologar da o seguinte erro :
error CS1061 decimal nao contem uma definicao para ToLower e nenhum metodo de extensao ToLower aceita que um primeiro argumento de tipo decimal seja encontrado voce nao esta usando uma diretriz ou referencia de assembly
Me Ajuda ai meu irmao…
Bom artigo, parabéns!
Considerações:
O tipo YEAR(2) deve ser evitado http://dev.mysql.com/doc/refman/5.5/en/migrating-to-year4.html
Para campos texto, mesmo que o tamanho do campo seja grande (maior que 16) é possível e recomendado limitar o tamanho das chaves dos índices. Esse tipo de limitação traz enorme ganho de performance. Índices com chaves maiores que 16 caracteres normalmente dever ser limitados. Uma maneira de fazer isso é ALTER TABLE nome_da_tabela DROP INDEX nome_indice, ADD INDEX(nome_indice(16));
Use e abuse do campo SET para tabelas como Estado, Cor etc. Normalmente a performance das buscas é maior e o espaço de armazenamento é menor com esse tipo de dado.
Valeu pelas dicas Airton. Toda contribuição sempre é bem-vinda! Abç.