Md. Parvez Alam February 2016

Create partition if not exist on MySQL

I have the following table

id | p_key | col3 | col4
------------------------
 1 | pr1   | c1   | co1  
 2 | pr2   | c2   | co2
 3 | pr3   | c3   | co3

Now I have to create partition on project key, something like

If partition partpr1 not exist
alter table create partition partpr1

I want to create partition with p_key but want to check if it exist or not before inserting record, if it exist it will insert in that partition else it will first create partition then insert.

Not posting any code. because I'm able to create simple partition, but could not getting any logic to implement desired scenario.

Answers


RiggsFolly February 2016

According to the MYSQL Manual

ADD PARTITION and DROP PARTITION do not currently support IF [NOT] EXISTS.

Also your syntax is incorrect anyway,

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

But this page also in The Manual may be useful as it show a number of ways you can query MYSQL and then look at the results to see if a partition with that name already exists.

Via either SHOW commands or by querying the INFORMATION_SCHEMA.PARTITIONS table directly.

Post Status

Asked in February 2016
Viewed 3,210 times
Voted 5
Answered 1 times

Search




Leave an answer