MySQL 表分区（九）

• 对哈希表分区和KEY表分区的管理手段与范围和列表表分区完全不同，比如不能删除表分区，但可以通过ALTER TABLE ... COALESCE PARTITION语句合并表分区，其partition后面的数字代表缩减的个数，而不是缩减到的个数

mysql> CREATE TABLE clients ( id INT, fname VARCHAR(30), lname VARCHAR(30), signed DATE ) 
    -> PARTITION BY HASH( MONTH(signed) ) 
    -> PARTITIONS 12;
Query OK, 0 rows affected (0.93 sec)

mysql> insert into clients values(1,'a','a','2017-01-01'),(2,'a','a','2017-02-01'),(3,'a','a','2017-03-01'),(4,'a','a','2017-04-01'); 
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from clients;
+------+-------+-------+------------+
| id   | fname | lname | signed     |
+------+-------+-------+------------+
|    1 | a     | a     | 2017-01-01 |
|    2 | a     | a     | 2017-02-01 |
|    3 | a     | a     | 2017-03-01 |
|    4 | a     | a     | 2017-04-01 |
+------+-------+-------+------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from clients;
+------+-------+-------+------------+
| id   | fname | lname | signed     |
+------+-------+-------+------------+
|    1 | a     | a     | 2017-01-01 |
|    2 | a     | a     | 2017-02-01 |
|    3 | a     | a     | 2017-03-01 |
|    4 | a     | a     | 2017-04-01 |
+------+-------+-------+------------+
4 rows in set (0.00 sec)

mysql> show create table clients;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| clients | CREATE TABLE `clients` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `signed` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (month(`signed`))
PARTITIONS 8 */ |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE clients ADD PARTITION PARTITIONS 6; 
Query OK, 0 rows affected (0.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table clients;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| clients | CREATE TABLE `clients` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `signed` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (month(`signed`))
PARTITIONS 14 */ |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from clients;
+------+-------+-------+------------+
| id   | fname | lname | signed     |
+------+-------+-------+------------+
|    1 | a     | a     | 2017-01-01 |
|    2 | a     | a     | 2017-02-01 |
|    3 | a     | a     | 2017-03-01 |
|    4 | a     | a     | 2017-04-01 |
+------+-------+-------+------------+
4 rows in set (0.00 sec)