如何删除分区而不删除 MySQL 中的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14192028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to drop partition without dropping data in MySQL?
提问by vidyadhar
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.
现在假设我的表有 200000 行，现在我想删除表上的分区并根据要求重新组织它们，而没有 MAX VALUE 。
Can any one help me to rearrange partition without dropping data or dropping table and recreating it ?
任何人都可以帮助我重新排列分区而不删除数据或删除表并重新创建它吗？
回答by user2643317
ALTER TABLE tbl REMOVE PARTITIONING;
回答by Michel Feldheim
You can reorganize the partition p0using the ALTER TABLE .. REORGANIZE PARTITIONcommand.
您可以p0使用该ALTER TABLE .. REORGANIZE PARTITION命令重新组织分区。
http://dev.mysql.com/doc/refman/5.5/en/partitioning-management-range-list.html
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 ... 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 p2is becoming too large you can split it the same way.
如果你的分区p2变得太大，你可以用同样的方式拆分它。
回答by Rahul Bisht
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
如果要在保留分区的同时重新排列数据，
可以查看 ALTER TABLE
命令的REORGANIZE PARTITION 和 COALESCE PARTITION 子句。
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
回答by Tse Kit Yam
Rearrange partitions doesn't require drop all existing partitions. You can specified the new partitioning in the ALTER TABLEsyntax directly, and no data will be lost.
重新排列分区不需要删除所有现有分区。您可以ALTER TABLE直接在语法中指定新的分区，不会丢失任何数据。
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
PS 用 MySQL 5.7.11 测试
