MYSQL -- one partition every half month. The table creation statement droptableifexiststerminal_parameter is automatically maintained. CREATETABLE 'terminal _ parameter '('terminal _ parameter_id' int (11) NOTNULLAUTO_INCREMENT, 'serial 'int (11) DEFAULTNULL, 'network _ type' char (1) DEFAULTNU
MYSQL -- one partition every half month. The table creation statement drop TABLE if exists terminal_parameter is automatically maintained; CREATE table 'terminal _ parameter '('terminal _ parameter_id' int (11) not null AUTO_INCREMENT, 'serial' int (11) default null, 'network _ type' char (1) DEFAULT NU
MYSQL-partitions are automatically maintained every half month
Table creation statement
Drop table if exists terminal_parameter;
Create table 'terminal _ parameter '(
'Terminal _ parameter_id 'int (11) not null AUTO_INCREMENT,
'Serial' int (11) default null,
'Network _ type' char (1) default null,
'Mcc 'int (8) default null,
'Mnc 'int (8) default null,
'Lac 'int (8) default null,
'Cellid' int (8) default null,
'Bsic _ psc' int (8) default null,
'Ta _ ec_io 'int (8) default null,
'Bcch _ rxlev_rscp 'int (8) default null,
'Arfcn _ uarfcn' int (8) default null,
'Rxq' int (8) default null,
'C1' int (8) default null,
'C2 'int (8) default null,
'Signal _ intensity 'int (8) default null,
'Error _ rate' int (8) default null,
'Arm _ type' varchar (16) default null,
'Txpower' int (8) default null,
'Small _ running_number 'int (8) default null,
'Createtime' datetime not null,
'Userid' int (8) not null,
'Terminal _ id' int (8) default null,
'State' char (1) DEFAULT '0 ',
'Order _ definition_id 'int (8) default null,
'Order _ Code' varchar (20) default null,
'Charg _ voltage 'float (8, 2) default null,
'Battery _ voltage' float (8, 2) default null,
'Temprad 'float (8, 2) default null,
'Run _ state' int (8) default null,
'Switching _ value1 'int (8) default null,
'Switching _ value2' int (8) default null,
'Bcch _ freq' int (8) default null,
'Rxlev' int (8) default null,
'Rxlev_full' int (8) default null,
'Rxlev_sub 'int (8) default null,
'Rxqual' int (8) default null,
'Rxqual _ full' int (8) default null,
'Rxqual _ sub' int (8) default null,
'Idle _ Ts' int (8) default null,
'Timing _ advance 'int (8) default null,
'Tch _ efr_out 'int (8) default null,
'Tch _ efr_in 'int (8) default null,
'Dtx 'int (8) default null,
'Major _ cycle_frequency 'int (8) default null,
Primary key ('terminal _ parameter_id ', 'createtime '),
KEY 'idx _ createtime' ('createtime '),
KEY 'idx _ terminal_id '('terminal _ id ')
) ENGINE = InnoDB default charset = utf8
Partition by range (TO_DAYS (createtime ))
(
PARTITION p20101115 values less than (TO_DAYS ('2017-11-15 ')),
PARTITION p20101130 values less than (TO_DAYS ('2017-11-30 ')),
PARTITION p20101215 values less than (TO_DAYS ('2017-12-15 ')),
PARTITION p20101231 values less than (TO_DAYS ('2017-12-31 ')),
PARTITION p20110115 values less than (TO_DAYS ('2017-01-15 ')),
PARTITION p20110131 values less than (TO_DAYS ('2017-01-31 ')),
PARTITION p20110215 values less than (TO_DAYS ('2017-02-15 ')),
PARTITION p20110228 values less than (TO_DAYS ('2017-02-28 ')),
PARTITION p20110315 values less than (TO_DAYS ('2017-03-15 ')),
PARTITION p20110331 values less than (TO_DAYS ('2017-03-31 ')),
PARTITION p20151115 values less than (TO_DAYS ('2017-04-15 ')),
PARTITION p20151130 values less than (TO_DAYS ('2017-04-30 '))
);
Stored Procedure Code:
* Run the command once every 15 days.
/* Program function: The program uses a circular partition with data retained for six months.
Time */
Drop procedure if exists Set_Partition;
Create procedure Set_Partition ()
Begin
/* Transaction rollback. In fact, it does not work here. alter table is committed implicitly and cannot be rolled back. */
Declare exit handler for sqlexception rollback;
Start TRANSACTION;
/* Find the maximum partition in the system table and get the date of the maximum partition. When creating a partition, the name is stored in the date format for later maintenance */
Select REPLACE (partition_name, 'P', '') into @ P12_Name from region where TABLE_SCHEMA = 'mydb _ 1' and table_name = 'terminal _ parameter 'order by partition_ordinal_position DESC limit 1;
/* Determine the time range of the maximum partition. If it is the first half of the month, add 13, 14, 15, 16 days as needed.
If it is in the second half of the month, then add 15 days. + 0 is used to format all dates into a format like YYYYMMDD */
IF (DAY (@ P12_Name) <= 15) THEN
CASE day (LAST_DAY (@ P12_name ))
WHEN 31 THEN set @ Max_date = date (DATE_ADD (@ P12_Name + 0, INTERVAL 16 DAY) + 0;
WHEN 30 THEN set @ Max_date = date (DATE_ADD (@ P12_Name + 0, INTERVAL 15 DAY) + 0;
WHEN 29 THEN set @ Max_date = date (DATE_ADD (@ P12_Name + 0, INTERVAL 14 DAY) + 0;
WHEN 28 THEN set @ Max_date = date (DATE_ADD (@ P12_Name + 0, INTERVAL 13 DAY) + 0;
End case;
ELSE
Set @ Max_date = date (DATE_ADD (@ P12_Name + 0, INTERVAL 15 DAY) + 0;
End if;
/* Modify the table, add a partition after the maximum partition, and add a time range of half a month */
SET @ s1 = concat ('alter TABLE terminal_parameter add partition (PARTITION P', @ Max_date, 'values less than (TO_DAYS (''', date (@ Max_date ), ''')))');
PREPARE stmt2 FROM @ s1;
EXECUTE stmt2;
Deallocate prepare stmt2;
/* Retrieve the name of the smallest partition and delete it.
Note: deleting a partition will delete the data in the partition at the same time. Exercise caution */
Select partition_name into @ P0_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA = 'mydb _ 1' and table_name = 'terminal _ parameter 'order by partition_ordinal_position limit 1;
SET @ s = concat ('alter TABLE terminal_parameter drop partition ', @ P0_Name );
PREPARE stmt1 FROM @ s;
EXECUTE stmt1;
Deallocate prepare stmt1;
/* Submit */
COMMIT;
End;
Scheduled task code:
Create event e_Set_Partition
ON SCHEDULE
EVERY 15 day STARTS '2017-04-30 23:59:59'
DO
Call Set_Partition ();
