创建mysql分区表及操作

最新推荐文章于 2024-10-14 09:51:30 发布

枯_叶

最新推荐文章于 2024-10-14 09:51:30 发布

阅读量506

点赞数

分类专栏： MySQL 文章标签： mysql 分区表

本文链接：https://blog.csdn.net/chinac6iz/article/details/41550455

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

/*创建分区表按月份分区*/

create table o_order(id int auto_increment comment '序号', 
orderid varchar(200) comment '订单号',
account varchar(50) comment '帐号', 
status int comment '状态 0进行中 1成功 2失败', 
addtime datetime comment '添加时间', 
rvctime datetime comment '完成时间', 
remark varchar(200) comment '备注',
primary key (id,addtime)
)engine=innodb partition by range(to_days(addtime)) 
( partition p201411 values less than (to_days('2014-11-01')), 
partition p201412 values less than (to_days('2014-12-01')), 
partition p201501 values less than (to_days('2015-01-01')),
partition p201502 values less than (to_days('2015-02-01')), 
partition p201503 values less than (to_days('2015-03-01')));

添加分区

ALTER TABLE o_order ADD PARTITION (PARTITION  p201504 values less than (to_days('2015-04-01')));

删除分区

ALTER TABLE o_order DROP PARTITION p201504;

-- 查看分区表信息

SHOW CREATE TABLE o_order;
SHOW TABLE STATUS LIKE 'o_order';

SELECT   
        partition_name part,    
        partition_expression expr,    
        partition_description descr,    
        table_rows    
FROM   
        INFORMATION_SCHEMA.partitions    
WHERE   
        TABLE_SCHEMA = schema()   
        AND TABLE_NAME='表名';