MySQL Generated Columns

The generated column feature, which has been available since MySQL 5.7, enables the generation of some columns’ data based on predefined phrases. This is comparable to calculated columns in SQL Server.

Based on the expressions, the values in the computed columns will be produced automatically. There is no need for user input. In the actual CREATE TABLE statement, it will be defined.

In this post, we’ll look at how to use generated columns in MySQL.

Example-1:
DROP TABLE IF EXISTS tblEmployee;

CREATE TABLE tblEmployee(
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
middle_name VARCHAR(50),
last_name VARCHAR(50) NOT NULL,
fullName varchar(100) GENERATED ALWAYS AS (CONCAT(first_name,’ ‘,IFNULL(middle_name, “”),’ ‘,last_name)),
Address VARCHAR(100),
City VARCHAR(100),
Country VARCHAR(100)
);

INSERT INTO tblEmployee (id, first_name, middle_name, last_name, address, city, country) VALUES (1001,’Zafar’,”,’Iqbal’,’DLRC’,’Dubai’,’UAE’);
INSERT INTO tblEmployee (id, first_name, middle_name, last_name, address, city, country) VALUES (1002,’Tahir’,NULL,’Iqbal’,’DLRC’,’Dubai’,’UAE’);
INSERT INTO tblEmployee (id, first_name, middle_name, last_name, address, city, country) VALUES (1003,’Hadi’,’Zafar’,’Iqbal’,’DLRC’,’Dubai’,’UAE’);

SELECT * FROM tblEmployee;

Example-2:
DROP TABLE IF EXISTS tbBalance;

CREATE TABLE tbBalance(
Id INT,
TrxnDate DATE,
dcBeginBalance DECIMAL(9,2),
dcTrxnAmount DECIMAL(9,2),
dcEndBalance DECIMAL(9,2) AS (dcBeginBalance-dcTrxnAmount)
);

INSERT INTO tbBalance(Id, TrxnDate, dcBeginBalance, dcTrxnAmount) VALUES
(1001, ‘2022-10-01’,25400, 120),
(1002, ‘2022-10-02’,21400, 100),
(1003, ‘2022-10-03’,22144, 124),
(1004, ‘2022-10-04’,20400, 1020);

SELECT * FROM tbBalance;

Virtual & Stored Columns:

The VIRTUAL or STORED keyword indicates how column values are stored.

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage. The default is VIRTUAL if neither keyword is specified.
STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.

Example-3:
Virtual Column:
DROP TABLE IF EXISTS tbBalance;
CREATE TABLE tbBalance(
Id INT,
TrxnDate DATE,
dcBeginBalance DECIMAL(9,2),
dcTrxnAmount DECIMAL(9,2),
dcEndBalance DECIMAL(9,2) AS (dcBeginBalance-dcTrxnAmount) VIRTUAL
);

INSERT INTO tbBalance(Id, TrxnDate, dcBeginBalance, dcTrxnAmount) VALUES
(1001, ‘2022-10-01’,2540000, 12000),
(1002, ‘2022-10-02’,2140000, 10000),
(1003, ‘2022-10-03’,2214400, 12400),
(1004, ‘2022-10-04’,2040000, 10200),
(1005, ‘2022-10-04’,2111000, 10200),
(1006, ‘2022-10-05’,2223000, 10200),
(1007, ‘2022-10-05’,2534000, 10200),
(1008, ‘2022-10-05’,2311200, 10200);

Example-4:
Stored Column:
DROP TABLE IF EXISTS tbBalance2;

CREATE TABLE tbBalance2(
Id INT,
TrxnDate DATE,
dcBeginBalance DECIMAL(9,2),
dcTrxnAmount DECIMAL(9,2),
dcEndBalance DECIMAL(9,2) AS (dcBeginBalance-dcTrxnAmount) STORED
);

INSERT INTO tbBalance2(Id, TrxnDate, dcBeginBalance, dcTrxnAmount) VALUES
(1001, ‘2022-10-01’,2540000, 12000),
(1002, ‘2022-10-02’,2140000, 10000),
(1003, ‘2022-10-03’,2214400, 12400),
(1004, ‘2022-10-04’,2040000, 10200),
(1005, ‘2022-10-04’,2111000, 10200),
(1006, ‘2022-10-05’,2223000, 10200),
(1007, ‘2022-10-05’,2534000, 10200),
(1008, ‘2022-10-05’,2311200, 10200);