PHP Developer News

MySQL Partition over the Virtual / Generated Column

                Had an interesting requirement from one of our client to have the two MySQL partitions ( partition_full / partition_half ) for store the Names based on the user input.

Requirement :

The table have two columns first_name and last_name. With the user input of both columns, it needs to be automatically compute the data for another column full_name . And, the status needs be consider as “FULL” .
If the column last_name don’t have the input from the user, then the first_name data needs to be compute as the full_name . And, the status needs be considered as “HALF” .
Need the separate partitions for both status HALF and FULL

We can achieve this with the help of Virtual / Generated columns and LIST partition . In this blog, I am going to explain the complete steps which I followed to achieve this .

What is Virtual Column ?

                Virtual columns are the generated columns because the data set for these columns will be computed based on the predefined column structure . Below are the three types we can generate the virtual columns .


Here is the detailed blog post from Mydbops Team, which contains the nice details about Virtual columns .

MySQL partition with Virtual / Generated columns

Step 1  –  ( Creating the table with virtual columns )

cmd : create table Virtual_partition_test (id int(11) not null auto_increment primary key, first_name varchar(16), last_name varchar(16) default 0, full_name varchar(32) as (case last_name when ‘0’ then first_name else concat(first_name,’ ‘,last_name) end) stored, name_stat varchar(7) as (case full_name when concat(first_name,’ ‘,last_name) then ‘full’ else ‘half’ end) stored, email_id varchar(16));

full_name  – for compute the data set from columns firstname and lastname .
name_stat – for compute the name status from columns firstname and lastname .

Step 2 –  ( Testing the virtual/generated column behaviour ) 

cmd :
insert into Virtual_partition_test (first_name,last_name,email_id) values (‘sri’,’ram’,’’),(‘hercules’,’7sakthi’,’’),(‘asha’,’mary’,’’);
insert into Virtual_partition_test (first_name,email_id) values (‘vijaya’,’’),(‘durai’,’’),(‘jc’,’’);

Yes, I have created 3 FULL and 3 HALF names .

The above result set, illustrates that the virtual/generated column is working perfectly as expected .

Step 3 –  ( Adding the partition key  )

It is important to have the partition column as the part of PRIMARY KEY .

cmd : alter table Virtual_partition_test drop primary key, add primary key (id,name_stat);

Step 4 – ( Configuring the partition )

cmd :
alter table Virtual_partition_test partition by listcolumns(name_stat)(partition partition_full values in (‘FULL’) engine=InnoDB,partition partition_half values in (‘HALF’) engine=InnoDB);

Partitions has been added as per the requirement .

more informations,

Hope this blog will help someone who is looking the partitions over the virtual / generated columns .

Thanks !!!