How To Use The MySQL Generated Columns

How To Use MySQL Generated Columns

Introduction to Generated Columns in MySQL

Generated columns in MySQL are special columns whose values are computed based on other columns in the same table. These columns can either be virtual (computed on demand) or stored (computed and saved to disk).

Generated columns are useful for situations where you want to store values derived from other columns or calculate values on the fly, avoiding the need for redundant storage of the computed data.

Types of Generated Columns

Virtual Generated Columns:
- The value is calculated on the fly when queried.
- Not stored in the database.
- They save disk space but may have a slight performance hit when queried because the calculation is done each time.
Stored Generated Columns:
- The value is stored in the database.
- It can improve query performance because the value is precomputed and does not require recalculation every time the row is accessed.
- It uses more storage space.

Syntax for Creating Generated Columns

column_name data_type GENERATED ALWAYS AS (expression) [VIRTUAL | STORED]

expression: A valid expression that computes the value for the generated column (e.g., arithmetic, string manipulation, or date functions).
VIRTUAL: Default. The column is computed when queried, not stored.
STORED: The column is stored on disk and computed when the row is inserted or updated.

Creating a Table with Generated Columns

Let's go through an example where we create a table that includes a generated column.

1. Virtual Generated Column Example

Suppose we want a table products with a price and tax_rate, and we want to calculate the total_price (price plus tax) as a virtual column.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2),
    tax_rate DECIMAL(5, 2),
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price + (price * tax_rate / 100)) VIRTUAL
);

Here, total_price is a virtual generated column. It's calculated as price + (price * tax_rate / 100) each time the data is queried.
It will not use additional storage as it is computed dynamically when requested.

2. Stored Generated Column Example

Now, let’s create a table where the total_price is a stored generated column:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2),
    tax_rate DECIMAL(5, 2),
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price + (price * tax_rate / 100)) STORED
);

In this example, total_price is stored in the database, which means it will occupy storage space but will be faster to access since it is precomputed.

Inserting Data into a Table with Generated Columns

When inserting data into a table with generated columns, you don't need to provide values for the generated column. MySQL will compute it automatically based on the expression.

Example Insert:

INSERT INTO products (price, tax_rate) VALUES (100.00, 10.00);

For the virtual column, the total_price will be calculated dynamically when queried, based on the inserted price and tax_rate.
For the stored column, the total_price will be calculated at insert time and stored in the database.

Querying Data from a Table with Generated Columns

When you query the data, the generated column behaves like any other column.

Example Query:

SELECT id, price, tax_rate, total_price FROM products;

For a virtual generated column, total_price will be computed dynamically for each row at query time.
For a stored generated column, total_price will be retrieved directly from the stored value.

Updating Data in a Table with Generated Columns

When updating the data in a table with generated columns, you can modify the non-generated columns (e.g., price or tax_rate), and MySQL will automatically update the generated column based on the expression.

Example Update:

UPDATE products SET price = 120.00, tax_rate = 8.00 WHERE id = 1;

MySQL will automatically recalculate the total_price based on the updated price and tax_rate.
If the column is stored, the new total_price will be saved in the database.
If the column is virtual, the total_price will be recalculated the next time you query the table.

Using Generated Columns in Indexes

You can create indexes on generated columns, which can improve query performance, especially if the column is stored.

CREATE INDEX idx_total_price ON products (total_price);

This index will help speed up queries that filter or sort by total_price.

Dropping a Generated Column

You can drop a generated column from a table if it is no longer needed.

ALTER TABLE products DROP COLUMN total_price;

This removes the generated column from the table entirely.

Limitations of Generated Columns

Generated columns cannot reference other generated columns in their expression.
Generated columns cannot be used in primary or unique keys unless the column is also indexed.
Expressions used in generated columns are limited to deterministic functions (functions that always return the same result given the same input, like +, -, *, /, and string functions).
Cannot use generated columns in foreign keys.

Best Practices for Using Generated Columns

Use virtual columns when you don't need the value to be stored but want it to be calculated dynamically on query.
Use stored columns for performance if the calculation is complex and will be queried frequently.
Avoid excessive use of stored columns if the calculation result can be easily computed during query time, as it will take additional storage.

Summary

Generated columns are columns whose values are calculated from other columns in the same table.
They can be either virtual (calculated on demand) or stored (precomputed and saved in the database).
They are useful for derived values like totals, ratios, or transformations that avoid redundant data storage.
Use virtual generated columns for efficiency if calculation can be done on the fly, and stored generated columns when you need to precompute and store the values for faster access.

Would you like to see more complex examples or any specific use cases for generated columns?

Souy Soeng