Generated Columns Mysql

 A generated column is similar to a normal column, but you cannot change its value

manually. This is because an expression

defines how to generate the value of a generated column based on the other values

that are read from the other columns of the same row.

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(100) NOT NULL,

first_name VARCHAR(60) NOT NULL,

last_name VARCHAR(60) NOT NULL,

full_name varchar(120) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name))

);

it is virtaul by default.

you can define a generated column as VIRTUAL or STORED

VIRTUAL Generated Columns:

MySQL does not store a generated column marked as VIRTUAL.

This means that MySQL evaluates its value on the fly when

required. This typically happens immediately after any

BEFORE query is triggered. In other terms, a virtual

generated column takes up no storage space. MySQL does not store a generated column marked as VIRTUAL.

This means that MySQL evaluates its value on the fly when

required. This typically happens immediately after any

BEFORE query is triggered. In other terms, a virtual

generated column takes up no storage space.

e.g.

CREATE TABLE usersvirtual (

id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(100) NOT NULL,

first_name VARCHAR(60) NOT NULL,

last_name VARCHAR(60) NOT NULL,

full_name varchar(120) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL

);

e.g.

CREATE TABLE usersvirtual (

id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(100) NOT NULL,

first_name VARCHAR(60) NOT NULL,

last_name VARCHAR(60) NOT NULL,

full_name varchar(120) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL

);

MySQL has to evaluate them when reading a table, making SELECT queries involving them slower.

Stored Generated Columns

MySQL stores any generated column marked as STORED.

This means that MySQL takes care of evaluating its

value and storing it on the disk every time you

insert or update a row. In other terms, a stored

column requires storage space as if it was a normal column.

e.g.

CREATE TABLE usersstored (

id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(100) NOT NULL,

first_name VARCHAR(60) NOT NULL,

last_name VARCHAR(60) NOT NULL,

full_name varchar(120) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED

);

INSERT or UPDATE comes with an overhead because MySQL has to generate them.