MySQL Generated Column with CASE Expression

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 a CASE expression to generate a column in MySQL while creating the table.

Example:
DROP TABLE IF EXISTS tbOrdersDetails;

CREATE TABLE tbOrdersDetails(
iOrderID INT,
OrderDate DATE,
isOrderAccepted BIT,
DeliveredDate DATE,
`shipmentStatus` ENUM ( ‘OPEN’, ‘CLOSED’ ) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci GENERATED ALWAYS AS (
(
CASE
WHEN ((
`DeliveredDate` IS NULL
)
AND ((
`isOrderAccepted` IS NULL
)
OR ( `isOrderAccepted` = ” ))) THEN
‘OPEN’ ELSE ‘CLOSED’
END
)) STORED);

Now let’s insert some dummy data into the table.
INSERT INTO tbOrdersDetails (iOrderID, OrderDate, isOrderAccepted, DeliveredDate) VALUES (10001, ‘2022-10-01′,1,’2022-10-05’);
INSERT INTO tbOrdersDetails (iOrderID, OrderDate, isOrderAccepted, DeliveredDate) VALUES (10001, ‘2022-10-01’,NULL,NULL);

Let’s retrieve the records and see how the data is stored for the generated column.
SELECT * FROM tbOrdersDetails;

Screenshot to display the outcome:

MySQLGeneratedColumn_Post-2