Tipos de dados do MySQL 5

Apostila com artigo extraído do site Imasters com os tipos de dados do banco MySQL

1. Tipos de Dados do
MySQL 5.0
Por Wagner Bianchi

Neste série de artigos, abordaremos todos os tipos de dados suportados
pelo MySQL 5.0, além de ressaltar todos os problemas decorrentes de
“inserções erradas” – estas que levam a perda de dados importantes no fluxo
geral de uma aplicação.
Saber o que é um tipo de dado e suas propriedades é essencial para que se
tenha máximo proveito da informação a ser armazenada no banco de dados,
saber como definir os tipos em colunas de tabelas de sue banco de dados é
algo que decidirá o futuro de sucesso ou insucesso de sua aplicação. Assuntos
e dicas serão dadas no decorrer das explicações e todos os estudos aqui
apresentados foram desenvolvidos a partir da matéria que é cobrada na exame
de certificação do MySQL 5.0 CMDEV I (Certified MySQL Developer I).
sw
Após ler esta primeira parte do estudo, você estará apto à:
 Definir o que são tipos de dados e sua importância no armazenamento da
informação;
 Quais são os tipos de dados suportados pelo MySQL 5.0;
 Trabalhar com todos os tipos de dados numéricos, além de também
entender e trabalhar com suas propriedades;
 Quantificar quanto espaço necessita cada valor de um determinado tipos de
dados;
 Idetificar “inserções erradas” – defendida por alguns autores como
“inserções anômalas”;
Tipos de dados são artifícios comuns em bancos de dados relacionais para
tipificar conjuntos de possíveis valores definidos em uma coluna. Desde o
momento em que você define que uma determinada coluna de uma tabela
armazenará valores de um determinado tipo de dado, as características destes
valores que ocuparão aquela parte da tupla foram definidas. Os tipos
permitidos para atributos de tabelas sempre sofrem inovações com o passar do
tempo e desde o System R, da IBM, estes pequenos coadjuvantes ou artifícios
são bastante úteis para definição da forma como a informação deverá ser
armazenada, melhor acessada e aproveitada, sem que haja perda e
informações indesejadas não sejam permitidas.
Para que os dados estejam realmente seguros, devemos prestar bastante
atenção ao definir o tipo de dados de colunas em tabelas em bancos de dados

