mysql-How to delete a hash table partition

A large table, previously in the form of a hash partition table,

Mysql> Show CREATE table history_uint;| History_uint |  CREATE TABLE ' history_uint ' (' Itemid ' bigint () unsigned not NULL, ' clock ' int (one-by-one) not null DEFAULT ' 0 ', ' Value ' bigint (20) unsigned NOT NULL default ' 0 ', ' ns ' int (one) not null default ' 0 ', PRIMARY key (' itemid ', ' clock ', ' ns '), key ' I_clock ' (' clock ') Engine=innodb DEFAULT charset=utf8/*!50100 PARTITION by HASH (ITEMID) Partitions 512 * * |

Now we have to remove the partition, how to go?

Check the syntax as follows:

Alter table Table Coalesce partition Num;num refers to the number of table partitions you want to remove

That now has 512 partitions, finally this table I still want, so try to remove 511 partitions to see (of course, before the online operation I have tested in the TEST library!!!) On-line operation should be cautious!!! ）

mysql> Alter table History_uint coalesce partition 511; Query OK, 0 rows affected (4 min 41.71 sec)

　　

View after operation, sure enough, there's only one partition left.

Mysql> SELECT table_name, partition_name, table_rows from INFORMATION_SCHEMA. Partitions WHERE table_name= ' history_uint '; +--------------+----------------+------------+| TABLE_NAME   | Partition_name | Table_rows |+--------------+----------------+------------+| History_uint | P0          |   34909051 |+--------------+----------------+------------+1 row in Set (0.01 sec)

　　

Let's get rid of this partition, and take care to remove it, and drop it, delete the partition and the data together.

mysql> ALTER TABLE history_uint REMOVE partitioning; Query OK, 0 rows affected (3 min 52.38 sec)

　　

Last view table structure

Mysql> Show CREATE table history_uint;| History_uint | CREATE TABLE ' history_uint ' (  ' itemid ' bigint () unsigned not null,  ' clock ' int (one-by-one) not null DEFAULT ' 0 ',  ' VA Lue ' bigint () unsigned NOT null default ' 0 ',  ' ns ' int (one) not null default ' 0 ',  PRIMARY KEY (' itemid ', ' clock ', ' n S '),  KEY ' i_clock ' (' clock ')) Engine=innodb DEFAULT Charset=utf8 |

　　

Table partition disappears, complete.

mysql-How to delete a hash table partition