MySQL partition table

Partition function
CREATE definer= ' root ' @ '% ' FUNCTION ' query_str ' () RETURNS varchar (+) CHARSET UTF8
BEGIN
DECLARE start_date date DEFAULT ' 2018-05-17 ';
DECLARE res_str varchar (+) DEFAULT ';
While start_date< ' 2018-06-16 ' do
Set res_str = Concat (res_str, ' PARTITION p ', Date_format (Date_sub (start_date,interval 1 day), '%y%m%d '), ' VALUES less THAN (To_days ("', Start_date, ') ') ', ', ');
Set start_date = Date_add (start_date,interval 1 day);
END while;
RETURN Res_str;
END

Auto-Partitioning events
DELIMITER $$

CREATE EVENT ' memberorder_partition ' on SCHEDULE every 1 day starts ' 2016-11-04 23:30:01 ' on completion not PRESERVE Disab LE on SLAVE COMMENT ' partition table Memberorder maintenance Schedule 1. Daily 23:30 create next day partition ' do BEGIN
/*start auto_add_partition*/
SET @auto_add_sql = CONCAT (
"ALTER TABLE ' Memberorder ' Add partition (partition P",
Date_format (Date_add (Current_date (), INTERVAL 1 day), '%y%m%d '),
"Values less than (To_days ('",
Date_add (Current_date (), INTERVAL 2 day),
"‘)))"
) ;
PREPARE Stmt_add from @auto_add_sql;
EXECUTE Stmt_add;
/*end auto_add_partition*/

end$$

DELIMITER;

