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.
- 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 .
- 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 )
insert into Virtual_partition_test (first_name,last_name,email_id) values (‘sri’,’ram’,’firstname.lastname@example.org’),(‘hercules’,’7sakthi’,’email@example.com’),(‘asha’,’mary’,’firstname.lastname@example.org’);
insert into Virtual_partition_test (first_name,email_id) values (‘vijaya’,’email@example.com’),(‘durai’,’firstname.lastname@example.org’),(‘jc’,’email@example.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 )
alter table Virtual_partition_test partition by list
(partition partition_full values in (‘FULL’) engine=InnoDB,
partition partition_half values in (‘HALF’) engine=InnoDB);
Partitions has been added as per the requirement .
Hope this blog will help someone who is looking the partitions over the virtual / generated columns .