mysql分区表

标签: sql  分区表  mysql  sql

1.新建普通表
CREATE TABLE human (age int, sex int);
2.插入数据
INSERT INTO human VALUES (5,1), (15, 0), (24, 1), (44, 1), (32, 0), (64, 1), (84, 1), (54, 0);
3.建立分区表
CREATE TABLE human_fq (age int, sex int)
       PARTITION BY RANGE (age) (
       PARTITION p0 VALUES LESS THAN (20),
       PARTITION p1 VALUES LESS THAN (40),
       PARTITION p2 VALUES LESS THAN (60),
       PARTITION p3 VALUES LESS THAN (80),
       PARTITION p4 VALUES LESS THAN MAXVALUE
       );  
4.插入数据至分区表
INSERT INTO human_fq SELECT * FROM human ;
5.查看分区表情况
SELECT table_name,partition_name,partition_description,table_rows FROM
information_schema.`PARTITIONS` WHERE table_name = 'human_fq';

在这里插入图片描述

6.查看分区查询数据实际使用情况
EXPLAIN  partitions   SELECT * FROM human_fq WHERE age= 20;

在这里插入图片描述

7.删除分区
alter table human_fq drop partition p0;

alter table human_fq drop partition p1,p2;
 -- 删除分区会直接删除数据,请谨慎。
 因为该类型是范围分区,如果要删除可从最大往最小删,且不能删除全部分区。
 
8.重置分区

alter table human_fq reorganize partition p0 into (partition p0 values less than (10),partition p1 values less than(20));
-- 如原分区p0 范围0-20 则重置的总范围也必须是该范围,分区数则不限


版权声明:本文为qq_37980436原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_37980436/article/details/105994867