MySQL创建分区方法,及分区中常用SQL示例

本文总结如何通过sql创建表分区,查看分区,以及日常如何使用分区表。

通过sql语句创建

mysql > CREATE TABLE `database_test`.`table_test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `create_time` datetime(0) NOT NULL,
  PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(create_time))(
    PARTITION partition1 VALUES LESS THAN (2020),
    PARTITION partition2 VALUES LESS THAN (2021)
);

查看分区情况:

mysql> SELECT PARTITION_NAME
        ,PARTITION_METHOD
        ,PARTITION_EXPRESSION
        ,PARTITION_DESCRIPTION
        ,TABLE_ROWS
        ,SUBPARTITION_NAME,SUBPARTITION_METHOD
    FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='your_table';

分区中常用SQL示例
1、查询指定分区内的数据:

mysql>  select * from `your_table` partition(your_partition_name);

 2、对已存在的表进行分区

mysql> ALTER TABLE `your_table` PARTITION by HASH(YEAR(`day`)) PARTITIONS 10;

3、 删除表的所有分区

mysql>  ALTER TABLE `your_table` REMOVE PARTITIONING;


4、删除表的特定分区

mysql> ALTER TABLE `your_table` DROP PARTITION your_partition_name;


5、 删除表单个分区内的数据

mysql >  ALTER TABLE `your_table` TRUNCATE PARTITION your_partition_name;

6、把分区内的数据复制到另一张表

mysql> INSERT INTO `your_new_table` SELECT * FROM `your_old_table` PARTITION(your_partition_name);

7、新增分区

mysql> alter table `your_table` add partition(partition your_partition_name values less than(2021));


8、原表已有主键,需要新增复合主键,则需要先丢弃再重新设置主键。

mysql> ALTER TABLE `your_database`.`your_table` 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `login_time`) USING BTREE;

9、查询分区信息。

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,
PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='your_table';

10、原表设置了MAXVALUE分区,若想添加新分区又不丢失数据,则需要重新分区。

Alter table `your_table` partition by RANGE(YEAR(login_time))(
partition p1 values less than (2014),
partition p2 values less than (2015),
partition p3 values less than (2016),
partition p4 values less than (2019),
partition p5 values less than (MAXVALUE)
);

  • 1
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一个高效工作的家伙

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值