Create a new partition table
CREATE TABLE ' memberorder_tmp ' (
' Id ' bigint (unsigned) not NULL auto_increment,
' MemberID ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' Merchant number ',
' OrderId ' varchar (CHARACTER SET utf32 not NULL DEFAULT ' COMMENT ' System order Number _ platform ',
' Orderstatus ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' order status ',
' Orderstatustitile ' varchar (a) Not NULL DEFAULT ' COMMENT ' order status description ',
' Ordertime ' datetime not NULL DEFAULT ' 2000-01-01 00:00:00 ' COMMENT ' Order time ',
' OrderAmount ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' Order Amount ',
' Orderamountfee ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' platform profit-actual profit includes splitting ',
' Ordermoneyfee ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' platform profit-does not include splitting downstream handling fee-upstream cost ',
' Orderamountrate ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' platform profit ratio ',
' Ordernotifyurl ' varchar (255) Not NULL DEFAULT ' COMMENT ' platform charge upstream callback address ',
' Merchantchannelrate ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' Merchant rate ',
' Merchantsharpprofit ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' merchant splitting ',
' Topamountfee ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' upstream charge platform rate amount-handling fee ',
' Topsharpprofitfee ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' upstream splitting amount ',
' Channelpaytyperate ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' upstream rate ',
' Channelsharpprofit ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' channel upstream splitting ',
' Lowamountfee ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' platform charge merchant rate amount-handling fee ',
' Lowsharpprofitfee ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' downstream splitting amount ',
' Orderiscomplete ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' order completed ',
' Paytypeid ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' payment method number ',
' Channelid ' int (one) not NULL COMMENT ' channel number ',
' Channelpayid ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' channelpaytype payment channel number ',
' Toporderid ' varchar (+) not NULL DEFAULT ' COMMENT ' channel order number _ upstream ',
' Merchantorderid ' varchar (+) not NULL DEFAULT ' COMMENT ' merchant order number _ downstream ',
' Merchantordertime ' datetime not NULL DEFAULT ' 2000-01-01 00:00:00 ' COMMENT ' Merchant submits order time ',
' Merchantnotifyurl ' varchar ($) Not NULL DEFAULT ' COMMENT ' Merchant callback address ',
' Merchanthttpjson ' text not NULL COMMENT ' merchant Request packet ',
' Istopnotify ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' Do you receive an upstream callback ',
' Istopnotifyrespond ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' whether it responds to upstream callback ',
' topnotify ' text not NULL COMMENT ' upstream callback content ',
' Topnotifytime ' datetime not NULL DEFAULT ' 2000-01-01 00:00:00 ' COMMENT ' upstream callback time ',
' Islownotify ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' send downstream callback ',
' Islownotifyrespond ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' receive downstream response callback ',
' lownotify ' text not NULL COMMENT ' downstream callback content ',
' Lownotifytime ' datetime not NULL DEFAULT ' 2000-01-01 00:00:00 ' COMMENT ' downstream callback send time ',
' Channelstatementype ' int (one) not NULL DEFAULT ' 1 ' COMMENT ' channel settlement type 1-t1 2-t0 3-d1 4-d0 ',
' Issettlementrespond ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' is the settlement executed ',
' Isbillsettlement ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' whether to complete billing settlement ',
' Settlementtype ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' settlement type 0-not settled 1-t1 2-t0 3-d1 4-d0 ',
' Settlementamountfee ' decimal (11,2) not NULL DEFAULT ' 0.00 ' COMMENT ' Bill settlement amount ',
' Settlementtime ' datetime not NULL DEFAULT ' 2000-01-01 00:00:00 ' COMMENT ' Bill settlement Time ',
' Settlementdayid ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' Settlement day table number ',
' Settlementmonthid ' int (one) not NULL COMMENT ' Settlement month table number ',
' Settlementyearid ' int (one) not NULL COMMENT ' settlement chronology number ',
' SalesPersonID ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' salesman number ',
' Salesmandayid ' int (one) not NULL COMMENT ' Settlement Clerk Day table ',
' Conditiontablekey ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' minute table key ',
' Exceptionstatus ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' is exception 0-no exception 1-payment exception 2-callback exception ',
' Exceptiontitle ' varchar (255) Not NULL DEFAULT ' COMMENT ' exception information ',
' Remark ' varchar (255) Not NULL DEFAULT ' COMMENT ' order notes ',
' CreateUser ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' creator ',
' Createtime ' datetime not NULL DEFAULT ' 2000-01-01 00:00:00 ' COMMENT ' creation time ',
' ModifyUser ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' modifier number ',
' Modifytime ' datetime not NULL DEFAULT ' 2000-01-01 00:00:00 ' COMMENT ' Modify Time ',
' IsValid ' tinyint (1) Not NULL DEFAULT ' 1 ' COMMENT ' is valid ',
' Isdel ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' is deleted ',
PRIMARY KEY (' Id ', ' createtime ') USING BTREE,
UNIQUE KEY ' id ' (' id ', ' createtime ') USING BTREE
) Engine=innodb auto_increment=142473 DEFAULT Charset=utf8 row_format=dynamic
PARTITION by RANGE (To_days (createtime))
(
PARTITION p20180517 values less THAN (to_days (' 2018-05-18 ')), PARTITION p20180518 values less THAN (to_days (' 2018-05-19 ') ), PARTITION p20180519 values less THAN (to_days (' 2018-05-20 ')), PARTITION p20180520 values less THAN (to_days (' 2018-05-21 '), PARTITION p20180521 values less THAN (to_days (' 2018-05-22 ')), PARTITION p20180522 values less THAN (to_days (' 2018-05-23 '), PARTITION p20180523 values less THAN (to_days (' 2018-05-24 ')), PARTITION p20180524 values less THAN (to_days (' 2018-05-25 '), PARTITION p20180525 values less THAN (to_days (' 2018-05-26 ')), PARTITION p20180526 values less THAN (to_ Days (' 2018-05-27 '), PARTITION p20180527 values less THAN (to_days (' 2018-05-28 ')), PARTITION p20180528 values less THAN ( To_days (' 2018-05-29 '), PARTITION p20180529 values less THAN (to_days (' 2018-05-30 ')), PARTITION p20180530 values Less THAN (to_days (' 2018-05-31 ')), PARTITION p20180531 values less THAN (to_days (' 2018-06-01 ')), PARTITION p20180601 values Less THAN (to_days (' 2018-06-02 ')), PARTITION p20180602 VALUES less THAN (to_days (' 2018-06-03 's)), PARTITION p20180603 VALUES less THAN (to_days (' 2018-06-04 ')), PARTITION p20180604 Values less THAN (to_days (' 2018-06-05 's)), PARTITION p20180605 VALUES less THAN (to_days (' 2018-06-06 ')), PARTITION p20180606 values less THAN (to_days (' 2018-06-07 ')), PARTITION p20180607 values less THAN (to_days (' 2018-06-08 ')), PARTITION p20180608 values less THAN (to_days (' 2018-06-09 ')), PARTITION p20180609 values less THAN (to_days (' 2018-06-10 ') ), PARTITION p20180610 values less THAN (to_days (' 2018-06-11 ')), PARTITION p20180611 values less THAN (to_days (' 2018-06-12 '), PARTITION p20180612 values less THAN (to_days (' 2018-06-13 ')), PARTITION p20180613 values less THAN (to_days (' 2018-06-14 '), PARTITION p20180614 values less THAN (to_days (' 2018-06-15 ')), PARTITION p20180615 values less THAN (to_days (' 2018-06-16 '), PARTITION p20180616 VALUES less THAN (to_days (' 2018-06-17 '))
)

Rename Table name
RENAME TABLE Memberorder to Memberorderhis,memberorder_tmp to Memberorder;

View Table Partitioning conditions
SELECT Partition_name,table_rows,table_name from INFORMATION_SCHEMA. partitions WHERE table_name = ' memberorder ';

Transferring source data to a new table
INSERT into Memberorder SELECT * from Memberorderhis;

1. See if the event is turned on

SHOW VARIABLES like ' Event_scheduler '

2. Set the current event to open

SET GLOBAL event_scheduler = 1;

Delete Partition
ALTER TABLE Memberorder DROP partition p20180616

MySQL partition table