How to add Computed Column / Generate Column in MySQL Table - MySQL Developer Tutorial

How to add Computed Column / Generate Column  in MySQL Table

Computed Column or Generate Column is column which is calculated from exiting columns. Let's say if we have a table called customer with firstname and lastname and we can add Computer Column / Generated Column "fullname" which will be concatenated values for "firstname" and "lastname" columns. 
You can use different operations and functions to write expression for generated columns.

Example :

Below is example of fullname generated column from firstname and lastname columns.

CREATE TABLE `customer` (
   `idcustomer` int not null auto_increment primary key,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL,
  fullname varchar(150) as (concat(firstname,' ',lastname))
) ;


-- Insert some sample Data, Notice we are not inserting FullName 
-- values
insert into customer(firstname,lastname,age,phonenumber,dob,gender)
values
('Raza','Ali',39,'505-4141969','1980-01-01','M'),
('Aamir','Naz',39,'505-4141969','1980-01-01','M'),
('Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
('Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
('Robert','Ladson',69,'505-345900','1960-01-01','M');


Let's select the data from customer table.

Select * from customer;


MySQL Computed Column / MySQL Generate Column


Video demo : Computed Column or Generated Column in MySQL

No comments:

Post a Comment