MySQL Generated Columns

Summary: in this tutorial, you will learn what a generated column is and how to use generated columns in MySQL with the help of examples.

A generated column defines the types of columns where you do not require to insert any data. The data is computed based on expression or from the other columns.

Starting from MySQL version 5.7, the concept of the generated column was introduced in MySQL. One can create the generated column using the CREATE TABLE and the ALTER TABLE statement. It is a way to store data without sending the actual insert or update command to the table. The data will be managed by MySQL itself based on the expression defined for the generated columns.

MySQL Generated Columns Syntax

The following are the generic syntax of defining generated columns in MySQL.

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

Let us understand the above syntax in detail.

First, specify the column name and its data type.

Next, the GENERATED ALWAYS keywords indicate that the column is a generated column.

Then, specify if the generated column is virtual or stored. By default, MySQL uses VIRTUAL if you don’t specify explicitly the type of the generated column.

After that, specify the expression within the braces after the AS keyword. The expression can contain literals, built-in functions with no parameters, operators, or references to any column within the same table. If you use a function, it must be scalar and deterministic.

Finally, If the generated column is STORED type, then you can define the UNIQUE KEY on the column.

MySQL Generated Columns Example

Let us understand the concept of MySQL generated columns with the help of some examples.

First, create a students table as follows.

CREATE TABLE students (
roll_no int AUTO_INCREMENT PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50),
student_age int NOT NULL,
student_email varchar(255)
);

Now insert some data into it.

INSERT INTO students (first_name,last_name,student_age,student_email) values ('Ram','Meena',23,'ram.meena@gmail.com');
INSERT INTO students (first_name,last_name,student_age,student_email) values ('Sagar','Sharma',22,'sagar99@gmail.com');
INSERT INTO students (first_name,last_name,student_age,student_email) values ('Priyankit','Jha',21,'pryankit@hotmail.com');

Check the data of the students tables using the SELECT statement.

SELECT * FROM Student;

Output:

mysql generated columns

Now, if you want to select the full name along with the other details, the query will be like as below.

SELECT 
first_name,
last_name,
concat(first_name,' ',last_name) as full_name,
student_age,
student_email
FROM students;

Output:

mysql> SELECT
    -> first_name,
    -> last_name,
    -> concat(first_name,' ',last_name) as full_name,
    -> student_age,
    -> student_email
    -> FROM students;
+------------+-----------+---------------+-------------+----------------------+
| first_name | last_name | full_name     | student_age | student_email        |
+------------+-----------+---------------+-------------+----------------------+
| Ram        | Meena     | Ram Meena     |          23 | ram.meena@gmail.com  |
| Sagar      | Sharma    | Sagar Sharma  |          22 | sagar99@gmail.com    |
| Priyankit  | Jha       | Priyankit Jha |          21 | pryankit@hotmail.com |
+------------+-----------+---------------+-------------+----------------------+
3 rows in set (0.00 sec)

An alternative way to achieve this is by using the generated columns. In this case, we can recreate the table as below.

CREATE TABLE students (
roll_no int AUTO_INCREMENT PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50),
full_name varchar(100) GENERATED ALWAYS AS (concat(first_name,' ',last_name)) stored,
student_age int NOT NULL,
student_email varchar(255)
);

Now insert the data again,

INSERT INTO Students (first_name,last_name,student_age,student_email) values ('Ram','Meena',23,'ram.meena@gmail.com');
INSERT INTO Students (first_name,last_name,student_age,student_email) values ('Sagar','Sharma',22,'sagar99@gmail.com');
INSERT INTO Students (first_name,last_name,student_age,student_email) values ('Priyankit','Jha',21,'pryankit@hotmail.com');

Finally, if we execute the SELECT statement on the students table, you can see the following output.

mysql generated columns

We can also define the generated columns using the ALTER TABLE statement.

ALTER TABLE students
ADD COLUMN full_name VARCHAR(100)
AS (CONCAT(first_name,' ',last_name));