Mysql 分区常用sql
文章目录
查询需求语句
SELECT SQL_NO_CACHE count(*) sum, award_id
FROM `yx_sweepstake`
WHERE `time` BETWEEN 1508889500 AND 1510047200 GROUP BY award_id;
查询分区情况
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,
PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,
SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='yx_sweepstake';
删除分区
ALTER TABLE yx_sweepstake REMOVE PARTITIONING;
添加范围分区
ALTER TABLE yx_sweepstake PARTITION BY RANGE(time div 100000)(
PARTITION p15089 VALUES LESS THAN (15089),
PARTITION p15090 VALUES LESS THAN (15090),
PARTITION p0 VALUES LESS THAN (MAXVALUE)
);
追加分区
ALTER TABLE yx_sweepstake PARTITION by range(time div 100000)(
partition par0 values less than (15089)
);
评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论