Create partition if not exist on MySQL

Question

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.

Did you find a way to find, if a particular Partition exists in MySql? Is there any query to find if a certain partition name exists? — Dev1ce, Jun 26 '19 at 06:32

score 2 · Answer 1 · answered Feb 08 '16 at 10:20

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.

is there any way to detect by querying information.schema or any other alternative in case we are using it in combination with php — Muhammad Omer Aslam, Aug 24 '20 at 13:44

Create partition if not exist on MySQL

1 Answers1