2. de um projeto. É muito importante entender cada tipo de dado e suas
conseqüências diante de várias das situações que seu banco de dados poderá
ser exposto no dia- a-dia.
Começaremos então, com este artigo, um estudo sobre os tipos de dados
suportados pelo MySQL 5.0 assim como os principais fatores que podem tornar
a vida complicada desde a criação do banco de dados até o momento em que
somos, nós DBA`s, expostos à situações chatas como alterações indesejadas
e inesperadas nas informações contidas no banco de dados, em certos
momentos até irreparáveis. Não é de hoje que se fala que ter um backup válido
é preciso, mas sem entender os tipos de dados e todas as implicações que
eles têm, talvez nem seja preciso ter um backup válido, pois, mesmo este não
terá muito valor. Saber gerenciar um banco de dados quanto aos conjuntos de
caracteres, espaço em disco e otimização em relação ao retorno da informação
é parte do conjunto de assuntos que abordaremos neste estudo.
Este foi produzido em um ambiente simples, rodando o MySQL 5.0.45-nt-
log sobre oWindows XP Professional. Utilizamos alguns exemplos com
o MySQL Query Browser e outros exemplos com o mysql client, este que já é
fornecido na instalação padrão doMySQL 5.0.
Sempre que iniciamos a definição de um modelo de dados lógico, já temos que
ter conhecimento dos tipos de dados para definirmos as colunas das tabelas,
mas não é somente para definir as colunas, temos que ter conhecimento em
relação à fatores como rapidez na recuperação da informação e espaço
requerido para o armazenamento. Além disso, algumas colunas ainda terão
índices, o que poderá diminuir o tempo de respostas ou mesmo degradar o
desempenho de uma consulta. NoMySQL 5.0, como na maioria dos bancos de
dados, temos um problema grave que divide muitos profissionais, a questão em
torno de tipos de dados CHAR e VARCHAR, que tem tipos de armazenamento
muito similares mas com grandes implicações no que se refere ao espaço
requerido em disco e performance para recuperação de informação. Fatores
que analisaremos assim que começarmos a discutir tipos de dados string.
Temos que ter ciência e muito cuidado para definir tipos de dados de
colunasAUTO_INCREMENT . A análise do negócio da aplicação que utilizará o
banco de dados e suas tabelas para armazenar seus dados deve ser muito
bem feita para que não haja problemas com colunas com tipos de dados
errados ou mesmo, no pior cenário, perda de informação em seu fluxo de
entrada. Uma PRIMARY KEY mal definida, por exemplo, com o
atributo AUTO_INCREMENT que seja mal dimensionada, poderá parar uma
aplicação com um erro de chave duplicada (DUPLICATE KEY), por exemplo.
Assim como veremos mais à frente, existem vários conceitos e várias diretrizes
que deveremos memorizar para que a definição de um banco de dados alcance
suas metas no tocante à tempo de resposta e consumo de recursos.
Tipos de Dados Suportados Pelo MySQL
O MySQL suporta as seguintes categorias de tipos de dados:

3. Tipos de Valores Numéricos: podem ser tipos inteiros, de ponto- flutuante ou de
ponto- fixo, ocupando de um a oito bytes por valor em disco, com exceção do
tipo bit que armazena de zero a 64 bits por valor. São
eles: BIT, TINYINT, SMALLINT, INT,MEDIUMINT e BIGINT. Tipos de
numéricos de ponto- flutante FLOAT e DOUBLE que requem quatro e oito
bytes, por valor, respectivamente, e de ponto-fixo DECIMAL, que requer quatro
bytes aproximadamente para cada lado, precisão e escala.
Temos alguns sinônimos de alguns tipos de dados de tipo numérico:
 Até o MySQL 5.0.3, BIT era sinônimo de TINYINT(1);
 BOOL ou BOOLEAN é o mesmo que TINYINT;
 SERIAL é um apelido (alias) de BIGINT UNISINED NOT NULL
AUTO_INCREMENT UNIQUE;
 INTEGER é o mesmo que INT;
 DEC é um sinônimo de DECIMAL. Tipos de Valores String: podem ser binários ou não- binários, podendo ocupar
espaços de tamanhos fixos ou variáveis. São
eles: CHAR, VARCHAR, TEXT, BINARY,VARBINARY, BLOB, ENUM e SET.
Os tipos de dados de valores string, se dividem em dois segmentos:
Não-binários: CHAR, VARCHAR e TEXT, este último que se divide em mais
outros três, que são: TINYTEXT, MEDIUMTEXT e LONGTEXT.
Binários: BINARY, VARBINARY e BLOB, este último que se divide em mais
outros três, que são: TINYBLOB, MEDIUMBLOB e LONGBLOB.
Tipos de Valores Temporais: são tipos de dados utilizados normalmente para
armazenar informações de data e hora, podendo ser somente a data ou a hora,
de acordo com o contexto e pode ocupar de um a oito bytes por valor. São
eles: DATE,TIME, DATETIME, TIMESTAMP e YEAR.
Quando nos referimos ao espaço de armazenamento para um valor de uma
coluna de acordo com o seu tipo de dado, nos referimos ao espaço que cada
valor de um determinado tipo de dado ocupará no arquivo de dados em disco,
ou seja, uma linha inteira ou tupla tem um somatório de bytes que pode nos
prover um número médio de crescimento do banco de dados ao longo do
tempo.
Para facilitar a compreensão, imagine ter uma coluna do tipo INT para uma
tabela que armazenará tipos de sexo. Seria um desperdício de espaço, já que
o tipo de dados INTpode armazenar muito mais que duas posições. O
tipo TINYINT seria o mais indicado em relação ao espaço necessitado para o
armazenamento, nesse contexto.
Tipos de Dados Numéricos
O MySQL 5.0 suporta todos os tipos de dados padrão SQL. Todos os tipos
supracitados são tipos de dados aceitos e podem ter muitos outros sinônimos –
palavra que tem exatamente o mesmo sentido que outra ou quase idêntico. O
que temos que ter ciência também é quanto de espaço por valor é requerido
por cada tipo. Tais especificações são expostas na Tabela 1.

4. Tabela 1 – Tipos Numéricos
Os tipos de dados numéricos, além de terem suas representações em colunas,
possuem também atributos/propriedades tais
como UNSIGNED, ZEROFILL e AUTO_INCREMENT .
O UNSIGNED impede que a coluna que tenha, por exemplo, o tipo de
dado INTarmazene números negativos, ou seja, valores menores que zero não
serão permitidos. Quando omitimos o atributo UNSIGNED em uma coluna de
tipos de dados numéricos, seu padrão é SIGNED, ou seja, permitirá valores
negativos. Uma observação interessante é que uma
coluna AUTO_INCREMENT sempre será automaticamenteUNSIGNED, mesmo
se omitirmos o atributo na criação.
O atributo ZEROFILL provoca efeitos de auto- preenchimento de espaços não
utilizados em uma coluna com zeros. Digamos que temos uma tabela, que
criamos com uma coluna do tipo TINYINT(2) e fornecemos a seguir
um INSERT como mostra a Figura 1.
Figura 1 – Criando a
tabela com a coluna ZEROFILL. (MySQL Query Browser)
O resultado poderá ser notado no SELECT seguinte feita na tabela de nome
“tabela”:
Figura 2 – Efeito de
ZEROFILL em uma coluna de uma tabela. (MySQL Query Browser)
Perceba que como o campo tem o display “2″, adicionado ao tipo de
dado TINYINT na criação da tabela, o *SGBD – leia-se Sistemas de
Gerenciamento de Bancos de Dados – MySQL 5.0 preenche os espaços com
zeros. Caso o campo tenha o atributoZEROFILL omitido na criação de uma
coluna quando criamos uma tabela, os espaços são preservados.
Utilizar ZEROFILL em uma coluna, automaticamente a tornaUNSIGNED, ou
seja, o campo não aceitará valores menores que zero.
O atributo AUTO_INCREMENT é bastante utilizado quando se precisa criar um
campo na tabela para ser chave primária quando não temos muitas alternativas
em outros atributos para que seja gerado automaticamente um identificador

5. único para cada registro que será cadastrado na tabela. Para que um
campo AUTO_INCREMENT seja criado sem problemas de compilação, a
coluna criada deverá seguir determinadas diretrizes, tais como:
 Obrigatoriamente o tipo de dado deve ser numérico e inteiro, ou seja, a
coluna pode ter tipos SMALLINT, MEDIUMINT, INT ou BIGINT;
 A coluna deve ser indexada (INDEX, UNIQUE INDEX ou PRIMARY KEY) e
ter a restrição NOT NULL;
 Uma tabela somente poderá ter uma única coluna com este atributo;
OBS.: A conjunção de UNIQUE INDEX + NOT NULL é igual à PRIMARY KEY.
O comportamento de uma coluna AUTO_INCREMENT tem alguns pontos à
salientar. Quando atribuímos de forma explícita um valor, caso esse valor não
seja repetido, o SGBD cadastra na coluna aquele que foi enviado e continua a
seqüência a partir deste. Para deixarmos que o SGBD tome conta da
seqüência dos números dessa coluna, podemos omitir a coluna da lista de
colunas no comando INSERT. Não será possível omitir a lista de colunas tendo
uma coluna com o atributo AUTO_INCREMENT de um comando ou
declaração INSERT, a menos que você atribua um valor explicitamente, o que
pode lhe trazer problemas com chave duplicada, caso esta coluna seja
indexada com uma PRIMARY KEY ou um UNIQUE INDEX.
Outro fator interessante, referente ainda aos tipos de dados numéricos é que,
caso tenhamos uma coluna do tipo SMALLINT UNISIGNED em uma
determinada tabela e cadastrarmos um valor maior que a largura permitida por
aquele tipo de dado, o SGBD truncará o valor fornecido para o maior valor
permitido pelo tipo de dado e enviará umWARNING ao usuário. Para exibir
o WARNING basta emitir o comando SHOW WARNINGS. A Figura 3 ilustra
esta situação.
Figura 3 – Dados são truncados e inseridos na tabela e um WARNING é
enviado ao usuário.
Esse comportamento poderá ser desagradável, uma vez que numa situação
dessas, esperamos que o SGBD não aceite. Para que isso aconteça, basta que
configuremos oSQL MODE para TRADITIONAL. Após isto, a maioria
dos WARNINGS disparados ao usuário serão explicitados em forma de erro,

6. não permitindo que valores indevidos, mesmo que truncados, cheguem a ser
inseridos, como mostra a Figura 4.
Figura 4 – Configuramos o SQL MODE para TRADITIONAL e o erro foi
explicitado.
O MySQL 5.0 ainda suporta os tipos de dados de ponto-
flutuante, FLOAT e DOUBLEque requerem quatro e oito bytes
respectivamente. Apesar de serem mais eficientes em performace na
recuperação por utilizarem um tipo numérico nativo do processamento
computacional, estes podem apresentar erros de arredondamento. Não seria
uma boa idéia utilizar tais tipos de dados para armazenar dados financeiros,
como preços e valores de serviços em notação monetária. Para isto, o MySQL
5.0 disponibiliza o tipo de ponto- fixo DECIMAL, que fornece mais segurança
quanto ao armazenamento de preços por exemplo por não apresentar
problemas com arredondamento, mas por outro lado são mais lentos na
recuperação e cálculos. NUMERIC é o mesmo que DECIMAL e funciona da
mesma maneira no MySQL 5.0.
As declarações abaixo são similares:
 DECIMAL
 DECIMAL(10)
 DECIMAL(10,0)
OBS.: Já que para valores monetários, é melhor utilizarmos o tipo de
dado DECIMAL, podemos definir os campos como DECIMAL(10,2), o que nos
dará 10 digitos de precisão – posições antes da vírgula – e 2 posições de
escala – posições após a vírgula. Para inserirmos dados num campo decimal, a
vígula deverá ser substituída pelo ponto (‘. ‘).
Tipo de Dados BIT
Não temos muito a falar do tipo de dados BIT, somente que ele é um tipo de
dados que armazena de 1 à 64 bits e seu valor não poderá ser recuperado com
um simplesSELECT e sim em meio a uma expressão. Fazendo um
simples SELECT temos a exibição do bit-field enquanto quando fazemos
um SELECT abordando o campo em meio a uma expressão simples, seu valor
é retornado, como mostra a Figura 5.

7. Figura 5 – Retorno de valor real de uma coluna do tipo BIT.
Conclusão da Primeira Parte
Vimos então neste artigo, com aplicações práticas dos conceitos e teorias
apresentadas que, o MySQL 5.0 apresenta, em ordem de quantidade de
espaço requerido por valor em uma coluna, os tipos
inteiros BIT, TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, os de ponto
flutuante FLOAT e DOUBLE e o de ponto fixo, DECIMAL, que é o mais
indicado para armazenamento de valores monetários. Vimos também quais são
e como utilizar as
propriedades ZEROFILL, AUTO_INCREMENT e UNSIGNED, além de falarmos
rapidamente do SQL MODE e espaço requerido por valor de cada tipo de dado.
No próximo artigo, que compõe esse estudo, apresentaremos os tipos de
dados String, ou seja, todos os dados que suportam caracteres binários e não-
binários no MySQL 5.0, bem como os conjuntos de caracteres existentes e
suas especificações e a tão discutida diferença entre os
tipos CHAR e VARCHAR.