Criando e usando Colunas Geradas – Campos Calculados em Tabelas no MySQL

Campos Calculados em Tabelas no MySQL

Campos calculados (ou colunas geradas) são colunas em uma tabela em um banco de dados que apresentam os resultados de uma expressão pré-definida, geralmente uma fórmula aplicada a outras colunas, da mesma forma que uma View, porém sem causar overhead no banco, pois por padrão seus dados não são fisicamente armazenados na tabela. Uma outra vantagem de um campo calculado é que a integridade dos dados é aumentada, pois os cálculos são realizados em nível de tabela, em vez de serem realizados por meio de queries (consultas) criadas pelo desenvolvedor.

Como citado, por padrão um campo calculado no MySQL não armazena nenhum valor – os dados são calculados no momento de uma consulta. Porém, é possível armazenar os dados de um campo gerado opcionalmente, o que significa que o cálculo é realizado e os dados são salvos na coluna. É possível até mesmo indexar um campo calculado, e uma das aplicações desses campos é na substituição de triggers, simplificando o design e a operação sobre o banco de dados.

Sintaxe

Para criar uma coluna calculada usamos a seguinte sintaxe:

nome_coluna tipo_dados [GENERATED ALWAYS] AS expressão [VIRTUAL | STORED] constraints

onde:

  • GENERATED ALWAYS é apenas uma forma de indicar explicitamente que o campo é calculado.
  • expressão é a fórmula que desejamos usar para realizar o cálculo do valor da coluna.
  • VIRTUAL significa que o valor do campo é calculado sempre que for realizada uma consulta a ele, mas seus dados não ficam armazenados na tabela em si. O campo nesse caso não ocupa espaço em disco. É possível criar índices secundários em colunas calculadas virtuais (com InnoDB).
  • STORED significa que o valor do campo é calculado (em operações de inserção e atualização de dados) e armazenado na tabela. O acesso aos dados é mais rápido nesse caso, mas obviamente ocupa mais espaço em disco

O padrão é a criação de colunas do tipo VIRTUAL caso não seja especificada a opção. Também é possível termos colunas VIRTUAL e STORED na mesma tabela (claro que não na mesma coluna).

Uma coluna gerada pode fazer referência a outras colunas geradas, desde que elas tenham sido definidas antes na definição da tabela. Com relação às colunas base (não-geradas), é possível fazer referência a qualquer uma (exceto colunas com auto incremento), mesmo que sua definição ocorra posteriormente na tabela.

Não é possível usar a opção AUTO_INCREMENT em um campo calculado.

Exemplos

Como primeiro exemplo, vamos criar uma tabela de nome tbl_mult, que contém três campos numéricos: dois com dados inseridos pelo usuário (num1 e num2) e um terceiro gerado pela multiplicação de num1 por num2 (num1 * num2):

CREATE TABLE tbl_mult (
  ID SMALLINT PRIMARY KEY AUTO_INCREMENT,
  num1 SMALLINT NOT NULL,
  num2 SMALLINT NOT NULL,
  num3 SMALLINT GENERATED ALWAYS AS (num1 * num2) VIRTUAL
);

Como criamos a coluna gerada como VIRTUAL, seus dados não ficam armazenados em disco.

Podemos inserir alguns registros na tabela para testar a geração dos dados no campo calculado num3:

INSERT INTO tbl_mult (num1, num2)
VALUES (2,1), (2,2), (2,3), (2,4);

E verificar os dados calculados realizando uma consulta à tabela:

SELECT * FROM tbl_mult;

Colunas Geradas em MySQL

Veja que os dados na coluna num3 mostram o resultado do cálculo especificado em cada linha da tabela.

Exemplo 02

Vejamos outro exemplo. Suponha uma tabela de vendas contendo os campos Preco_Produto, Qtde, Desconto e Preco_Total. Queremos criar essa tabela de modo que o campo Preco_Total seja calculado dinamicamente, multiplicando o preço do produto pela quantidade (adquirida), e aplicando um desconto percentual especificado na coluna Desconto ao preço total. Queremos também persistir o valor calculado na tabela:

CREATE TABLE tbl_Vendas (
 ID_Venda SMALLINT PRIMARY KEY AUTO_INCREMENT,
 Preco_Produto DECIMAL(6,2) NOT NULL,
 Qtde TINYINT NOT NULL,
 Desconto DECIMAL(4,2) NOT NULL,
 Preco_Total DECIMAL(6,2) AS (Preco_Produto * Qtde * (1 - Desconto / 100)) STORED
);

Vamos inserir alguns dados de vendas na tabela e depois verificar se o preço total foi calculado corretamente:

INSERT INTO tbl_Vendas (Preco_Produto, Qtde, Desconto)
VALUES
(50.00, 2, 20),
(65.00, 3, 15),
(100.00, 1, 12),
(132.00, 3, 18);

Agora vamos efetuar a consulta para visualizar os dados na tabela:

SELECT * FROM tbl_Vendas

Colunas Geradas MySQL

Note que o campo Preco_Total possui os preços calculados corretamente para cada produto vendido.

Observações importantes:

  • Uma coluna calculada não pode ter a restrição NOT NULL aplicada, e também não pode ter dados inseridos por uma declaração INSERT e nem modificados por um UPDATE.
  • Também não pode ser utilizada com definições de restrição DEFAULT e FOREIGN KEY (chave estrangeira).
  • Não é necessário especificar o tipo de dados do campo calculado ao criar a tabela.
  • Colunas persistidas ocupam mais espaço em disco do que colunas calculadas virtuais (sem PERSISTED).

Próximo: Realizar Consultas simples em Tabelas no MySQL – Comando SELECT

 

Sobre Fábio dos Reis (1212 Artigos)
Fábio dos Reis trabalha com tecnologias variadas há mais de 30 anos, tendo atuado nos campos de Eletrônica, Telecomunicações, Programação de Computadores e Redes de Dados. É um entusiasta de Ciência e Tecnologia em geral, adora Viagens e Música, e estuda idiomas, além de ministrar cursos e palestras sobre diversas tecnologias em São Paulo e outras cidades do Brasil.

3 Comentários em Criando e usando Colunas Geradas – Campos Calculados em Tabelas no MySQL

  1. Carlos Alberto // 10/05/2018 em 13:41 // Responder

    Oi Fábio, tudo bem. Fiz o teste com o campo calculado mas dá erro de sintaxe. Utilizo o HeidiSQL. Ele tem alguma restrição em relação ao Workbench?
    Grato

  2. Carlos Mader // 06/04/2020 em 17:55 // Responder

    Boa tarde
    Estou apreendendo sobre banco de dados, e suas aulas tem me ajudado muito, porem no meu exemplo de tabela quando insiro colunas calculadas, com um numero grande de variaveis e calculo de percentagem, tem tido problema com uma informação que aparece na forma de Warming – Dados trucados na linha… 1265.
    Estas colunas tenho definidas como DECIMAL (5,2) ou (6,2)
    Quando seleciono a tabela os dados estão calculados corretamente.
    Qual causa desta informação ou qual o procedimento que estou usando de forma inadequada

  3. Hortêncio Barca // 15/07/2023 em 22:10 // Responder

    Pergunta: posso utilizar a expressão usada para “soma cumulativa” ou “running sum” numa coluna calculada?

Escreva um comentário

Seu e-mail não será divulgado


*