MYSQL分区技术(二)--分区管理

1、删除分区:
删除分区，数据也没有了
alter table emp_age_range drop partition p1;

mysql> select * from emp_age_range;
+-------+----------+--------+------+
| empno | empname  | deptno | age  |
+-------+----------+--------+------+
| 001   | zhangsan |      1 |    8 |
| 002   | zhang2   |      1 |   15 |
| 003   | zhang3   |      1 |   20 |
+-------+----------+--------+------+
3 rows in set (0.00 sec)

mysql> alter table emp drop partition p1;
ERROR 1146 (42S02): Table 'test.emp' doesn't exist
mysql> alter table emp_age_range drop partition p1;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp_age_range;
+-------+---------+--------+------+
| empno | empname | deptno | age  |
+-------+---------+--------+------+
| 002   | zhang2  |      1 |   15 |
| 003   | zhang3  |      1 |   20 |
+-------+---------+--------+------+
2 rows in set (0.00 sec)

 

不可以删除hash或者key分区。
一次性删除多个分区
alter table emp_birthdate_key drop partition p1;

mysql> show create table  emp_birthdate_key;
+-------------------+------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                        |
+-------------------+----------------------------------------------+
| emp_birthdate_key | CREATE TABLE `emp_birthdate_key` (
  `empno` varchar(20) NOT NULL,
  `empname` varchar(20) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  `birthdate` date NOT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (birthdate)
PARTITIONS 4 */ |
+-------------------+--------------------------------+
1 row in set (0.00 sec)
mysql> alter table emp_birthdate_key drop partition p1;
ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

 

2、删除表的所有分区:--不会丢失数据
alter table emp_age_range remove partitioning;

mysql> alter table emp_age_range remove partitioning;
Query OK, 2 rows affected (0.18 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp_age_range;
+-------+---------+--------+------+
| empno | empname | deptno | age  |
+-------+---------+--------+------+
| 002   | zhang2  |      1 |   15 |
| 003   | zhang3  |      1 |   20 |
+-------+---------+--------+------+
2 rows in set (0.00 sec)
mysql> show create table emp_age_range;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                              |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_age_range | CREATE TABLE `emp_age_range` (
  `empno` varchar(20) NOT NULL,
  `empname` varchar(20) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

分区没有了，数据还存在

 

 

3、重新定义range分区表:----不会丢失数据
alter table emp_age_range partition by range(age)
(
partition p1 values less than (10),
partition p2 values less than (25)
);

mysql> alter table emp_age_range add partition (partition p1 values less than (10));
ERROR 1505 (HY000): Partition management on a not partitioned table is not possible

不是分区表不能add分区
mysql> Alter table emp_age_range partition by range(age)
    -> (
    -> partition p1 values less than (10),
    -> partition p2 values less than (20)
    -> );
ERROR 1526 (HY000): Table has no partition for value 20

分区不能盛放已经有的值
mysql> Alter table emp_age_range partition by range(age) ( partition p1 values less than (10), partition p2 values less than (25) );
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0

 

4、增加分区:
alter table emp_age_range add partition (partition p1 values less than (30));
alter table emp_age_range add partition (partition p3 values in (40));
mysql> alter table emp_age_range add partition (partition p3 values less than (30));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp_age_range;
+---------------+--------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                  |
+---------------+----------------------------------------------------------------------------------------------------+
| emp_age_range | CREATE TABLE `emp_age_range` (
  `empno` varchar(20) NOT NULL,
  `empname` varchar(20) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (age)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (25) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (30) ENGINE = InnoDB) */ |
+---------------+-------------------------------------------------------------+
1 row in set (0.00 sec)