Como usar as colunas geradas pelo MySQL

Resumo : neste tutorial, você aprenderá como usar as colunas geradas pelo MySQL para armazenar dados calculados a partir de uma expressão ou outras colunas.

Introdução à coluna gerada pelo MySQL

Ao criar uma nova tabela, você especifica as colunas da tabela na CREATE TABLEinstrução. Em seguida, você usa as instruções INSERT, UPDATEe DELETE para modificar diretamente os dados nas colunas da tabela.

O MySQL 5.7 introduziu um novo recurso chamado coluna gerada . As colunas são geradas porque os dados nessas colunas são calculados com base em expressões predefinidas.

Por exemplo, você tem o contactscom a seguinte estrutura:

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para obter o nome completo de um contato, você usa a CONCAT()função da seguinte forma:

SELECT 
    id, 
    CONCAT(first_name, ' ', last_name), 
    email
FROM
    contacts;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Esta não é a consulta mais bonita ainda.

Usando uma coluna gerada, você pode recriar a contactstabela da seguinte maneira:

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)),
    email VARCHAR(100) NOT NULL
);Linguagem de código:  PHP  ( php )

Esta GENERATED ALWAYS as (expression)é a sintaxe para criar uma coluna gerada.

Para testar a fullnamecoluna, você insere uma linha na contactstabela.

INSERT INTO contacts(first_name,last_name, email)
VALUES('john','doe','[email protected]');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Agora, você pode consultar dados da contactstabela.

SELECT 
    *
FROM
    contacts;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Os valores na fullnamecoluna são calculados dinamicamente quando você consulta dados da contactstabela.

MySQL fornece dois tipos de colunas geradas: armazenadas e virtuais. As colunas virtuais são calculadas instantaneamente cada vez que os dados são lidos, enquanto as colunas armazenadas são calculadas e armazenadas fisicamente quando os dados são atualizados.

Com base nesta definição, a fullnamecoluna do exemplo acima é uma coluna virtual.

Sintaxe para coluna gerada pelo MySQL

A sintaxe para definir uma coluna gerada é a seguinte:

column_name data_type [GENERATED ALWAYS] AS (expression)
   [VIRTUAL | STORED] [UNIQUE [KEY]]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Primeiro, especifique o nome da coluna e seu tipo de dados.

Em seguida, adicione a GENERATED ALWAYScláusula para indicar que a coluna é uma coluna gerada.

Em seguida, indique o tipo da coluna gerada utilizando a opção correspondente: VIRTUALou STORED. Por padrão, o MySQL usa VIRTUALse você não especificar explicitamente o tipo da coluna gerada.

Depois disso, especifique a expressão entre colchetes após a ASpalavra-chave. A expressão pode conter literais e funções integradas sem parâmetros, operadores ou referências a qualquer coluna da mesma tabela. Se você usar uma função, ela deverá ser escalar e determinística.

Finalmente, se a coluna gerada estiver armazenada, você poderá definir uma restrição exclusiva para ela.

Exemplo de coluna armazenada MySQL

Vejamos a productstabela no banco de dados de exemplo :

Os dados das colunas quantityInStocke buyPricepermitem calcular o valor do estoque por SKU usando a seguinte expressão:

quantityInStock * buyPriceLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

No entanto, podemos adicionar uma coluna gerada armazenada nomeada stock_valueà productstabela usando a seguinte ALTER TABLE ...ADD COLUMNinstrução:

ALTER TABLE products
ADD COLUMN stockValue DEC(10,2)
GENERATED ALWAYS AS (buyprice*quantityinstock) STORED;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Normalmente, a ALTER TABLEinstrução requer uma reconstrução completa da tabela, portanto, será demorada se você alterar as tabelas grandes. No entanto, este não é o caso da coluna virtual.

Agora podemos consultar o valor do estoque diretamente na productstabela.

SELECT 
    productName, 
    ROUND(stockValue, 2) stock_value
FROM
    products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )