Skip to main content

Generated Columns in MySQL with Atlas

MySQL is a popular open-source relational database. Generated columns are a feature of MySQL that allows you to define tables with columns whose value is a function of the value stored in other columns; without requiring complex expressions in SELECT, INSERT or UPDATE queries.

What are Generated Columns?​

Generated columns are columns that contain values calculated by expressions which can be dependent on other columns; in a similar manner to formulas in a spreadsheet. There are two types of generated columns in MySQL: Stored and Virtual.

Stored Generated Columns​

Stored generated columns are stored and evaluated when a row is inserted or updated. As a result, stored generated columns use disk space in addition to CPU cycles during the execution of INSERT and UPDATE statements.

Virtual Generated Columns​

Virtual generated columns are not stored, and only evaluated when a row is read (after BEFORE triggers). As a result, virtual generated columns take no storage at the cost of CPU cycles for SELECT statements.

Limitations of Generated Columns​

Generated column expressions must be deterministic which means that — given the same input — an expression must always produce the same output. As a result, generated columns can not be used with stored variables, functions, procedures, and subqueries; which could cause the output to be non-deterministic. Following this constraint, generated columns can not be used to generate random values. On the other hand, a generated column may reference any non-generated column regardless of its position within the table row and any other generated column within the same table row, as long as those columns are declared before the generated column.

When to use Generated Columns?​

Generated columns should be used whenever you want to create a column with a value that can be directly determined from the values of other columns in the same row. In simpler words, for data that is dependent on other data. This saves the developer from complex application code that is prone to errors on SELECT, INSERT and UPDATE statements. It also ensures that data which must be consistent, stays consistent.

MySQL Syntax for a Generated Column

column_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
    [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string']

Using Stored Generated Columns​

Stored generated columns should be used for data (in a table) that is read more frequently than it is updated. This saves CPU cycles while reading rows (via SELECT). Stored generated columns should also be used when you want to use the column in the table primary key or use it as a foreign key constraint. Alternatively, use stored generated columns as a cache for complex conditions that are costly to calculate.

Example​

The following example declares a stored generated column in a table that stores the base and height of a triangle in the base and height column, then computes its area in area (when triangles are inserted or updated).

CREATE TABLE triangles (
    base   DOUBLE,
    height DOUBLE,
    area   DOUBLE AS (base * height * 1/2) STORED
);

Using Virtual Generated Columns​

Virtual generated columns should be used for computed data (in a table) that is updated more frequently than it is read or computed data that is expensive to store on disk (via INSERT or UPDATE). Since values are calculated on the fly, virtual generated columns are perfect for table columns that will have a new value for every SELECT statement. If you use the InnoDB Storage Engine, secondary indexes can be defined on virtual columns.

Example​

The following example declares a virtual generated column in a table that stores the price and amount of products sold in the price and quantity column, then computes its revenue (when products are read).

CREATE TABLE products (
    price    DOUBLE,
    quantity INT,
    revenue  DOUBLE AS (price * quantity) VIRTUAL
);

Managing Generated Columns is easy with Atlas​

Managing generated columns and database schemas in MySQL is confusing and error-prone. Atlas is an open-source tool that allows you to manage your database using a simple declarative syntax (similar to Terraform). Instead of creating complex SQL statements that break upon schema migration, we will implement generated columns using Atlas.

Getting started with Atlas​

Install the latest version of Atlas using the Guide to Setting Up Atlas.

Generated Column Syntax in Atlas​

Use as in a column in a table to declare a MySQL generated column. For examples with other databases, read the Atlas Generated Columns DDL.

column "name" {
    type = data_type
    as {
        expr = expression
        type = [STORED | VIRTUAL]
    }
}

Implementing Stored Generated Columns with Atlas​

The following example declares a stored generated column in a table that stores the lengths of the sides of right-triangles in the a and b column, then computes the hypotenuse in c (when triangles are inserted or updated).

table "triangles"  {
    schema  = schema.example
    column "a"  {
        type  = numeric
    }
    column "b"  {
        type  = numeric
    }
    column "hypotenuse"  {
        type  = numeric
        as  {
            expr  = "SQRT(a * a + b * b)"
            type  = STORED
        }
    }
}

Guarantee the table is created by applying the schema to the database.

atlas schema apply -u "mysql://root:pass@localhost:3306/example" -f atlas.hcl

Approve the schema migration plan that Atlas creates for you (if applicable).

-- Planned Changes:
-- Create "triangles" table
CREATE TABLE "example"."triangles" ("a" numeric NOT NULL, "b" numeric NOT NULL, "hypotenuse" numeric NOT NULL GENERATED ALWAYS AS (SQRT(a * a + b * b)) STORED)
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
> Apply
Abort

Insert triangles into the table.

INSERT INTO triangles (a, b) VALUES (1,1);
INSERT INTO triangles (a, b) VALUES (3,4);
INSERT INTO triangles (a, b) VALUES (6,8);

Select all the triangles in the table using SELECT * FROM triangles to receive a table with the following output.

abc
111.4142135623730951
345
6810

Implementing Virtual Generated Columns with Atlas​

The following example declares a virtual generated column in a TABLE that stores the first and last name of a person, and computes the full name of the person (when people are selected).

table "people"  {
    schema  = schema.example
    column "first_name"  {
        type  = varchar(255)
    }
    column "last_name"  {
        type  = varchar(255)
    }
    column "full_name"  {
        type  = varchar(255)
        as  {
            expr  = sql("CONCAT(first_name, ' ', last_name)")
            type  = VIRTUAL
        }
    }
}

Alternatively, use the default type of generated column (VIRTUAL in MySQL).

table "people"  {
    schema  = schema.example
    column "first_name"  {
        type  = varchar(255)
    }
    column "last_name"  {
        type  = varchar(255)
    }
    column "full_name"  {
        type  = varchar(255)
        as  = sql("CONCAT(first_name," ",last_name)")
    }
}

Approve the schema migration plan that Atlas creates for you (if applicable).

-- Planned Changes:
-- Create "people" table
CREATE TABLE "example"."people" ("first_name" character varying(255) NOT NULL, "last_name" character varying(255) NOT NULL, "full_name" character varying(255) NOT NULL GENERATED ALWAYS AS (first_name + ' ' + last_name) VIRTUAL)
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
> Apply
Abort

Insert people into the table.

INSERT INTO people (first_name, last_name) VALUES ("Bob", "Bark");
INSERT INTO people (first_name, last_name) VALUES ("Kat", "Meow");
INSERT INTO people (first_name, last_name) VALUES ("Ty", "Garoar");

Select all the people in the table using SELECT * FROM people to receive a table with the following output.

first_namelast_namefull_name
BobBarkBob Bark
KatMeowKat Meow
TyGaroarTy Garoar

Need More Help?​

Join the Ariga Discord Server for early access to features and the ability to provide exclusive feedback that improves your Database Management Tooling.