修改mysql hash 分区为range 分区，并删除一个分区

来源：互联网发布：开机自动运行软件编辑：程序博客网时间：2024/05/29 19:40

----将hash 分区修改为range 分区，并删除一个分区
mysql> ALTER TABLE orders_range DROP PARTITION p0;
ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

mysql> show create table orders_range;
+--------------
| Table | Create Table
| orders_range | CREATE TABLE `orders_range` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_surname` varchar(30) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
`salesperson_id` int(11) DEFAULT NULL,
`order_date` date DEFAULT NULL,
`note` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (id) PARTITIONS 4 */ |
+--------------
1 row in set (0.00 sec)

mysql> ALTER TABLE orders_range PARTITION BY RANGE(id) PARTITIONS 4;
ERROR 1492 (HY000): For RANGE partitions each partition must be defined

mysql> ALTER TABLE orders_range PARTITION BY RANGE(id) PARTITIONS 5
-> (
-> PARTITION p0 VALUES LESS THAN(10000),
-> PARTITION p1 VALUES LESS THAN(20000),
-> PARTITION p2 VALUES LESS THAN(30000),
-> PARTITION p3 VALUES LESS THAN(40000),
-> PARTITION p4 VALUES LESS THAN(50000)
-> );
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table orders_range;
+---------------+
| Table | Create Table
| orders_range | CREATE TABLE `orders_range` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_surname` varchar(30) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
`salesperson_id` int(11) DEFAULT NULL,
`order_date` date DEFAULT NULL,
`note` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (10000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (30000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (40000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (50000) ENGINE = InnoDB)

1 row in set (0.00 sec)

mysql> ALTER TABLE orders_range DROP PARTITION p0;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table orders_range\G
*************************** 1. row ***************************
Table: orders_range
Create Table: CREATE TABLE `orders_range` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_surname` varchar(30) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
`salesperson_id` int(11) DEFAULT NULL,
`order_date` date DEFAULT NULL,
`note` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (20000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (30000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (40000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (50000) ENGINE = InnoDB) */
1 row in set (0.00 sec)

1 0