View and add table partitions in MYSQL, and add partitions in mysql

View and add table partitions in MYSQL, and add partitions in mysql

View and add table partitions in MYSQL

1. View table partitions

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 '; --- here is the table name

2. Add Table partitions

Alter table td_sendmessagelog add partition (
PARTITION p20150210 values less than (TO_DAYS ('2017-02-10 ')),
PARTITION p20150220 values less than (TO_DAYS ('2017-02-20 ')),
PARTITION p20150301 values less than (TO_DAYS ('2017-03-01 ')),
PARTITION p20150310 values less than (TO_DAYS ('2017-03-10 ')),
PARTITION p20150320 values less than (TO_DAYS ('2017-03-20 ')),
PARTITION p20150401 values less than (TO_DAYS ('2017-04-01 ')),
PARTITION p20150410 values less than (TO_DAYS ('2017-04-10 ')),
PARTITION p20150420 values less than (TO_DAYS ('2017-04-20 ')),
PARTITION p20150501 values less than (TO_DAYS ('2017-05-01 ')),
PARTITION pmax values less than (maxvalue)
);

Note: to create a table partition, you must specify the corresponding columns. In the preceding example, a time column (sendtime) is used)

The table creation process is as follows (note the specified primary key ):

Create table td_sendmessagelog
(
Id int not null,
Sendtime datetime not null
Primary key (id, sendtime)
)

Create an index:

Create index Index_sid_sendtime on td_sendmessagelog
(
Sid,
Sendtime
);