Mysql > delete partition from database

The online partition table occupies a large space, so you need to delete partition data. Record the following information and record other commands for maintaining partitions for subsequent query.

The previous table was created as follows, leaving out some other fields

CREATE TABLE `bm_scenes_data_reminder` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `trace_id` varchar(50) DEFAULT NULL COMMENT 'data traceId',
  `bc_url` varchar(100) DEFAULT NULL COMMENT 'data BC. Url',
  `data_source` varchar(50) DEFAULT NULL COMMENT 'Data Source (topic)',
  `bz_resultCode` varchar(10) DEFAULT NULL COMMENT 'Error code',
  `start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Start time',
  PRIMARY KEY (`id`,`start_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19771343 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
/ *! 50100 PARTITION BY RANGE (to_days(start_time)) (PARTITION p20210103 VALUES LESS THAN (738158) ENGINE = InnoDB, PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB, PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB, PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB, PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB, PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB, PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB, PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB, PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
Copy the code

View the number of rows and occupancy for each partition

SELECT PARTITION_NAME,TABLE_ROWS,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bm_scenes_data_reminder';

+----------------+------------+-------------+
| PARTITION_NAME | TABLE_ROWS | DATA_LENGTH |
+----------------+------------+-------------+
| p20210103      |          0 |       16384 |
| p20210104      |          0 |       16384 |
| p20210105      |          0 |       16384 |
| p20210106      |          0 |       16384 |
| p20210107      |          0 |       16384 |
| p20210108      |          0 |       16384 |
| p20210109      |          0 |       16384 |
| p20210110      |          0 |       16384 |
| future         |          0 |       16384 |
+----------------+------------+-------------+
9 rows in set (0.00 sec)
Copy the code

If the partition data is empty, only the partition data is cleared

alter table bm_scenes_data_reminder truncate partition p20210104; 
Copy the code

Deleted partitions

alter table bm_scenes_data_reminder drop partition p20210104; 
Copy the code

P20210104 p20210104 p20210104

Increase the partition

  1. P20210104 p20210104 p20210104 P20210104 p20210104 p20210104 What to do. Try adding partitions directly first
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);
Copy the code

The result is as follows, indicating that it is not feasible.

mysql> ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>
Copy the code
  1. This is not possible in step ##1, == the hint must be added after the last partition. = =

P20210104 ==p20210104== P20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition ==p20210104 partition The operation is as follows:

ALTER TABLE BM_scenes_datA_reminder DROP PARTITION P20210105; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210106; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210107; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210108; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210109; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210110; ALTER TABLE bm_scenes_data_reminder drop PARTITIONfuture; Add partition P20210104ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB); Alter table p20210104 alter table P20210104ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Copy the code

And then check the DDL found partition and back, but this kind of operation will delete all partitions p20210104 partition at the back of the data, please carefully in the formal online environment