mysql查看和新增表分区
1、查看表分区
select
partition_name part,
partition_expression expr,
partition_description descr,
from_days(partition_description) lessthan_sendtime,
table_rows
from
information_schema.partitions
where
table_schema = schema()
and table_name='td_sendmessagelog'; ---这里是表名
2、添加表分区
alter table td_sendmessagelog add partition (
partition p20150210 values less than (to_days('2015-02-10')),
partition p20150220 values less than (to_days('2015-02-20')),
partition p20150301 values less than (to_days('2015-03-01')),
partition p20150310 values less than (to_days('2015-03-10')),
partition p20150320 values less than (to_days('2015-03-20')),
partition p20150401 values less than (to_days('2015-04-01')),
partition p20150410 values less than (to_days('2015-04-10')),
partition p20150420 values less than (to_days('2015-04-20')),
partition p20150501 values less than (to_days('2015-05-01')),
partition pmax values less than (maxvalue)
);
注意：创建表分区，要指定对应的列。上面例子，用的是一个时间列（sendtime）
创建表过程如下（注意指定的primary key）：
create table td_sendmessagelog
(
id int not null,
sendtime datetime not null
primary key (id,sendtime)
)
创建索引：
create index index_sid_sendtime on td_sendmessagelog
(
sid,
sendtime
);