Colunas derivadas mantidas pela aplicação

Uma busca por descrição pode deixar de encontrar um produto logo depois de uma correção feita direto no banco, e o total de uma venda pode deixar de bater com a soma dos itens, sem nenhum erro nas tabelas. Nos dois casos, a explicação costuma estar em uma coluna derivada, cujo valor a aplicação calcula a partir de outras colunas e grava junto com elas.

O que é uma coluna derivada

Uma coluna derivada guarda um resultado que poderia ser calculado a partir de outras colunas. A aplicação grava esse resultado para tornar mais rápidas as buscas, as ordens e os relatórios. No cenário, há dois exemplos:

• a coluna descricao_busca, da tabela produtos, guarda a descrição em letras maiúsculas e sem acentos, e a busca e a ordem por descrição usam essa coluna;
• a coluna total, da tabela vendas, guarda a soma dos itens da venda, calculada com a quantidade e o preço unitário de cada item.

Há também colunas derivadas criadas pela camada de tradução da aplicação. Quando uma ordem usa uma expressão que o banco não monta sozinho, a camada pode calcular essa expressão e gravar o resultado em uma coluna de chave combinada, que junta o valor da expressão e o número do registro em um único texto de tamanho fixo. O formato dessa coluna é interno da camada.

Quem mantém o valor

A aplicação recalcula a coluna derivada a cada gravação feita pelo caminho normal do sistema. O MySQL não conhece essa regra. Para o banco, a coluna derivada é uma coluna comum, e um comando feito direto no banco altera apenas as colunas citadas nele.

Um UPDATE direto que corrige a descrição de um produto de DIPIRONA 500MG para DIPIRONA SÓDICA 500MG muda a coluna descricao, mas a coluna descricao_busca continua com o texto antigo. A busca do sistema, que procura na coluna derivada, deixa de encontrar o produto pelo nome novo e continua a encontrá-lo pelo nome antigo. Da mesma forma, um UPDATE direto na quantidade de um item muda a soma dos itens, mas não o total gravado na venda.

O índice guarda o valor derivado

Quando a coluna derivada tem um índice, o índice guarda os valores que estão na coluna. Depois do UPDATE direto, o índice continua íntegro, mas organiza o texto antigo. Uma verificação física da tabela não encontra nenhum erro, e a reconstrução do índice refaz as entradas com os mesmos valores desatualizados. O problema está no conteúdo da coluna, e só o recálculo do valor resolve.

Conferir os valores derivados

A conferência compara a coluna derivada com o resultado da regra aplicada às colunas de origem:

• SELECT v.id, v.total, SUM(i.quantidade * i.preco_unitario) AS soma_itens FROM vendas v JOIN itens_venda i ON i.venda_id = v.id GROUP BY v.id, v.total HAVING v.total <> soma_itens;: mostra as vendas cujo total difere da soma dos itens;
• SELECT id, descricao, descricao_busca FROM produtos WHERE descricao_busca <> descricao;: mostra os produtos com a descrição de busca diferente da descrição.

A segunda consulta depende da collation das colunas. Com uma collation que ignora maiúsculas e acentos, como a padrão do MySQL 8, a comparação só aponta as linhas em que as palavras mudaram. Com outras collations, a conferência precisa repetir a regra completa da aplicação. Nos dois casos, a consulta aponta candidatos, e a regra da aplicação confirma a divergência.

Corrigir pelo caminho da aplicação

A correção de uma coluna derivada recalcula o valor com a mesma regra da aplicação. Muitos sistemas têm uma rotina de manutenção que recalcula essas colunas, e a gravação do registro pela própria tela também recalcula as colunas derivadas daquele registro. Um UPDATE manual na coluna derivada exige conhecer a regra exata, com o tratamento de acentos, de espaços e de tamanho, e uma coluna de chave combinada segue um formato interno que dificilmente é reproduzido à mão.

Por isso, uma correção feita direto no banco precisa considerar as colunas derivadas antes da execução. Ou a correção inclui essas colunas, com a mesma regra da aplicação, ou é seguida da rotina que recalcula os valores.

O cálculo feito pelo próprio MySQL

O MySQL também sabe calcular uma coluna a partir de outras. Uma coluna gerada, disponível a partir do MySQL 5.7, tem o valor calculado pelo próprio servidor, e um índice funcional, disponível a partir do MySQL 8.0.13, indexa o resultado de uma expressão. Nesses recursos, o servidor faz o cálculo sozinho, e uma alteração feita direto no banco também atualiza o resultado.

Esses recursos não mudam as colunas derivadas que a aplicação já criou, que continuam comuns. A troca de um modelo pelo outro exige que a expressão do servidor reproduza exatamente a regra da aplicação, e essa é uma decisão de desenvolvimento, e não uma correção de suporte.