Tabelas, colunas e tipos de dados

Uma busca pelo código de um produto pode trazer o item errado, um valor pode aparecer cortado, e uma soma pode errar alguns centavos. Muitas vezes, a explicação está no tipo de dado da coluna. Cada coluna guarda um tipo de informação, e o tipo define o que cabe nela, como o valor é comparado e como deve ser escrito em uma consulta.

A definição da tabela

Toda tabela tem uma definição, que lista as colunas e o tipo de cada uma. Dois comandos mostram essa definição:

• SHOW COLUMNS FROM produtos;: lista as colunas, com o tipo, a aceitação de valor ausente, a participação em chaves, o valor padrão e informações extras;
• SHOW CREATE TABLE produtos;: mostra o comando completo que criaria a tabela, com as colunas, os índices e o engine.

No cenário, a tabela produtos do banco loja tem estas colunas:

• id, do tipo INT: o número que identifica cada produto no banco;
• codigo, do tipo VARCHAR(20): o código do produto, como o código de barras;
• descricao, do tipo VARCHAR(100): o nome do produto;
• preco, do tipo DECIMAL(10,2): o preço de venda;
• estoque, do tipo INT: a quantidade disponível;
• ativo, do tipo TINYINT: a indicação de produto ativo, com o valor 1, ou inativo, com o valor 0.

Os tipos numéricos

• INT: números inteiros, de menos 2,1 bilhões até 2,1 bilhões, aproximadamente. Com a opção UNSIGNED, que retira o sinal, o limite passa a ser de cerca de 4,3 bilhões, sem valores negativos.
• BIGINT: números inteiros muito maiores, usados em identificadores de tabelas com grande volume.
• TINYINT: números inteiros pequenos, de menos 128 até 127. É o tipo comum para indicações de sim e não, com os valores 1 e 0.
• DECIMAL: números com casas decimais exatas. Em DECIMAL(10,2), o primeiro número é a quantidade total de dígitos, e o segundo é a quantidade de casas depois da vírgula. Por isso, esse é o tipo adequado para valores em dinheiro.
• FLOAT e DOUBLE: números com casas decimais aproximadas. O valor guardado pode diferir um pouco do valor informado, e somas de muitos valores podem acumular diferenças.

Em definições antigas, aparece um número entre parênteses depois do tipo inteiro, como em INT(11). Esse número não limita o valor guardado. Ele indicava apenas uma largura de exibição e deixou de ser usado nas versões recentes do MySQL.

Os tipos de texto

• VARCHAR: texto de tamanho variável, até a quantidade de caracteres indicada entre parênteses. Em VARCHAR(100), cabem até 100 caracteres.
• CHAR: texto de tamanho fixo. Em CHAR(1), cabe exatamente um caractere, como uma letra que indica uma situação.
• TEXT: textos longos, como observações.

As datas têm tipos próprios, como DATE, para a data, e DATETIME, para a data com o horário.

Texto e número não se comparam da mesma forma

Um código pode ter zeros à esquerda, como 00123. Se a coluna é de texto, o valor guardado é a sequência de caracteres, com os zeros. Em uma consulta, o valor de texto é escrito entre aspas simples:

• SELECT * FROM produtos WHERE codigo = '00123';: compara textos e encontra apenas o código 00123.

Quando a comparação usa um número sem aspas, o MySQL converte o texto de cada linha em número antes de comparar:

• SELECT * FROM produtos WHERE codigo = 123;: encontra o código 00123 e também o código 123, porque os dois viram o mesmo número.

Essa conversão tem outro efeito. Como o MySQL precisa converter o valor de cada linha, o índice da coluna deixa de ser usado de forma eficiente, e a consulta pode ficar lenta em tabelas grandes. O valor da comparação deve ter o mesmo tipo da coluna.

O valor maior que a coluna

Quando um valor não cabe na coluna, o resultado depende do modo de operação do servidor, chamado sql_mode:

• no modo estrito, padrão a partir do MySQL 5.7, o MySQL recusa o comando com o erro de valor longo demais, código 1406;
• fora do modo estrito, o MySQL corta o valor para caber na coluna e registra apenas um aviso.

Nas tabelas MyISAM, que não desfazem gravações, o modo estrito tem um limite. Quando o valor grande demais aparece depois da primeira linha de um comando que grava várias linhas, as linhas anteriores já foram gravadas, e o MySQL corta o valor com um aviso ou interrompe o comando no meio, com parte das linhas já alteradas.

O comando SELECT @@sql_mode; mostra o modo em uso, e o comando SHOW WARNINGS; mostra os avisos do último comando. Em um servidor fora do modo estrito, ou em uma tabela MyISAM, uma descrição cortada no meio pode ter sido gravada assim, sem nenhum erro na tela.