Mysql分区表------范围分区表（新增、删除、重组、查看）

文章目录

• 一、mysql分区表分类
• • • 范围分区（Range Partitioning
    • 哈希分区（Hash Partitioning）
    • 列表分区（List Partitioning）
    • 键值分区（ Key partition ）
    • 二、范围分区（Range Partitioning
    • • • 1、新建分区表：
        • 2、分区结构查询：
        • 3、根据分区查数据
        • 4、查看分区表的生成文件
        • 5、添加分区：
        • 6、查看分区
        • 三、删除分区表和删除分区：
        • • • 1、直接删除分区表：
            • 2、分区删除语句：
            • 3、分区重组织：

              ---

              一、mysql分区表分类

              范围分区（Range Partitioning

              哈希分区（Hash Partitioning）

              列表分区（List Partitioning）

              键值分区（ Key partition ）

              二、范围分区（Range Partitioning

              Range partition是按照分区表达式的运算结果，判断结果落在某个范围内，从而将数据存储在对应的分区。各个分区定义之间需要连续且不能重叠，范围分区通过partition by range子句定义，而分区的范围通过values less than子句划分。

              ---

              1、新建分区表：

              定义一个员工表，根据员工ID分区，1-10号员工一个分区，11~20号员工一个分区，依次类推，共建立4个分区：

              create table emp (    id int primary key,    name varchar(30),    hire_date date)    partition by range(id)(    partition p0 values less than (11),    partition p1 values less than (21),    partition p2 values less than (31),    partition p3 values less than (41));    现在随便插入几条数据：insert into emp values(1,'Jim','2022-01-02');insert into emp values(2,'Ora','2022-02-02');insert into emp values(11,'Grant','2021-03-02');insert into emp values(16,'Sun','2021-05-02');insert into emp values(22,'Han','2020-10-02');insert into emp values(35,'MJ','2020-11-02');insert into emp values(45,'BABALA','2019-12-02');commit;

              2、分区结构查询：

              show create table test.emp\G;

              Create Table: CREATE TABLE `emp` (  `id` int(11) NOT NULL,  `name` varchar(30) DEFAULT NULL,  `hire_date` date DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4/*!50100 PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (21) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (31) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (41) ENGINE = InnoDB) */1 row in set (0.00 sec)

              表的分区结构中新建的4个分区已新建

              ---

              3、根据分区查数据

              select * from emp partition(p0); – 查询p0分区

              select * from emp partition(p0,p1); – 查询p0和p1分区

              mysql> select * from emp partition(p0,p1);+----+-------+------------+| id | name  | hire_date  |+----+-------+------------+|  1 | Jim   | 2022-01-02 ||  2 | Ora   | 2022-02-02 || 11 | Grant | 2021-03-02 || 16 | Sun   | 2021-05-02 |+----+-------+------------+4 rows in set (0.00 sec)

              根据以上命令可以查询分区的相关数据库信息

              4、查看分区表的生成文件

              [root@mysql5 /]# cd /data/mysql_data/test[root@mysql5 test]# lltotal 400-rw-r-----. 1 mysql mysql    67 Jul 14 09:03 db.opt-rw-r-----. 1 mysql mysql  8626 Jul 14 09:03 emp.frm-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p0.ibd-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p1.ibd-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p2.ibd-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p3.ibd

              由此可见4个分区文件生成

              ---

              5、添加分区：

              alter table emp add partition (partition p4 values less than(51));

              6、查看分区

              mysql> show create table test.emp\G;*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `id` int(11) NOT NULL,  `name` varchar(30) DEFAULT NULL,  `hire_date` date DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4/*!50100 PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (21) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (31) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (41) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (51) ENGINE = InnoDB) */1 row in set (0.00 sec)

              新建分区新增成功

              ---

              三、删除分区表和删除分区：

              1、直接删除分区表：

              drop table emp;

              2、分区删除语句：

              alter table emp drop partition p1;

              mysql> alter table emp drop partition p1;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql>  show create table test.emp\G;*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `id` int(11) NOT NULL,  `name` varchar(30) DEFAULT NULL,  `hire_date` date DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4/*!50100 PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (31) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (41) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (51) ENGINE = InnoDB) */1 row in set (0.00 sec)

              当我们删除p1分区以后，p1分区原来的分区区间定义将由他后面的分区定义区间所继承。而且数据将会被删除。

              ---

              3、分区重组织：

              如果一定要在分区之间插入新的分区，则可以采用重组织的方式，将已有分区的数据重新划分，达到创建新分区的效果：

              例如我要将p2划分为2个分区，分别是11-20,21~30:

              alter table emp reorganize partition p2 into (

              partition p1 values less than(21),

              partition p2 values less than(31));