Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 40 ยท Data Types and Storage Engines

Generated Columns, Sequences and System-Versioned Tables

Use MariaDB features beyond plain tables: virtual and persistent generated columns, CREATE SEQUENCE, system-versioned tables with FOR SYSTEM_TIME, invisible columns and JSON indexing, with runnable stand-ins.

MariaDB has several features that save you application code and that many other open source databases lack. This lesson covers four of them: generated columns (values computed from other columns), sequences (number generators independent of tables), system-versioned tables (automatic history) and invisible columns. The syntax is MariaDB-specific, so it is shown read-only, and every idea has a runnable equivalent that works in the editor.

Generated (virtual and persistent) columns

A generated column holds a value computed from other columns of the same row. You never write it, and it can never be out of date.

SQL
CREATE TABLE order_lines (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quantity   INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  line_total DECIMAL(12,2) AS (quantity * unit_price) VIRTUAL,
  total_cents BIGINT AS (ROUND(quantity * unit_price * 100)) PERSISTENT
);
  • VIRTUAL: computed when read, uses no storage. This is the default.
  • PERSISTENT (or STORED): computed on write and saved, which costs space but reads like any column.
  • The expression must be deterministic: no NOW(), no subqueries, only functions that always give the same result for the same row.

The best reason to use generated columns is indexing an expression. MariaDB cannot index LOWER(email) directly, but it can index a generated column that holds it, and it can index values inside JSON:

SQL
ALTER TABLE users
  ADD COLUMN email_lc VARCHAR(190) AS (LOWER(email)) PERSISTENT,
  ADD UNIQUE KEY uq_email_lc (email_lc);

ALTER TABLE events
  ADD COLUMN user_id INT AS (JSON_VALUE(payload, '$.user.id')) VIRTUAL,
  ADD INDEX idx_user_id (user_id);

SQLite supports the same concept with GENERATED ALWAYS AS, so you can run it:

SQL Generated columns

Press Run (or Ctrl + Enter) to see the result here.

SQL Index an expression through a generated column

Press Run (or Ctrl + Enter) to see the result here.

Sequences

A sequence is a named number generator that exists on its own, outside any table. It is useful for invoice numbers, ids shared across tables or numbers that must not depend on AUTO_INCREMENT:

SQL
CREATE SEQUENCE invoice_no START WITH 1000 INCREMENT BY 1 CACHE 20;

SELECT NEXT VALUE FOR invoice_no;     -- 1000
SELECT NEXTVAL(invoice_no);           -- 1001 (function form)
SELECT LASTVAL(invoice_no);           -- last value this session got
SELECT PREVIOUS VALUE FOR invoice_no;

CREATE TABLE invoices (
  id     BIGINT PRIMARY KEY DEFAULT NEXT VALUE FOR invoice_no,
  amount DECIMAL(10,2)
);

SELECT * FROM invoice_no;             -- a sequence is also a one-row table
ALTER SEQUENCE invoice_no RESTART WITH 5000;
DROP SEQUENCE invoice_no;

Options include MINVALUE, MAXVALUE, CYCLE (start over at the maximum) and CACHE n (pre-allocate numbers for speed, at the price of gaps after a restart). Sequences are not transactional: a rolled-back NEXTVAL is not given back.

SQLite has no CREATE SEQUENCE, but the idea, a counter table with an atomic "take next", can be written with a small table, as a stand-in:

SQL A sequence stand-in with a counter table

Press Run (or Ctrl + Enter) to see the result here.

MariaDB's SEQUENCE engine also provides virtual tables such as seq_1_to_100, perfect for generating test data or calendar rows:

SQL
SELECT seq FROM seq_1_to_5;
INSERT INTO numbers (n) SELECT seq FROM seq_1_to_1000;

A recursive CTE gives the same result and works everywhere:

SQL Generate numbers with a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

System-versioned tables

A system-versioned table keeps the full history of every row. When you update or delete a row, the old version stays, stamped with the time period it was valid. You can then ask what the data looked like at any moment, which is excellent for auditing, debugging and recovering from mistakes.

SQL
CREATE TABLE prices (
  product_id INT NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (product_id)
) WITH SYSTEM VERSIONING;

INSERT INTO prices VALUES (1, 9.99);
UPDATE prices SET price = 12.50 WHERE product_id = 1;
DELETE FROM prices WHERE product_id = 1;

SELECT * FROM prices;                                       -- current rows only (none)
SELECT * FROM prices FOR SYSTEM_TIME ALL;                   -- every version
SELECT * FROM prices FOR SYSTEM_TIME AS OF '2026-03-01 12:00:00';
SELECT *, ROW_START, ROW_END FROM prices FOR SYSTEM_TIME BETWEEN '2026-01-01' AND '2026-03-01';

MariaDB adds two hidden columns, row_start and row_end. A current row has row_end at the maximum timestamp. You can convert an existing table with ALTER TABLE t ADD SYSTEM VERSIONING;, partition the history to keep it fast and prune old versions with DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2025-01-01'.

The same technique with plain SQL is a history table maintained by hand, or in a trigger. The stand-in below does it with two statements so you can see exactly what the engine automates:

SQL Row history by hand (what versioning automates)

Press Run (or Ctrl + Enter) to see the result here.

Invisible columns and other extras

  • ALTER TABLE users ADD COLUMN internal_flag INT INVISIBLE; hides a column from SELECT * and from inserts without a column list, so adding it does not break old code. Name it explicitly to use it.
  • RETURNING on INSERT and DELETE, seen earlier, avoids extra queries.
  • ALTER TABLE ... ADD COLUMN IF NOT EXISTS makes migrations repeatable.
  • CREATE OR REPLACE TABLE/VIEW/PROCEDURE redefines in one step.
  • JSON_TABLE, window functions, and DEFAULT expressions such as DEFAULT (UUID()) round out the modern feature set.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between a VIRTUAL and a PERSISTENT generated column?

    Virtual columns trade CPU on reads for zero storage; persistent columns do the reverse.
  2. 2. Why create a generated column such as LOWER(email)?

    An index on the generated column makes case-insensitive or JSON-based lookups fast and lets a UNIQUE key apply to the expression.
  3. 3. What does SELECT NEXT VALUE FOR invoice_no return?

    It takes the next value and moves the sequence forward. NEXTVAL() is the function form.
  4. 4. How do you read all historical versions of rows in a system-versioned table?

    A plain SELECT returns only current rows; FOR SYSTEM_TIME ALL, AS OF and BETWEEN query the history.

Summary

  • Generated columns compute values from other columns (VIRTUAL or PERSISTENT) and let you index expressions.
  • CREATE SEQUENCE provides table-independent number generators; the seq_1_to_N tables generate series.
  • WITH SYSTEM VERSIONING keeps row history automatically and FOR SYSTEM_TIME queries the past.
  • Invisible columns, RETURNING and CREATE OR REPLACE are other MariaDB conveniences.

Next: Joins in MariaDB