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 .




STORED
VIRTUAL
GENERATED ALWAYS

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’,’sriram@gmail.com’),(‘hercules’,’7sakthi’,’hercules7sakthi@gmail.com’),(‘asha’,’mary’,’ashamary@gmail.com’);
insert into Virtual_partition_test (first_name,email_id) values (‘vijaya’,’vijaya@gmail.com’),(‘durai’,’durai@gmail.com’),(‘jc’,’jc@gmail.com’);





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 !!!