30

I have a table like:

create table registrations( 
id int not null auto_increment primary key,
name varchar(50),
mobile_number varchar(13)) 
engine=innodb 
partition by range(id) (
partition p0 values less than (10000),
partition p0 values less than (20000),
partition p0 values less than max value);

Not exactly like above but similar to that....

Now assume that my table has 200000 rows and now I want to remove partitions on the table and reorganize them in accordance to requirement without MAX VALUE in it.

Can any one help me to rearrange partition without dropping data or dropping table and recreating it ?

JohnGalt
  • 32
  • 3
vidyadhar
  • 3,118
  • 6
  • 22
  • 31

4 Answers4

123
ALTER TABLE tbl REMOVE PARTITIONING;

Source: ALTER TABLE Statement in MySQL Reference Manual

BinaryButterfly
  • 18,137
  • 13
  • 50
  • 91
user2643317
  • 1,239
  • 1
  • 8
  • 3
  • can confirm, this will remove the partitions from your mysql table without any data loss. It will take some time as the table needs to be rebuilt (similar time to optimize/repair) – Luke Rehmann Apr 14 '22 at 22:40
  • MYSql document exact link confirming the same here https://dev.mysql.com/doc/refman/5.6/en/alter-table-partition-operations.html#:~:text=REMOVE%20PARTITIONING%20enables%20you%20to%20remove%20a%20table%27s%20partitioning%20without%20otherwise%20affecting%20the%20table%20or%20its%20data.%20This%20option%20can%20be%20combined%20with%20other%20ALTER%20TABLE%20options%20such%20as%20those%20used%20to%20add%2C%20drop%2C%20or%20rename%20columns%20or%20indexes. – srth12 Jul 07 '22 at 13:57
6

You can reorganize the partition p0 using the ALTER TABLE .. REORGANIZE PARTITION command.

http://dev.mysql.com/doc/refman/5.5/en/partitioning-management-range-list.html

If you intend to change the partitioning of a table without losing data, use ALTER TABLE ... REORGANIZE PARTITION

ALTER TABLE registrations 
REORGANIZE PARTITION p0 INTO (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p0 VALUES LESS THAN (20000)
);

Note that this will not make sense until you actually create several partitions e.g.

ALTER TABLE registrations 
REORGANIZE PARTITION p0 INTO (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Have a look at RANGE partitioning in MySQL

If your partition p2 is becoming too large you can split it the same way.

Michel Feldheim
  • 17,625
  • 5
  • 60
  • 77
1

Rearrange partitions doesn't require drop all existing partitions. You can specified the new partitioning in the ALTER TABLE syntax directly, and no data will be lost.

ALTER TABLE registrations
PARTITION by RANGE(id) (
PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p3 VALUES LESS THAN (30000),
PARTITION p4 VALUES LESS THAN (40000),
PARTITION p5 VALUES LESS THAN (MAXVALUE);

P.S. Tested with MySQL 5.7.11

Tse Kit Yam
  • 173
  • 8
0

If you want to rearrange the data while keeping the partitions,
you can take a look at REORGANIZE PARTITION and COALESCE PARTITION clauses of ALTER TABLE
command.
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Rahul Bisht
  • 482
  • 3
  • 5