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

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)