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)
相关推荐
MySQL5.1新功能-分区
MySQL数据库分区技术,解决多并发问题,详细描述分区流程及操作步骤
mysql存储分区
mysql主从复制与分区技术: 主讲:李健; QQ:89267659; 1、mysql用户授权; 2、mysql bin-log日志; 3、mysql主从复制; 4、mysql分区技术;
MYSQL分区表测试过程详细说明。MYSQL分区表测试过程详细说明。
详细介绍mysql5.1 分区技术,通过对list range hash key四种分区技术的举例阐述Mysql分区,18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分区处理NULL值的...
MySQL分区管理工具
...
。。。
数据库高可用和分区解决方案-MySQL篇.docx
MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 仅供个人学习, 禁止转载及其他商业用途.
用存储过程实现了MySQL数据库分区表的自动创建和自动删除功能。亲测有效。希望有用。
表分区是最近才知道的哦 ,以前自己做都是分表来实现上亿级别的数据了,下面我来给大家介绍一下mysql表分区创建与使用吧,希望对各位同学会有所帮助。表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的...
MySQL 分区有助于增强大型数据库应用的性能和管理 存储过程可提高开发人员效率 触发器可在数据库层面实施复杂的业务规则 视图可确保敏感信息不受攻击 Performance Schema 可监视各个用户/应用的资源占用情况 ...
实现mysql按时间分区方式自动创建与删除分区,包括创建/删除日志记录,通过存储过程与事件联合实现,自动创建数量与删除数量可动态配置
mysql分区入门的详细解释
mysql表分区策略,包含range分区、list分区、hash分区等方法介绍及详解
MySQL分区检查前置条件-- 查看分区插件是否激活 partition -> active-- 查看分区插件是否激活方法2,plugin_status ->
互联网公司为啥不使用mysql分区表