Need help to create auto increment partition based on date

[Tanmoy Moulik]

Dear Team,

I am new to mysql.I have been exploring possibility to create partition based on date.So did came across key partition case be used with date field ,however in such case number of partition is fixed.I have shared the sample sql.

create table test_date_partition
(name varchar(100),
dept varchar(100),
salary integer,
join_date date not null)
partition by key(join_date)
partitions 6;


However, I want partition in which partition should auto increment and need basis partition with old date can be dropped.Is there any method or solution to solve this issue?

Thanks in Advance!!!

Regards,
Tanmoy Moulik